Big Database backup best practice

mysql database backup database-backups
Roman Gelembjuk picture Roman Gelembjuk · May 8, 2012 · Viewed 22k times · Source

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

Kevin Bedell picture Kevin Bedell · May 8, 2012

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.

Related questions

mysqldump with create database line

I'm in the process of moving my files onto another computer, and one thing I would like to do is to transfer the data in my MySQL database. I want to dump the databases into .sql files, but also have …

Read More
MySQL Database backup automatically on a windows server

Is there a way to back up MySQL database automatically at certain times of the day for designated servers or send an email with an attachment.. Which ever do you think is the best and safest way to achieve this?

Read More
How to backup MySQL database on a remote server?

I have a MySQL database existing on a remote server. I only have sql connection privilege. I don't have FTP access to the server, and I need to do a complete dump of the database. I have tried mysqldump, but …

Read More