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!