Jason Irwin dot Net

Merging WordPress Databases

May 5, 2009 Internet, Technology

This post is the second in a short series outlining how anyone can take measures to simplify WordPress installations on shared or private hosting solutions, improving security and reducing maintenance time.

Yesterday I showed one possible way to host multiple WordPress blogs with a single installation and today I will show you an easy way to merge WordPress databases, further simplifying any administration we may perform or giving us the opportunity to create some more databases on a restrictive web host plan.  Regardless of what reasons we might have for merging several WordPress databases, this is something that can easily be accomplished in the same amount of time it takes to drink a cup of coffee.

In my case, I had over 20 MySQL databases for the various blogs I had set up for friends and family that I no longer wanted to see.  Why I had 20 databases for sites that would see maybe 10 updates a year, I don’t know, but I’ve recently taken the track of keeping personal servers running as clean and efficiently as the production-level servers I had once maintained.

WordPress Export/Import vs. SQL Dumps

One of the most common ways for people to go about merging databases would be to start with a new one, assign some different table prefixes, have WordPress export existing data, and then re-import it into the new database.  While this is certainly one way to attack the situation, the WordPress export will ignore some of the datatables used by various plugins.  So, in an effort to reduce data loss and site reconfiguration, I would suggest using something like phpMyAdmin or MySQL Administrator to first export all the data from the different databases you want to merge. From here we can create a new database and begin to import the data.  We can gave this new database a generic name like ‘general’ or ‘family’, and then import the previous databases one at a time.

Ah, but each database has the same tables, you say?  And each table is prefixed with the same “wp_” you say?  This is really easy to change.  After each SQL import, you can rename the tables in phpMyAdmin or MySQL Query Browser with the following code:

[code lang="sql"]
RENAME TABLE orig_name TO new_name;
[/code]

In my case, I wrote this once for the ten main WordPress tables, and then executed the query against the database after uploading the .sql file.  If there were other tables, from plugins and whatnot, I would issue a few more RENAME statements.

With yesterday’s post I had shown how, if we’re using just a single installation of WordPress, we can change the database name and prefix based on the $_SERVER['HTTP_HOST'] value.  So if you want to merge installations and databases at the same time, you just need to make sure that the table prefix in config.php matches the prefix that you’re changing the imported tables to. For example:

[code lang="php"]
case “sub1.YourDomain”:
$pre = “wp1_”;
$db = “SharedDatabase”;
break;
[/code]

… would mean we’d type something like …

[code lang="sql"]
RENAME TABLE wp_comments TO wp1_comments;
[/code]

Because WordPress does not use DRI by any stretch of the imagination, we will have no trouble assigning different prefixes to different installations.

Error: You Do Not Have Sufficient Permissions To Access This Page

If you try to access the blog now, you’ll probably find that you can load it just fine.  But you’re not done with the SQL work just yet because the moment you try to access the Admin screens you’ll be faced with a happy little error reading “You do not have sufficient permissions to access this page“.

To solve this problem, you need to change some settings in both the options and usermeta tables.  Luckily, we can use these quick SQL statements:

[code lang="sql"]
UPDATE wp1_options
SET option_name = REPLACE(option_name, 'wp_', 'wp1_')
WHERE option_name LIKE 'wp_%';
[/code]

[code lang="sql"]
UPDATE wp1_usermeta
SET meta_key = REPLACE(meta_key, 'wp_', 'wp1_')
WHERE meta_key LIKE 'wp_';
[/code]

Be sure to replace the prefixes accordingly, and you’ll have no trouble logging in to the Admin screens.

Done and Done?

Believe it or not, this is all there is to it.  By following these simple steps, you can quickly merge as many WordPress databases into one big one.  Naturally, you would want to consider whether everyone should have their own separate login and what table permissions they should have, but if you’re just looking for simplicity and not too concerned about security, every blog can have the same username and password. Just remember that if every blog is using the same database and credentials, then you could be begging for trouble if you happen to fall prey to an exploit.

Tags: database, install, MySQL, WordPress