When 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).
Select the tables to export as it relates to the WordPress installation and selected SQL as the export format.
Selected Add DROP TABLE /DROP VIEW and Add IF NOT EXISTS on top of the defaults, as indicated in the image below.
Then Save as file, and deselected Save on server in …
Open the saved text file with your favorite 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.
- 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.
Then copy the directory structure from the test installation to the production box. Edit the wp-config.php file, the DB_NAME should remain the same. 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, data may get overwritten.
<?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!
Use at your own risk.