HyperDB Configuration Master/Slave

Question

Running on server 2008 R2 IIS 7.5, with a MySQL Master/Slave (also on server 2008 r2), and WordPress 3.7. Is it ONLY compatible with wordpress 3.5.2? If that is so is there any other plugins to allow a Master/slave mysql setup?

This is what I have done so far: 1. I copied db.php to /wp-content/ folder 2. Edited the wp-config.php file to have (not the real IP addresses) define('DB_HOST', '192.168.1.198'); define('DB_HOST_SLAVE', '192.168.1.198'); 3. edited the db-config.php and put at the root of the site.

<?php
$wpdb->save_queries = false;
$wpdb->persistent = false;
$wpdb->max_connections = 10;
$wpdb->check_tcp_responsiveness = true;
$wpdb->add_database(array(
    'host'     => DB_HOST,
    'user'     => DB_USER,
    'password' => DB_PASSWORD,
    'name'     => DB_NAME,
    'write' => 1,
    'read' => 1,
    'dataset' => 'global',
    'timeout' => 0.2,
));
$wpdb->add_database(array(
    'host'     => DB_HOST_SLAVE,
    'user'     => DB_USER,
    'password' => DB_PASSWORD,
   'name'     => DB_NAME,
    'write' => 0,
    'read' => 1,
    'dataset' => 'global',
    'timeout' => 0.2,
));

Now when I bring the master down the site stops loading, I expected that the slave would just put the site into read only. All I see in the php logs is it trying to connect to 192.168.1.198, when I expected the reads would fail over to the slave if it can't connect to 192.168.1.199. This is the error

WordPress database error 2013-12-26 19:25:40 Can't select global__w -
'referrer' => 'blah.com/wp-admin/',
'server' => ,
'host' => 192.168.1.198,
'error' => ,
'errno' => 0,
'tcp_responsive' => ,
'lagged_status' => 3 for query SELECT option_value FROM wp_options WHERE option_name = 'db_upgraded' LIMIT 1 made by get_option

Now if I edit the db-config.php to be like this

<?php
$wpdb->save_queries = false;
$wpdb->persistent = false;
$wpdb->max_connections = 10;
$wpdb->check_tcp_responsiveness = true;
$wpdb->add_database(array(
    'host'     => DB_HOST_SLAVE,
    'user'     => DB_USER,
    'password' => DB_PASSWORD,
    'name'     => DB_NAME,
    'write' => 0,
    'read' => 1,
    'dataset' => 'global',
    'timeout' => 0.2,
));

I can get the site to come up when the master is down, BUT if the slave goes down the entire site goes down. Meaning if the master is up and the slave is down, the site is down.

score 1 · Accepted Answer · answered Jun 24 '14 at 20:39

I'm using Tungsten Replicator with 3 MySQL servers across the world (all masters configuration). This configuration is working for me.

MySQL.Cluster resolves to virtual IP of "main" node in corosync cluster - it's better to write to only one master :)

$wpdb->add_database(array(
        'host'     => 'MySQL.Cluster',     // If port is other than 3306, use host:port.
        'user'     => DB_USER,
        'password' => DB_PASSWORD,
        'name'     => DB_NAME,
        'write'    => 1,
        'read'     => 2,
        'dataset'  => 'global',
        'timeout'  => 0.2,
));


$wpdb->add_database(array(
        'host'     => '127.0.0.1',     // If port is other than 3306, use host:port.
        'user'     => DB_USER,
        'password' => DB_PASSWORD,
        'name'     => DB_NAME,
        'write'    => 2,
        'read'     => 1,
        'dataset'  => 'global',
        'timeout'  => 0.1,
));

HyperDB Configuration Master/Slave

1 Answers1