Ensuring Data Integrity While Restoring Database (Dump + Binary Log)

Databases
,
1

I have been thinking about restoring a MySQL database, and ensuring data integrity. So basically during restoring a database to a snapshot, using a sql dump, it does the locking etc, so you can be sure the database is in the state you expect. But then if you want to restore the database further using the Binary Log, there may be a time gap between finishing the sql dump and starting the Binary Log commands, that a user or process might be able to change data in the database, and destroy data integrity.

So there has to be a way to stop anything from making changes to the database until the full restore process is complete. I can think of a few ways:

  1. Write the dump file so that it does its statements in a transaction, and before the transaction is committed, create a sql file from the appropriate Binary Logs, which is executed too. Commit the changes after the whole thing is finished.
  2. You would normally shut down any applications that might be using the database anyway. But in addition, to be absolutely sure that nothing is accessing the database during the process of restoring it, you could copy the mysql.user table to a backup table, change all the users passwords to a temporary master password, start the process of restoring the database and when finished, restore the users original passwords, and start the applications.

I like option 2 because it ensures without doubt that no process will be able to change anything until you are finished restoring. It is simple, easy, and powerful.

My question is:
Is there a better way?

2

There is a better way, I am just writing up a tutorial on my personal website. I will share my method here when I have finished.

3

just check our rules for self-promotion first :wink:

4

Hi RT_ I’ve never had to deal with backing up a large “live” database but I’m interested in reading your findings.

Don’t be intimidated by molona, as long as you discuss things here instead of simply posting a “read it here” link you’ll be fine :thumbsup:

5

Thanks Mittineague.

I seem to be very busy at the moment, but will get the tutorial up asap. As I understand the documentation, you can actually backup a live MySQL database very easily without having to block anyone or turn anything off, providing all your tables are InnoDB (Default setting in MySQL 5.5+). Just execute this statement:

mysqldump -u root -p --single-transaction Your_Database_Name > your_sql_backup_file_name.sql

Because it is done as a single transaction, rather than locking all the tables, so other sessions can still use the database.

It is restoring the database while ensuring data integrity that seems a little tricky, and not well documented.

RT_ :slight_smile:

6

Sorry I haven’t posted the tutorial yet. I’ve been busy starting up a new company. Will try and finish it this week.

7

For those who are interested:

http://www.ralphturner.me.uk/mysqlBackup.html

Let me know if you spot any problems, although I have run through it I expect there is a mistake or two in there somewhere.