2 comments

1. What you’re talking about is a database fallback. It’s possible to set something like this up, but not directly with WordPress.

   How it would work

   Instead of accessing the database directly, you’d access a proxy. This proxy would sit in front of both your primary and backup databases and pass traffic through. Essentially, it would work as a load balancer for your databases.

   If traffic spikes and your primary DB goes down, the proxy would forward traffic instead to your backup.

   There are several problems with this approach, though, in terms of data consistency and maintenance. I recommend, if you intend to pursue this, to contract with a good sysadmin or host to configure everything for you.

   A better way

   If you’re concerned about a traffic hike killing the database, I recommend you instead set up a caching system. I use W3 Total Cache on my own sites. It caches requests to the database so that they don’t need to hit the database each time.

   Unless I’m actively changing the site, serving cached data is find. It saves on DB traffic and insulates my sites from performance issues when traffic spikes.

   Log in to Reply

2. You could do one of two things:

   Setup Manual Failover

   Setup MySQL Circular Replication with a DBVIP point at one of the DB Servers. The other DB Server would be used as a passive but hot standby. You would have to limit all reads and writes to the DBVIP. Suppose the DBVIP you want is 10.1.2.30. You would simply run this on whichever DB Server would server as the Master

   DBVIP=10.1.2.30
   ip addr add ${DBVIP}/24 dev eth1
   

   Should the Master go down (crash, reboot, etc), run these two commands on the other DB Server. ou would then have sysadmins setup the Old Master as the new Slave by reestablishing Circular Replication.

   Setup Automatic Failover

   Using DRBD and ucarp, you can configure two DB servers with disk level redundancy. Again, You would have to limit all reads and writes to the DBVIP. The DBVIP and automatic failover would be mananged by ucarp by you supplying two scripts

   upscript

   The upscript would be scripted to do the following

   • disconnect DRBD
   • promote DBRD to Primary
   • assume the DBVIP (by running ip addr add)
   • mount DRBD on /var/lib/mysql
   • service mysql start

   downscript

   The downscript would be scripted to do the following

   • service mysql stop
   • umount /var/lib/mysql
   • ip addr del the DBVIP
   • disconnect DRBD
   • demote DRBD to Secondary

   caveat on automatic failover

   To preserve all MySQL data to survive unexpected failovers in a consistent state, convert all MyISAM tables to InnoDB. Here is the script to do so:

   MYISAM_TO_INNODB_CONVERSION_SCRIPT=/root/ConvertMyISAMToInnoDB.sql
   echo "SET SQL_LOG_BIN = 0;" > ${MYISAM_TO_INNODB_CONVERSION_SCRIPT}
   mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" >> ${MYISAM_TO_INNODB_CONVERSION_SCRIPT}
   

   MyISAM tables that use FULLTEXT indexes cannot be converted. In the future, MySQL 5.6 is supposed to support FULLTEXT indexing in InnoDB.

   Log in to Reply