Do InnoDB tables got locked during mysqldump when mixed with MyISAM?
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?
Solution 1:
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>;
Solution 2:
mysqldump --single-transaction does not lock tables but MyISAM tables are not guaranteed to have consistent dumps with this option. It's better to use mydumper, mydumper locks MyISAM tables and does not lock InnoDB so the dump is consistent.