[HyperDB] Replication and Failover

[Mustafa Suphi Yilmaz]

Dear Masters,
We are planning to setup two servers acting both as master and slave. To do
so, we were planning to manually configure both servers but we had a lot of
worries like auto-increment values, failover and so on. Then, we found out
about HyperDB, you might imagine that we got really excited. But the
installation guide is quite poor, so, I want to share my concerns and await
for your help on this matter.

Our plan is the following :

1)  2 IP addresses in DNS, one for each server.  When DNS is set up with
more than 1 IP address, they are given out by the DNS servers in round robin
order.  This will spread the load randomly over both servers.

2)  Both servers set up as a MySQL master and as a slave to the other one.
As I mentioned earlier, the problem with this is only auto-increment
columns. Each server will check the other once per minute and obtain any
updates (only new MySQL writes).

3)  There needs to be some mechanism to synchronize file system writes and
uploads.  There are many ways to do this.  The most sophisticated way would
be to set up server to server mirroring of one partition, where the Word
Press files are.  I would use the same software already in use for disk
mirroring, ggated.  However, I have never worked with this and there may be
pitfalls. rsync might be a better idea.  One minor disadvantage of all the
synchronization software I know about is that a master - slave relationship
is always assumed.

When I looked up on the db_settings.php of HyperDB, add_server() values
don't include any options about failover, and when I look into the code of
db.php, I don't see how HyberDB handles auto-increment. Should the following
configuration works for this kinda setup? What do you think?

add_db_server( 'global', 0, 'nyc1',  0, 1,  'global.db.example.com:3722',
   'global.db.example.lan:3722',      'global-db1',  'global-db1-user',
password');
add_db_server( 'slave',  0, 'nyc1,  1, 1,  'global.db.example.com:3722',
   'global.db.example.lan:3722',      'slave-db1',  'slave-db1-user',
'password');
add_db_server( 'global', 0, 'nyc2', 0, 1,  'slave.db.example.com:3509',
'slave.db.example.lan:3509',       'global-db2', 'global-db2-user',
'password');
add_db_server( 'slave',  0, 'nyc2', 1, 1,  'slave.db.example.com:3509',
'slave.db.example.lan:3509',       'slave-db2',   'slave-db2-user',
'password');

I also want to add a way of disabling writes to the databases when one of
the servers is down. Any ideas?

Best Wishes,
Mustafa Suphi Yilmaz
http://paulocoelhoblog.com