This is the second part in my effort to help explain how you can use HyperDB to scale your large WordPress, WordPress MU or Buddypress sites.     You can read the first part in the series here!    First off, again:

• This tutorial was written for testing and personal purposes.
• I make no warranty that this will work for you as all setups are generally different.
• HyperDB is designed for larger sites to scale when one server isn’t enough and not recommended on small projects
• Backup all data before trying it out.
• This tutorial is not sanctioned by Automattic or the developers, but to be used as a helping guide if possible.

In the first tutorial, I discussed the in and outs of getting your db-settings.php file configured to work with your site and used the example of getting sharing your WordPress users with another site by placing the users tables in their own database.     In this part, I want to discuss using multiple database servers as well as replication.

Replication

This is not a tutorial on setting up replication.   In fact, I have three (3) incredible articles that I am listing below that will help you set up replication:

1. Acid Drop – Great tutorial on setting up mySQL replication
2. Crazytoon – Another article that I referenced
3. HowToForge – One of the best in the game for reference

At this point, I am making the following assumptions:

• You have mySQL replication setup between at least 2 database servers
• You want to have writes going to the master and reads coming from the slaves
• Your IP’s of these servers are:  192,168.0.1, 192.168.0.2, 192.168.0.3, etc
• The database servers will have datacenter names corresponding to their function; master, slave1, slave2, etc

What we want to do first is setup your main (master) server in db-settings.php to handle all database writes as well as be last in line for database reads if the slave servers are not responding or busy:

add_db_server(’global’, 0, ‘master’, 2, 1,’192.168.0.1′,’localhost’,’database-name’,’database-user’,’database-password’);

Now, we want to put in the requests for the (slave) servers so that they don’t neither accept any writes and both slave drives will take on the database reads in the same priority.

add_db_server(’global’, 0, ‘slave1’, 1, 0,’192.168.0.2′,’localhost’,’database-name’,’database-user’,’database-password’);

add_db_server(’global’, 0, ‘slave2’, 1, 0,’192.168.0.3′,’localhost’,’database-name’,’database-user’,’database-password’);

There are a million combinations of what you want to happen.    This is just giving a rough idea of how you can have the master database server accepting all the writes which in turn replicates this data to the slaves to handle all reads of data for the most part.

It is still best to subscribe to the HyperDB mailing list as well as really look at the code to determine how things are going to happen.    The codex is also a good read.  As Andy has mentioned on the mailing list:

I am reluctant to write documentation for HyperDB because I wouldn’t advise its use to anyone who wasn’t thoroughly familiar with how it works. The code isn’t the prettiest, but it can be understood.

This concludes this tutorial and I hope that this opens up your eyes to the different setups that are possible with this great plugin!

HyperDB is a replacement for the standard WPDB class which adds the ability to use multiple databases. HyperDB supports partition of data as well as replication.

HyperDB is a plugin written by matt, andy, ryan, & mdawaffe from Automattic.     It is written primarily as a way to help larger sites manage growth as well as offers support for replication for backup and performance redundancy.     While most users will never need something like HyperDB, this tutorial has a purpose of explaining its configuration.

We might as well get this out right now….the disclaimer!

• This tutorial was written for testing and personal purposes.
• I make no warranty that this will work for you as all setups are generally different.
• HyperDB is designed for larger sites to scale when one server isn’t enough and not recommended on small projects
• Backup all data before trying it out.
• This tutorial is not sanctioned by Automattic or the developers, but to be used as a helping guide if possible.

Now that we have that out of the way, let us continue!

The first thing you need to do is download HyperDB.   It has 3 files included.    From the readme.txt you get the following directions and should become extremely familiar with them:

Nothing goes in the plugins directory.

1. *WordPress MU only:* add this line near the top of `wp-config.php`:
`define(’WPMU’, true);`

2. Upload `db.php` to the `/wp-content/` directory. At this point, HyperDB is active. It will use the database connection constants until you complete the final steps.

3. Upload `db-settings.php` in the directory that holds `wp-config.php`

4. Edit the db settings according to the directions in that file.

5. Add this line near the top of `wp-config.php`:

`require(’db-settings.php’);`

Any value of `WP_USE_MULTIPLE_DB` will be ignored by HyperDB. If you wish to switch off multiple DB, remove the ‘require’ statement from step 5.

As you can see, it is pretty straight forward except for step 4 That is what we are going to talk about today.    I suggest that you do step 4 first using my example and then the other steps in order, but it is up to you.   In my example I am going to demonstrate how you can use HyperDB to split up your wp_users table so that you can share your users with another install.   In our PART 2, we will discuss some of the more advanced issues of HyperDB, but this is just to get you familiar with working with it.

For documentation purposes, I am going to have the following assumptions:

1. Your IP Address of your server is 192.168.0.1 and your mySQL is on the standard port
2. You have 2 databases:   1) blog 2) users
3. Your Database username is “wp-user” for both databases
4. Your Database password is “wp-password” for both databases

db-settings.php

This is the main and only configuration file required to run HyperDB.     The first thing you should notice is that line 57 is there so your install works with the readme.txt before getting to step 4.     Since we are starting on step 4, we need to comment it out right away.

// add_db_server(’global’, 0,    ”, 1, 1,                        DB_HOST,                        DB_HOST,    DB_NAME,     DB_USER,     DB_PASSWORD);

I like to copy line 61 and place it just below that previously commented out line.    This is the one that we are going to edit for our first database, “blog”.   This is the line we should copy across before we edit it.

add_db_server(’global’, 0, ‘nyc’, 1, 1,’global.mysql.example.com:3509′,’global.mysql.example.lan:3509′,’global-db’,’globaluser’,’globalpassword’);

This is where we tell HyperDB where and what our server really is.     There are some things in that configuration that can be configured for more advanced purposes, but will remain the same in this discussion.    For example, “nyc” is the New York Data center.    This is important for replication (discussed later), but not now.   Here is the configuration using our assumptions earlier and our data center we will just call “fun”

add_db_server(’global’, 0, ‘fun’, 1, 1,’192.168.0.1′,’localhost’,’blog’,’wp-user’,’wp-password’);

This tells HyperDB that we are going to have all calls (reads and writes) going to this global database unless we specify otherwise.   It is calling the IP address of our database server, since we don’t have a fancy DNS for our database setup and just localhost for the same reason.   If your host has some DNS entries setup, then you will have to adjust accordingly.

Now that we have defined our main database, we are going to add the following line below our previous one for our second database that houses our users.   *If you haven’t already, copy the wp_users table into the “users” database*

add_db_server(’users’, 0, ‘fun’, 1, 1,’192.168.0.1′,’localhost’,’users’,’wp-user’,’wp-password’);

This tells HyperDB that we have a second database setup and that we are going to have calls to it.    We haven’t told it yet what we are going to call, but that is next.   You can also copy the wp_usermeta table as well and set it up if you want the same user roles in the second blog.    I want that on a blog by blog basis, so I only am using the wp_users table.

On line 97 there is the following line:

// add_db_table(’misc’, ‘bbq_sauces’);

We are going to make sure that below this line we setup our calls to our second database, “users”.   In our example, we are adding the following lines to call the users  information from our second database:

add_db_table(’users’, ‘wp_users’);

That should be all we need to do.    Now complete the rest of the steps in the readme.txt file including copying this db-settings.php file we created to the same directory as wp-config.php, edit wp-config.php to have the following in it near the top of the file:

require(’db-settings.php’);

Finally, copy db.php to your /wp-content/ directory.     Everything should be working correctly at this point.    If you find it isn’t, just comment out the require that we just did in wp-config.php, rename db.php to db.txt and double check all your settings.   When I say comment out, I mean:

// require(’db-settings.php’);

This concludes our PART 1 series on HyperDB configuration.    As you can see, you could have a second install of WP or WPMU using HyperDB and tell it to use the second “users” database and completely share users!    In PART 2 we will discuss replication and configuring other plugins like Buddypress to have its tables in a database for itself.