0

Galera 3 node cluster with MariaDB 5.5

error

mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Lock wait timeout exceeded; try restarting transaction (1205)

backup command

mysqldump --max_allowed_packet=1G -u root -pPuuRuu0aetaiquoojai6 --opt --flush-logs --single-transaction  --all-databases | bzcat -zc > /var/lib/mysql/backup/fullbackup`date +%Y%m%d-%H%M%S`.sql.bz2

I found only XtraBackup Failing with Lock wait timeout exceeded from Percona Discussion group

The most likely explanation for this is that you have a long-running statement (even a SELECT).

innobackupex uses FLUSH TABLES WITH READ LOCK to ensure exclusive access to the non-transactional tables before it backs them up. But FTWRL requires that there be no statements running, and it will wait until any current statements complete. If you have a SELECT that is running for 100 seconds for example, then FTWRL may give up before it succeeds. And therefore innobackupex won't be able to complete backing up the non-transactional tables (e.g. mysql.*).

The workaround if you are invoking innobackupex yourself is to use the --no-lock option on the command-line. But as far as I know, this is not possible when innobackupex is invoked as an SST method by PXC.

Only mysql db is MyISAM / no customer databases.

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

why mysqldump uses FLUSH TABLES WITH READ LOCK? Howto debug this error?

1

For mysqldump, FLUSH TABLES WITH READ LOCK is only issue with --master-data, or --flush-logs together with --single-transaction.

To debug, simply turn on general query log (general_log=1) and you should see the corresponding line where MySQL executes this statement.

0

Instead of using single transaction and flush logs, you can pass --master-data=2 This will help you to get the correct table contents and binary log and position. Once the dump is completed you can see the bin log file name and its position inside the dump.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.