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.
We need to let the MySql user of site1 be able to make changes on site2's database.
mysql> GRANT ALL PRIVILEGES ON site2's-db.* TO 'site1s-username'@'localhost';
mysql> FLUSH PRIVILEGES;
Now we change the prefix of site1 so that there is no conflicts when we move over it's tables to site2's database.
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_%`
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.
Now time to export site1's database to it
You need to be logged in as root for this.
mysqldump -u root -p SITE1'S-DATABASE-NAME > /YOUR-CREATED-DIRECTORY/ANY-NAME.sql
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
Now site1's database is merged with site2's
Now you have to change site1's wp-config.php to use site2's database.
You have merged two WordPress sites into a single database.
Any questions feel free to comment below.