How to make my MySQL databases available at all times? Some expert DB advice needed!

Question

I've been doing a lot of research, reading on replication, etc but just not sure as to what mysql solution would work.

This is what I'm looking at:

• when my mysql fails for some reason or there are certain queries that are taking really long to execute and locking some tables, I want the other insert/update/select queries to still function at normal speed without having to wait for locks to be released or for the main database to be back up. I'm thinking there should be a second mysql server for this to happen, but is what I mentioned possible even if there is and would it involve a lot of change in my existing programming logic?

• when my database is being backed up, I would still like my site to function normally, all inserts/selects/updates should function as normal.

• when I need to alter a large table, I wouldn't like it to affect my application, there should be a backup server to work from.

So what do I need to do to get all this done and also would it require changing plenty of existing coding to suit the new set up? [My site has a lot of reads and writes]

Answer 1

Not a MySQL specific answer, but a general one. Have a read only copy of your DB for site to render, which will be synced to the master DB regularly. This way, you can keep your site working even if the master DB is under load/lock due to insert/delete. For efficiency, keep this copy as denormalized as you can.

Answer 2

There's no easy way. You're asking for a highly-available MySQL-based setup, and that requires a lot of work at the server and client ends.

Some issues, for example:

• when I need to alter a large table, I wouldn't like it to affect my application, there should be a backup server to work from.

If you're altering the table, you can't trivially create a copy to work from during the update. What about the changes that are made to your copy while the first update is taking place?

Have a search for "High Availability MySQL". It's mostly a solved problem, but the solution depends heavily on your exact requirements. You cannot just ask for "I want my SQL server to run at full speed always forever no matter what I throw at it".