Best practice for writing site database during cold backups?

Question

Let's say you have a large, popular database website. There are people on the site all day and all night. They gain access to pages that are read and written to the database.

When implementing a daily database backup plan, cold backups are required, which means that you temporarily close databases or lock tables during backups. This ensures that new data will not be inserted into the database during backup.

What are some good approaches to coding the controllers and models of your site so that they do not fail due to locking tables or an offline database? Do you need to implement some kind of database writing system or something like that? Or just temporarily release the entire site? Or just make real-time backups without blocking or closing 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 the balance between A, B and C
• during backup, your application should NOT read / write from A
  • free A from the master, advance B to the master, change slave C to listen to B
  • use A for backup
  • after backup
  • change A to listen from B (which means that A is now becoming subordinate)
• when A catches up with B, (without delay), change your application to load balance between A, B, C again

On the second day, just repeat the whole cycle, of course the master is now B.