How to handle wordpress database failure by connecting to backup database automatically

Question

I have database back-up kept in 3 different servers.

Whenever a database failure happens in the currently connected database server, I want my site to connect to the next specified database server automatically. Also the failure should be notified to the specified email.

Like that each database failure should be handled by connecting to the next available database server till the failure is handled. If all three servers fail, it can show Wordpress default message "Error establishing database connection".

I didn't downvote it, but your question doesn't appear to meet Stack Overflow guidelines. Questions should show *evidence of research and attempts to solve the problem yourself*, a clear outline of your *specific* coding-related issue, and any relevant code in a [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve), so we have enough information to be able to help. At the moment your question reads like a shopping list of requirements. — FluffyKitten, Oct 01 '17 at 06:13
As above. But also I'd be looking to change host if database connection errors kept occurring like you mention... :-O — Mat, Oct 01 '17 at 13:32
Lookup Automatic Failover. You can use a lot of different techniques for this, including DNS failover. AWS uses this for their own RDS failover:http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.MultiAZ.html#Concepts.MultiAZ.Failover — Mazzy, Oct 08 '17 at 22:48
you can use proxySQL ,best way is replication and use proxysql on top of them , and put many server on failure node — Honarkhah, Oct 09 '17 at 10:32
@Leo T Abraham https://torbjornzetterlund.com/how-to-setup-a-mysql-replication-database-for-wordpress/ might help you.. — Mohammedshafeek C S, Oct 10 '17 at 21:11

score 2 · Answer 1 · answered Oct 05 '17 at 10:08

Though I'd try to get to a more stable environment as well, you should be able to do this. Here's my idea:

$wpdb is set in require_wp_db() (wp-includes/load.php). If a file named "db.php" exists in WP_CONTENT_DIR (usually wp-content), it will be included before $wpdb is created.

Add a class in db.php that extends wpdb and override db_connect with custom code to change host, credentials etc depending on $this->reconnect_retries and then use parent::db_connect(). Instantiate $wpdb with your db-class.

I haven't tested this, but I don't see why it shouldn't work.

i don't know why you are not getting love for this, though it is sketchy af, the question asks for a sketchy answer, and this SHOULD work indeed,, so @Leo T Abraham, if there is a way without editing core files (bad idea), this is it. your custom class will be able to try different connections before a total failure with this. — Ralph Thomas Hopper, Oct 10 '17 at 19:18

score 0 · Answer 2 · answered Oct 04 '17 at 06:23

0

You can use many open source tools for failover, for MySQL automated failover I would recommend orchestrator

answered Oct 04 '17 at 06:23

Khurram Naseem

19
4

score 0 · Answer 3 · answered Oct 08 '17 at 12:05

If you are trying to achieve from php side, then only thing I can tell is, implement something like load balance or kind of distributed system.

What that means ?

Assuming you have 3 database servers and they are all in sync. When you are establishing connection to database you can user different server on different user/request. This way, you can avoid database server being overloaded.

Implementation

You could maintain log of active user on each database server and according you open connection for new request/user.

Kudehinbu Oluwaponle · Answer 4 · 2017-10-10T00:03:49.107

This generally seems like an awkward solution, you should consider some sort of distributed system or cluster servers/databases.

But if you insist on implementing it programmatically in your codes, then you can include your connections in successive try/catch blocks.

e.g if you use PDO, you can

try {
    $con = new PDO("mysql:host=$lang[dhost];dbname=$lang[db]", $lang['user'], $lang['pass']);
} catch(PDOException $e) {

    try{second connection..........
    ......................
}

score 0 · Answer 5 · answered Oct 10 '17 at 18:56

I think that this problem is bigger than you think.

I don't have any experience, but I can tell you that this kind of things affect the way your systems work currently, and a real investigation (of actual code and servers architecture) it's needed.

I don't think this is the kind of things StackOverflow should do for you.

How to handle wordpress database failure by connecting to backup database automatically

5 Answers5