Merge WordPress Site Databases, Append Prefix

Merge WordPress Site Databases, Append Prefix

,,,,,,,,,

meadowlark

WordPress and MySql, One in the same

BACKUP BACKUP BACKUP!!! Before attempting this. Full server snapshot. Not just WordPress backup!

Hello, today I had a task and was unable to find a complete guide for it. So here you go.

This is a simple task. You have two sites on the same database server using different databases. You want to merge them into the same database.

I needed to do this so I could share user tables between sites. Without using Multisite.

Steps:

  • Grant site1's user privilege's on site2's database
  • Change site1's database prefix (if site 2 dose not have a stock prefix then ignore this)
  • Merge the two databases
  • Change site1's wp-config to site2's database (keep user and password the same)
  • Throw a party!

Grant site1 user privilege's on site2's database

We need to let the MySql user of site1 be able to make changes on site2's database.

  • Find username of site1
  • Grant all on site2's database to site1
Database username will be in wp-config.php
mysql> GRANT ALL PRIVILEGES ON site2's-db.* TO 'site1s-username'@'localhost';
mysql> FLUSH PRIVILEGES;

Change site1's database prefix

Now we change the prefix of site1 so that there is no conflicts when we move over it's tables to site2's database.

  • Use php-my-admin to append table prefixes
  • Correct inter-table WordPress data.
Select all tables in site1's database and click "replace table prefix"

Now we must correct all the inner-table prefixe's.

Come over to here in php-my-admin (in the database of the site you just appended.)

Run the following queries:

SELECT * FROM `NEW-PREFIX-GOES-HERE_options` WHERE `option_name` LIKE '%wp_%'

That will give you all the table entrys that still have the old prefix. You need to manually change every entry displayed to the new prefix

Do the same with the results of this query:

SELECT * FROM `NEW-PREFIX-GOES-HERE_usermeta` WHERE `meta_key` LIKE `%wp_%`

Merge databases

Now time to merge the two sites databases together into one.

  • Drop into terminal
  • Export site1's database to a .sql file
  • Import it into site2's database

Make a folder somewhere where you can save the .sql export to. You will only need it for a second.

I made a directory called "dbspot" in /opt (ignore the home folder ls, I accidently did that)

Now time to export site1's database to it

You need to be logged in as root for this.

sudo -i

mysqldump -u root -p SITE1'S-DATABASE-NAME > /YOUR-CREATED-DIRECTORY/ANY-NAME.sql
password: MYSQL-ROOT-PASSWORD

Now a copy of site1's database is in whatever directory you put it in.

Now time to import it to site2's database (will still need to be logged in as root.)

mysql -u root -p SITE2'S-DATABASE < /YOUR-CREATED-DIRECTORY/SITE1-BACKUP-NAME.sql
password: MYSQL-ROOT-PASSWORD

Now site1's database is merged with site2's

Change site1's wp-config.php to reflect changes

Now you have to change site1's wp-config.php to use site2's database.

  • Change wp-config database name
  • Change wp-configs database prefix
Change this value in site1's wp-config to the name of site2's database
Change prefix to one you changed it to.

That's all folk's (of course restart the server)

You have merged two WordPress sites into a single database.

Any questions feel free to comment below.

Leave a Reply

Your email address will not be published.

We Would Love to Help

Web design. IT computer network systems. Security Cameras. Graphic design. We do it all. Reach out to see if we are a good fit for you.
menuchevron-down linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram