Combine multiple installations of WordPress into the same MySQL database.

wordpress-3When in a situation where the internet hosting site will only permit the use of one MySQL database and it is serving one WordPress installation, and you would like to serve more than one WordPress installation, then combine the multiple installations of WordPress into the same MySQL database. This can be accomplished using phpMyAdmin to select the default WordPress database (for example, wordpress).

I selected the tables that I wanted to export as it relates to the WordPress installation and selected SQL as the export format.

I selected Add DROP TABLE /DROP VIEW and Add IF NOT EXISTS on top of the defaults, as indicated in the image below.

phpmyadmin1

I wanted to Save as file, and deselected Save on server in …

phpmyadmin2

Open the saved text file with your favorate editor and look for references to your test box or alternate server and change to the new server location. For example, http://localhost/myblog to http://www.myblogsite.com. There are at least two locations of these entries, home and siteurl.

Now, this is important, very important. Since I had to use the same database using multiple installations of WordPress, I didn’t want to overwrite any existing table or value. So for each installation, I had to change the prefix of each table as it related to the installation of WordPress.

For example:

  • http://www.wordressinstallsite.com uses database wordpress with table prefixes of wp_
  • http://store.wordpressinstallsite.com uses database wordpress with table prefixes of store_
  • http://anotherblog.wordpressinstallsite.com uses database wordpress with table prefixes of ablog_

With you favorite editor replace wp_ entries with ie. ablog_. When complete, use phpMyAdmin to import the text file and create the tables.

I then copied my folder tree from my test installation to my production box. I edited the wp-config.php file, the DB_NAME should remain the same. Likely changes to the DB_USER and DB_PASSWORD may be required; however, a change to the $table_prefix will be required so that the WordPress installation may access the table entries. If this is not changed, you may overwrite your data.

<?php
// ** MySQL settings ** //
define(‘DB_NAME’, ‘wordpress‘); // The name of the database
define(‘DB_USER’, ‘appropriate useraccount as other wordpress install’); // Your MySQL username
define(‘DB_PASSWORD’, ‘appropriate password as other wordpress install’); // …and password
define(‘DB_HOST’, ‘localhost’); // 99% chance you won’t need to change this value
define(‘DB_CHARSET’, ‘utf8’);
define(‘DB_COLLATE’, ”);

// You can have multiple installations in one database if you give each a unique prefix
$table_prefix = ‘ablog_‘; // Only numbers, letters, and underscores please!

Disclaimer: As with all the information provided on this site, we cannot be held accountable for any negative impact that you may encounter. We are only reporting what has worked for us.

Checkout the following real life example: