Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Is it possible to define 2 database for one wp installation, I have searched but there are options for one database and 2 installations. I want this because, one of my clients site is hosted on dedicated server, still if a traffic hike happens the database going offline. So if have two database and one installed on another host it'll be possible solution. Any help?

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
1  
Hence my "contract with a good sysadmin" advice. The majority of WP users/developers wouldn't need to know how to do this and probably shouldn't be doing it on their own anyway. –  EAMann Mar 26 '12 at 17:56
1  
Far above my MySQL level, therefore +1 ;) Sidenote: You really need to get much more into detail. This stuff is that far away from WP stuff, that most people would just read "WUT?!?". –  kaiser Mar 26 '12 at 23:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.