Innodb
Galera / InnoDB：“mysqldump：無法執行’FLUSH TABLES WITH READ LOCK’：超過鎖定等待超時”怎麼會發生？
帶有 MariaDB 5.5 的 Galera 3 節點集群
錯誤
mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Lock wait timeout exceeded; try restarting transaction (1205)備份命令
mysqldump --max_allowed_packet=1G -u root -pdbpassword --opt --flush-logs --single-transaction --all-databases | bzcat -zc > /var/lib/mysql/backup/fullbackup`date +%Y%m%d-%H%M%S`.sql.bz2我發現只有XtraBackup Failing with Lock wait timeout從 Percona Discussion group
對此最可能的解釋是您有一個長時間執行的語句（甚至是 SELECT）。
innobackupex 用於
FLUSH TABLES WITH READ LOCK確保在備份非事務表之前對其進行獨占訪問。但是 FTWRL 要求沒有語句在執行，它會一直等到所有目前語句完成。例如，如果您有一個執行 100 秒的 SELECT，那麼 FTWRL 可能會在它成功之前放棄。因此 innobackupex 將無法完成對非事務性表（例如 mysql.*）的備份。如果您自己呼叫 innobackupex，解決方法是在命令行上使用 –no-lock 選項。但據我所知，當 PXC 將 innobackupex 作為 SST 方法呼叫時，這是不可能的。
只有
mysqldb 是MyISAM/沒有客戶數據庫。> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'MyISAM'; +---------------+--------------+---------------------------+------------+--------+---------+------------+------------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------------------------------------------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | +---------------+--------------+---------------------------+------------+--------+---------+------------+------------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------------------------------------------------+ | def | mysql | columns_priv | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 227994731135631359 | 4096 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_bin | NULL | | Column privileges | | def | mysql | db | BASE TABLE | MyISAM | 10 | Fixed | 422 | 440 | 186560 | 123848989752688639 | 58368 | 880 | NULL | 2016-04-06 17:50:04 | 2017-05-03 00:16:26 | 2016-04-06 17:50:04 | utf8_bin | NULL | | Database privileges | | def | mysql | event | BASE TABLE | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 2048 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_general_ci | NULL | | Events | | def | mysql | func | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 162974011515469823 | 1024 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_bin | NULL | | User defined functions | | def | mysql | help_category | BASE TABLE | MyISAM | 10 | Dynamic | 39 | 28 | 1092 | 281474976710655 | 3072 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_general_ci | NULL | | help categories | | def | mysql | help_keyword | BASE TABLE | MyISAM | 10 | Fixed | 464 | 197 | 91408 | 55450570411999231 | 16384 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_general_ci | NULL | | help keywords | | def | mysql | help_relation | BASE TABLE | MyISAM | 10 | Fixed | 1028 | 9 | 9252 | 2533274790395903 | 19456 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_general_ci | NULL | | keyword-topic relation | | def | mysql | help_topic | BASE TABLE | MyISAM | 10 | Dynamic | 508 | 886 | 450388 | 281474976710655 | 20480 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_general_ci | NULL | | help topics | | def | mysql | host | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 110056715893866495 | 2048 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_bin | NULL | | Host privileges; Merged with database privileges | | def | mysql | ndb_binlog_index | BASE TABLE | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | latin1_swedish_ci | NULL | | | | def | mysql | plugin | BASE TABLE | MyISAM | 10 | Dynamic | 1 | 32 | 32 | 281474976710655 | 2048 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_general_ci | NULL | | MySQL plugins | | def | mysql | proc | BASE TABLE | MyISAM | 10 | Dynamic | 3 | 1208 | 3624 | 281474976710655 | 4096 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_general_ci | NULL | | Stored Procedures | | def | mysql | procs_priv | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 239253730204057599 | 4096 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_bin | NULL | | Procedure privileges | | def | mysql | proxies_priv | BASE TABLE | MyISAM | 10 | Fixed | 1 | 693 | 1386 | 195062158860484607 | 5120 | 693 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | 2016-04-06 17:50:04 | utf8_bin | NULL | | User proxy privileges | | def | mysql | servers | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 433752939111120895 | 1024 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_general_ci | NULL | | MySQL Foreign Servers table | | def | mysql | tables_priv | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 239535205180768255 | 4096 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_bin | NULL | | Table privileges | | def | mysql | time_zone | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 1970324836974591 | 1024 | 0 | 1 | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_general_ci | NULL | | Time zones | | def | mysql | time_zone_leap_second | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 3659174697238527 | 1024 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_general_ci | NULL | | Leap seconds information for time zones | | def | mysql | time_zone_name | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 55450570411999231 | 1024 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_general_ci | NULL | | Time zone names | | def | mysql | time_zone_transition | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 4785074604081151 | 1024 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_general_ci | NULL | | Time zone transitions | | def | mysql | time_zone_transition_type | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 10696049115004927 | 1024 | 0 | NULL | 2016-04-06 17:50:04 | 2017-04-27 17:09:29 | NULL | utf8_general_ci | NULL | | Time zone transition types | | def | mysql | user | BASE TABLE | MyISAM | 10 | Dynamic | 424 | 120 | 51132 | 281474976710655 | 17408 | 240 | NULL | 2016-04-06 17:50:04 | 2017-05-03 00:16:26 | NULL | utf8_bin | NULL | | Users and global privileges | +---------------+--------------+---------------------------+------------+--------+---------+------------+------------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------------------------------------------------+ 22 rows in set, 3 warnings (21.77 sec)為什麼
mysqldump使用FLUSH TABLES WITH READ LOCK？如何調試此錯誤？
對於 mysqldump，
FLUSH TABLES WITH READ LOCK僅與--master-data或--flush-logs與 一起發出--single-transaction。要調試，只需打開通用查詢日誌 (
general_log=1)，您應該會看到 MySQL 執行此語句的相應行。
而不是使用單個事務和刷新日誌，您可以通過
--master-data=2This will help you get the correct table contents andbinary log and position. 轉儲完成後，您可以看到 bin 日誌文件名及其在轉儲中的位置。