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>;
