6

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?

Jake Wilson
  • 88,616
  • 93
  • 252
  • 370

1 Answers1

3

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.

ajreal
  • 46,720
  • 11
  • 89
  • 119
  • If you don't have any control over the live MySQL server configuration, for example in a shared web hosting environment, is replication still an option? – Jake Wilson Dec 16 '11 at 16:34
  • This is problem then, The hosting price is attractive nowadays, I dun think it will be hard to get one that is good and cheap. Between, what are the hosting company you are using? – ajreal Dec 16 '11 at 16:39
  • Rackspace Cloud Sites. It's good cause your site(s) magically scales well with traffic but they limit you on features a lot. No SSH, RSYNC, SFTP, MySQL configuration, etc. We might have to choose a separate host just for the database hosting alone. – Jake Wilson Dec 16 '11 at 19:26