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?
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.
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
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
The upscript would be scripted to do the following
The downscript would be scripted to do the following
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 tables that use FULLTEXT indexes cannot be converted. In the future, MySQL 5.6 is supposed to support FULLTEXT indexing in InnoDB.