Working Within Limited Databases: How to Install Multiple WordPress Websites into a Single MySQL Database
WordPress counts more than 60 million worldwide users of all sizes, from corporate websites to personal blogs. And while corporate implementations of WordPress might have access to unlimited server resources and MySQL databases, it’s often true that amateur web hosting plans cut corners and eliminate features in order to save customers money. In most cases, one of the first features to get minimized on a low-cost web hosting plan is access to the all-important MySQL databases that WordPress and other web applications require in order to operate. In many cases, web hosts will grant customers access to only one or two such databases for all of their needs. That can be a significant problem, especially for those individuals who are looking to host multiple blogs or WordPress installations within the same server or domain name.
Luckily, this problem is worse on paper than it is in practice. The developers behind WordPress have often catered to the amateur developer community, and they’ve made sure to do this when it comes to WordPress’ utilization of its database. Before the initial setup page for a WordPress installation is ever launched, site administrators can easily customize configuration settings that will allow for unique WordPress installs into a single MySQL database. They can also specify advanced settings, such as those that allow for the sharing of username and password information between these various installations. These advanced options are supplemented by an easier-to-use multi-site feature within the WordPress Dashboard, and this allows for multiple websites within the same database using an entirely different approach.
In order to get WordPress installations working side-by-side in a single database, website administrators should have some familiarity with basic PHP. They should also have a rough idea of how to appropriately edit the “wp-config.php” file in order to make the changes that will enable multiple instances in one MySQL database. Beyond this, the skills are pretty basic: Administrators will simply need to go through the WordPress installer to set up each installation if they’re not invoking the included multi-site feature.
Step 1: Locating the WordPress Configuration File in Order to Modify It
Anyone who has used WordPress for any amount of time can attest to its larger and well-organized file structure. While that’s good in practice, it can make certain files or libraries hard to locate when they’re needed most. This can easily be avoided by understanding where the
wp-config.php file is located in every WordPress installation. Since the installation in question will likely be secondary, and thus placed into a subfolder within the current website, the relevant configuration file can be found here:
This file can be accessed and edited via either a web-based file manager (like those included with cPanel) or via a traditional desktop FTP client. In this case, an FTP client is probably preferable. Its more advanced functions and file editing processes will assist new users in getting the job done quickly and accurately.
Finally, be sure that the correct
wp-config.php file is open. The modifications made to this file will directly determine its access to content and users, and editing the wrong file can have adverse effects on the production website. When it’s been assured that the correct file is open, proceed to the next step of this tutorial.
Step 2: Changing the WordPress Installation’s Database Prefix
The key to installing multiple iterations of WordPress into the same MySQL database is to simply change the database prefix that determines where WordPress will create and populate its information for processing by the software itself when a user accesses the website. This is done within
wp-config.php by altering a single line of code near the middle of the file. This information lies below where the database name, and user information, is accessed and edited.
In order to edit the WordPress installation’s database prefix, locate the following snippet of PHP code, right about midway through:
$table_prefix = 'wp_'; // example: 'wp_' or 'b2' or 'mylogin_'
This is how the line will look by default in an un-edited
wp-config.php file. This indicates a database prefix of
wp_, which is the default for every WordPress installation. However, if this is a secondary installation of the software, that prefix will need to be changed. If it is left unedited, and the installation process is completed for the new install, it will overwrite every user, post, and page, that has been created by the primary installation.
The database prefix can be changed to virtually anything, so long as it follows some basic rules. Though uppercase letters aren’t necessarily forbidden, it’s a good idea to leave them out and prefer an all-lowercase database prefix. Numbers should probably be left out as well, and the database prefix absolutely cannot contain any spaces or other punctuation. The only punctuation permitted is the underscore after the database prefix’s name, as this is what will connect to the WordPress table names like “users” or “posts.” Most users prefer to give the new installation a descriptive prefix like “musicblog” or “news.” A descriptive prefix is the best way to ensure that the proper database tables are being manipulated and edited at some point down the road.
Step 3: Determining Whether or Not Users Should be “Shared” Between Installations
While fitting multiple WordPress installations into a single database might be seen as an inconvenience by many of the software’s users, it actually comes with a huge advantage in terms of utilizing a “single sign-on.” Because all of the WordPress installations will be installed into the same database, the “wp-config.php” file can actually be told to use one of the other installation’s user and password tables. This is done simply by telling WordPress to look for users in a different prefixed table than it currently uses to store pages, posts, and plugin data, and it’s as easy as customzing a few lines near the end of the configuration file.
WordPress refers to the entire package of user information as “usermeta,” and this will be more obvious when editing the two lines needed to enable single sign-on. Simply enough, there are two lines near the bottom of the configuration file that assign username and password information, as well as profile data, to certain database prefixes. In most cases, this prefix is set to the variable “$table_prefix,” making it the same as the prefix defined earlier in this tutorial. When this variable is changed, user information can be stored anywhere — or pulled from any other WordPress installation in the same database.
For the purposes of this tutorial, the user data configuration lines will be updated to pull information from the “mainblog” database prefix. Here is an example of what the lines look like before the modification:
define('CUSTOM_USER_TABLE', $table_prefix.'my_users'); define('CUSTOM_USER_META_TABLE', $table_prefix.'my_usermeta');
These two lines are then easily updated to include the desired database prefix to enable communication between installations. After being changed, they look like this:
define('CUSTOM_USER_TABLE', mainblog_'my_users'); define('CUSTOM_USER_META_TABLE', mainblog_'my_usermeta');
The variable, which is traditionally surrounded with the dollar sign and period punctuation marks, is removed. Appropriately, the punctuation marks are also removed in favor of the “mainblogs” prefix and the traditional underscore that bridges the prefix and the actual user table name. When this change is applied to every WordPress installation located within the same database, users will be able to use their same credentials to access every Dashboard and post comments on every website. It’s a great way to reduce overall database size, as it doesn’t require the duplication of any tables or information, and it will be extremely convenient to users who won’t have to sign up multiple times, or remember multiple passwords when interacting with all of a site’s secondary WordPress blogs.
Step 4: Using WordPress Networks to Install Multiple Sites Without Different Prefixes
For several years, the developers behind WordPress actually developed two separate versions of the software. The first, and longest-lasting, is the standard version of WordPress. The second, unveiled in 2007, was WordPress MU. This version of WordPress was used to run multiple websites within a single database. This particular version of WordPress was discontinued in 2011 and its feature set was rolled into the standard WordPress release under the name “WordPress Networks.” Though hidden by default, the WordPress Networks feature can be enabled simply by editing the
wp-config.php file and instructing WordPress to show the Networks settings in the Dashboard.
The distinct advantage to using a WordPress Networks-based approach to hosting multiple blogs in one database is that it involves a minimal amount of redundant database tables and cells. This streamlined approach can actually lead to a much smaller database overall, and that’s a key thing to note. Many web hosts which limit the number of databases available to their customers also limit either the size of the overall database or the total amount of hard disk space that can be utilized. Eliminating redundant information used by multiple WordPress blogs will provide the best utilization of limited and restricted space at many hosts.
To enable the feature, a line of code must be added to the
wp-config.php file. This line instructs WordPress to show the WordPress Networks control panel page, and it will prompt the software to go through a new installation routine to configure this network. The line of code that must be added is:
After this line has been added, a “WordPress Networks” heading will appear in the Dashboard sidebar area; this heading should be clicked, and it will reveal a page full of settings that pertain to the new network that is being established. The site administrator will need to specify the network’s URL, where all blogs will be installed, and will have to fill in information about the network’s title, the server’s addresses, and some email information. When that’s complete, the information is saved and the next screen of the installation process displays.
On that screen, WordPress will give the administrator a whole host of things that need to be modified manually, outside of the WordPress Dashboard. More lines of code will need to be added to the
wp-config.php file; these lines of code help the network operate properly within the site’s unique setup and requirements, and the specific lines that must be pasted very between WordPress installations and web servers themselves.
The second step of this manual process will see the WordPress Dashboard offering up some new lines of code for the “.htaccess” file. These lines of code are essential to the network’s permalinks. They’re also the way that the network’s subdomains or directories will be properly managed and mapped by the Dashboard itself on every new blog.
Finally, administrator users will need to specify a “media” directory. This is where uploaded images, videos, and other files, will be stored for every blog within the new WordPress Network configuration. This process doesn’t involve manual modification of files. Instead, administrators will simply need to enter the server path to the directory where they’d like to store this content. It can be virtually anywhere, and is best placed in a convenient, site-independent location.
Once these modifications have been made, the network is completely setup and ready to be utilized. Administrators will notice that the Dashboard has been slightly modified; it will now present a “My Sites” drop-down menu where it used to link to the main website’s homepage. That menu will present every website created within the WordPress Network, and it can be used to either view those websites or change their settings and appearance.
Two Great Ways to Work with Limited Hosting Plans
Whether using the WordPress Networks feature, or simply modifying the default database prefix employed by the
wp-config.php file, there are plenty of ways to get around limited hosting plans. These two options are both great ways to get multiple sites up and running within a single database, even as it is limited by overall size or disk space utilization.
You might also like…
Using jQuery to Validate the Standard WordPress Comment Form →
Using the Power of the .htaccess File to Improve WordPress SEO →
For the Novice Developer: The Anatomy of a WordPress Theme →
Focusing on Usability with an Enhanced Pagination Design in WordPress Entries →