Backing up MySQL database using InnoDB Storage Engine

[prinz]

Hi,

A prospect has a MySQL database that they would like to host on a NetApp system (for faster backups) and they are wondering if the backup script that is listed in TR-3601 will properly quiesce a database that is using the InnoDB Storage Engine.

It appears that the paper and associated script really address engines that do not have their own online backup solution.  So, I'm thinking that you would need to write a different script to support the InnoDB Hot Backup solution.  Am I correct about that?

If so, does anyone know if we have written such a script that is available elsewhere?

Also, how would SnapCreator fit into this.  Is this something that we should/need to be looking at.

Thanks.

[fjohn · Accepted solution]

The script in TR-3601 places the tables in read-only mode for the backup:

#locking tables with read only mode

my $query = "flush tables with read lock";

my $sth1234 = $dbh->prepare($query);

$sth1234->execute() or die "Couldn't connect to database: $DBI::errstr\n";

$sth1234->finish();

Since the database buffers have been flushed and the database tables are in read-only mode during the backup, the script schould be compatible with a transactional database using the InnoDB engine.

J

[prinz]

Thanks for the information.

The customer did note that but was a little concerned whether it would work

for the InnoDB engine since the TR calls it out as being different.

I appreciate the clarification.

[nkarthik]

For the script and SC the storage engine doesn’t matter.

1. Before take the backup the database in read only mode, to maintain the consistency

2. Flush the logs to disk

3. Take the snapshot

4. Unlock the tables

Thanks

Karthikeyan.N

[fjohn]

That was my take on it.

As long as you flush the buffers and put the tables in hot backup (read-only) mode, you should have an application consistent backup.  InnoDB builds on top of mySQL to provide ACID transactions.  Even if the script executed during the middile of an ACID transaction, it's still consistent from the DB level and the transaction in progress would roll back at restore.

j

[prinz]

Thanks for the clarification everyone.

[nkarthik]

Hi,

The script in the 3601 and SC will not address the Innodb Engine Hot Backup. we are exploring the possibility of innodb hot backup. we will update you soon.

Thanks

Karthikeyan.N