Introducing HyperDB into your WordPress site
Adding HyperDB is extremely easy: even thought it’s classed as a plugin, you don’t add it via the WordPress backend. Instead, you grab hold of your favourite FTP client and copy db-config.php into the directory which holds wp-config.php. Then you’ll copy db.php into the wp-content directory and voila – HyperDB is active.
By default, HyperDB is configured to use your standard MySQL server for read and write queries – as it’s defined in the constants in wp-config.php (that’s DB_HOST, DB_USER, DB_PASSWORD and DB_NAME). It does this via these lines – they’re about halfway down in the db-config.php file. They’re one of the few that are not commented out:
/** * This is the most basic way to add a server to HyperDB using only the * required parameters: host, user, password, name. * This adds the DB defined in wp-config.php as a read/write server for * the 'global' dataset. (Every table is in 'global' by default.) */ $wpdb->add_database(array( 'host' => DB_HOST, // If port is other than 3306, use host:port. 'user' => DB_USER, 'password' => DB_PASSWORD, 'name' => DB_NAME, )); /** * This adds the same server again, only this time it is configured as a slave. * The last three parameters are set to the defaults but are shown for clarity. */ $wpdb->add_database(array( 'host' => DB_HOST, // If port is other than 3306, use host:port. 'user' => DB_USER, 'password' => DB_PASSWORD, 'name' => DB_NAME, 'write' => 0, 'read' => 1, 'dataset' => 'global', 'timeout' => 0.2, ));
When it says in the readme file “configure db-config.php”, it means put the values relevant to your system in here. This is just an example and it’s fairly self explanatory. Copy the second block and paste it with values that fit your configuration for as many servers as you have in your setup.
I’ll talk you through it line by line:
- create an array with values for
- host: that’s your database host IP or domain name. You’d replace this with ‘master1.com’ or ‘111.222.111.222’, whatever your second database server is.
- user: the database user name. You can leave this constant in place, unless you have different login credentials for this server (unlikely)
- password: the database user password. As above, leave it in place.
- name: the actual database name. Again, we can take this from the wp-config.php file and leave this constant in place.
- write: set this to 0 or 1, depending if you’d like to use this server for read or write queries.
- read: as above
- dataset: set this to ‘global’ or a specific database table. I’m only going to use HyperDB for complete databases, but for larger installations a single table could live on a different server all by itself – this is where you’d set this. It’s not covered here though.
- timeout: amount of time HyperDB waits until it considers this server unresponsive. 0.2 is the default – let’s leave it there.
Sample HyperDB Configuration
My configuration for this section looks something like this. I’m using both master1.com and master2.com for reading and writing, while slave1.com and slave2.com are read only.
/** * adding Master 1 for write queries */ $wpdb->add_database(array( 'host' => 'master1.com', // If port is other than 3306, use host:port. 'user' => DB_USER, 'password' => DB_PASSWORD, 'name' => DB_NAME, 'write' => 1, 'read' => 0, 'dataset' => 'global', 'timeout' => 0.2, )); /** * adding Master 1 for read queries */ $wpdb->add_database(array( 'host' => 'master1.com', // If port is other than 3306, use host:port. 'user' => DB_USER, 'password' => DB_PASSWORD, 'name' => DB_NAME, 'write' => 0, 'read' => 1, 'dataset' => 'global', 'timeout' => 0.2, ));
/** * adding Master 2 for write queries */ $wpdb->add_database(array( 'host' => 'master2.com', // If port is other than 3306, use host:port. 'user' => DB_USER, 'password' => DB_PASSWORD, 'name' => DB_NAME, 'write' => 1, 'read' => 0, 'dataset' => 'global', 'timeout' => 0.2, ));
/** * adding Master 2 for read queries */ $wpdb->add_database(array( 'host' => 'master2.com', // If port is other than 3306, use host:port. 'user' => DB_USER, 'password' => DB_PASSWORD, 'name' => DB_NAME, 'write' => 0, 'read' => 1, 'dataset' => 'global', 'timeout' => 0.2, ));
/** * adding Slave 1 for read queries */ $wpdb->add_database(array( 'host' => 'slave1.com', // If port is other than 3306, use host:port. 'user' => DB_USER, 'password' => DB_PASSWORD, 'name' => DB_NAME, 'write' => 0, 'read' => 1, 'dataset' => 'global', 'timeout' => 0.2, ));
/** * adding Slave 2 for read queries */ $wpdb->add_database(array( 'host' => 'slave2.com', // If port is other than 3306, use host:port. 'user' => DB_USER, 'password' => DB_PASSWORD, 'name' => DB_NAME, 'write' => 0, 'read' => 1, 'dataset' => 'global', 'timeout' => 0.2, ));
7 thoughts on “How to deploy HyperDB”