Merge WordPress Site Databases, Append Prefix

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

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.

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.

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

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.