Big Database backup best practice

Question

I maintain big MySQL database. I need to backup it every night, but the DB is active all the time. There are queries from users. Now I just disable the website and then do a backup, but this is very bad as the service is disabled and users don't like this.

What is a good way to backup the data if data is changed during the backup?

What is best practice for this?

Answer 1

I've implemented this scheme using a read-only replication slave of my database server.

MySQL Database Replication is pretty easy to set up and monitor. You can set it up to get all changes made to your production database, then take it off-line nightly to make a backup.

The Replication Slave server can be brought up as read-only to ensure that no changes can be made to it directly.

There are other ways of doing this that don't require the replication slave, but in my experience that was a pretty solid way of solving this problem.

Here's a link to the docs on MySQL Replication.