ONTAP Discussions

Backing up MySQL database using InnoDB Storage Engine

prinz
5,679 Views

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.

1 ACCEPTED SOLUTION

fjohn
5,679 Views

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

View solution in original post

6 REPLIES 6

fjohn
5,680 Views

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
5,679 Views

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
5,679 Views

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
5,679 Views

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
5,679 Views

Thanks for the clarification everyone.

nkarthik
5,679 Views

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

Public