Best practice for website database writes during cold backups?

Question

Lets say you have a large, popular database-driven website. There are people on the site all day and all night. They access pages that both read and write to the database.

When implementing a daily database backup plan, it involves doing cold backups, which means that you temporarily shutdown the database or lock tables while the backup is in progress. This ensures that new data is not inserted into the database while the backup is happening.

What are some good approaches to coding your site controllers and models so that they don't fail due to table locks or the database being offline? Do you need to implement some sort of database write queue system or something like that? Or just take the whole site offline temporarily? Or just do live backups without locking or shutting down anything? What is a good approach?

Answer 1

Use replication, here is a simple scenario

1 master A, 2 slaves B, C

• during normal operation, your application should load balance between A,B and C
• during back-up, your application should NOT read / write from A
  • relieve A from being master, promote B to master, change slave C to listen on B
  • use A for backup
  • after backup done
  • change A to listen from B (that's mean A become slave from now onward)
• when A is catch up with B, (no more delay), change your application to load balance again between A,B,C

For Day 2, just repeat the whole cycle, of course master is now B.