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.
- 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_%`
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.
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
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.