2

I have a hosted website (Namecheap Pro shared hosting) that connects to a remote MySQL database using a read-only user privilege. This remote DB is updated by a separate system. The website works great until the remote DB goes down (Which it does now and again - cannot be resolved), at which point my website also fails with a PHP MySQL DB connect error.

To resolve this problem, I intend to create a copy of the remote DB locally on the hosted website, and synchronise this copy every hour. And then, when the website is reverting to the local copy, a message will appear on the screen informing the user that they are seeing cached data.

So far so good, except that I'm trying to get the website to test the remote DB first and if that fails, then connect to the local copy, but I can't figure out how to do a simple and quick test to establish this; my code just bombs out on a MySQL connect error after a page time-out when trying the remote DB first.

Of course, creating a replicated slave DB updated instantly when the remote DB is updated, and then pointing the website always and only at the slave would be ideal, but there are technical problems preventing this right now.

Any ideas on how to test a remote MySQL DB connection quickly so that a fallback local (copy) DB can be connected without much disruption the the user experience?

Improve this question

1 Answer 1

Reset to default
1

Well, on the mysqli constructor page for the php manual you have several examples on how to deal with connection errors, depending on the PHP version. For example, on the first code snippet, change the die() to

if ($mysqli->connect_error) {
    //try connecting to the backup node
    $mysqli = new mysqli('backup_node_host', 'my_user', 'my_password', 'my_db');
    ...
}

However, doing everything programmatically has its disadvantages, and as you are on DBA.stackexchange, and not on stackoverflow, I could suggest an alternative setup:

 read-only      read/write 
application     application
     |              |
     |              |
 HA Proxy           |
     |\             |
     | \            |
     |  \           |
     |   \          |
     |    \         |
     |     \        |
     |      \       |
     |       \      |
     |        \     |
     |         \    |
     |          \   |
     |           \  |     
 read-only      read/write
MySQL slave    MySQL Master
     ^              |
     |              |
     ----------------
    MySQL asynchronous
       replication

Obviously, this may cost more than a couple of shared hosting but avoids manual intervention (imports, failover management) and there is better management and control or certain type of errors (network timouts, etc.).

Improve this answer
4
  • As an alternative, the MySQL replication and load balancing plugin php.net/manual/en/book.mysqlnd-ms.php can do most of the switchover work for you.
    – jynus
    Aug 4, 2014 at 9:58
  • Thanks for the in-depth answer :-) . The problem with $mysqli->connect_error is that PHP will execute this instruction and wait until it errors out, maybe 5 seconds, but my users will have given up waiting by then, lol!. Maybe replication is the only answer, whereby I could point my website to use only the DB copy.
    – user44632
    Aug 4, 2014 at 11:38
  • @Matt Yes, thus "has its disadvantages", that is why HA Proxy is way faster, while being transparent to the application. Replication is cheap, although many shared hosting do not allow it. Have you considered the cloud (e.g. RDS)?
    – jynus
    Aug 4, 2014 at 13:04
  • I also suspect that the shared hosting will not allow it. I'm looking into using a cron php script to do the dirty work of reading in the entire remote DB tables one by one, and truncating the local tables then dumping the entire remote tables into them, on a quarter hour update basis. But really the answer is replication, so I'll look into making that happen as it's the best solution. Thanks again.
    – user44632
    Aug 4, 2014 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.