Mysql – Do InnoDB tables got locked during thesqldump when mixed with MyISAM

database-backupinnodbmyisamMySQL

I'm looking for a backup solution for my mysql servers and I need as less downtime as possible. I have the following:

  • MySQL servers
  • they are not replicated
  • each server stands for its own

This number can grow, so setting up a master/slave replication won't be a good idea.

The easiest backup way as I see it would be using mysqldump with a software such as "automysqlbackup". My most important data uses InnoDB. My InnoDB tables are pretty heavy.

The question is: If I do a mysqldump to all the databases in the server, will it lock my Innodb tables?

Best Answer

mysqldumps with InnoDB and MyISAM together are treated as mutually exclusive. Here is why:

If you can login to mysql while a mysqldump is in progress, you will see something like this:

SELECT /* SQL_NO_CACHE */ * FROM tblname

By default, mysqldump will do the following:

  • Every database is dumped in alphabetical order
  • Every table dumped per database is dumped in alphabetical order (regardless of storage engine)

This should be fine for a MySQL Instance that has no other database activity. InnoDB tables and MyISAM tables do not affect each other.

Using --single-transaction against an all-InnoDB MySQL Instance creates a checkpoint and dumps all tables from the same point-in-time. Once a MyISAM table is encountered, all bets are off. It could cause all InnoDB tables after the MyISAM to be dumped from a different point-in-time.

To have a consistent point-in-time dump for a mixture of InnoDB and MyISAM you have there options

OPTION #1

Restart mysql so that no one else can login via TCP/IP and then mysqldump

service mysql restart --skip-networking --skip-grant-tables
mysqldump --routines --triggers --all-databases > MySQLData.sql
service mysql restart

OPTION #2

If all MyISAM tables are for reading only, just mysqldump using --single-transaction

OPTION #3

If any MyISAM tables are are being written, --single-transaction is not enough

You will have do the following:

mysql -u... -p... -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)"
sleep 30
mysqldump --routines --triggers --all-databases > MySQLData.sql

Immediately after the mysqldump is done, login to mysql and do show processlist;. Look for the query SELECT SLEEP(86400), find process ID, and run KILL <procidnumn>;

Related Topic