Merge WordPress Site Databases, Append Prefix

Tags:

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 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.

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.

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.

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 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. Required fields are marked *

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