MySQL xtrabackup/mydumper/mysqldump备份恢复及锁说明
1.FLUSH TABLES WITH READ LOCK
大多数的备份软件在备份过程中都会执行FTWRL, 这个语句会获取一个全局读锁，从而保证数据处于一致的状态。
在大多数场景下，FTWRL获取读锁、unlock tables释放读锁的时间很短，所以通常热备份的锁在备份阶段是无感知的。
然而，FTWRL获取读锁时，需要找到一个所有的事务及查询都结束的时间点，如果获取读锁时mysql实例上有长事务或长查询，该语句将会被阻塞，FTWRL被阻塞反过来会阻塞数据库中后续的所有读写操作，甚至无法登录(因为登录时需要获取数据库中的表列表信息，除非使用-A选项)，这种场景如下：
所以在FTWRL方式的备份执行前，需要确保数据库中没有长查询，否则会锁库。mydumper有两个选项--long-query-guard和--kill-long-queries可以在存在长查询时kill掉
--long-query-guard Set long query timer in seconds, default 60
--kill-long-queries Kill long running queries (instead of aborting)
另外，Innodb的表在备份中实际上不需要FTWRL，但是如果需要获取binlog位点，则需要FTWRL
2.mysqldump
mysqldump备份中除了--lock-tables和--lock-all-tables锁表的选项之外，我们通常用到的两个选项是--single-transaction和--master-data.
其中，--single-transaction是将备份session的事务隔离级别设置为RR，然后开启一个事务进行备份从而保证备份数据库的一致性。该选项并不会执行FTWRL.
--single-transaction
This option sets the transaction isolation mode to REPEATABLE READ and sends a START
TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional
tables such as InnoDB, because then it dumps the consistent state of the database at the time when
START TRANSACTION was issued without blocking any applications.
While a --single-transaction dump is in process, to ensure a valid dump file (correct table
contents and binary log coordinates), no other connection should use the following statements:
ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent
read is not isolated from those statements, so use of them on a table to be dumped can cause the
SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents
or fail.
--master-data
该选项是为了获取备份时的binlog位点，所以加了该参数之后备份文件中会有CHANGE MASTER TO语句记录备份时binlog的位点。如果只是加了--master-data而没有加--single-transacton，则该选项默认会--lock-all-table，也就是备份期间会锁整个库。当--master-data 加了--single-transaction时，则会在备份开始时执行FTWRL获取binlog位点，然后马上unlock tables释放锁，没有长查询的情况下，这个过程几乎是无感知的。
The --master-data option automatically turns off --lock-tables. It also turns on --lockall-
tables, unless --single-transaction also is specified, in which case, a global read lock
is acquired only for a short time at the beginning of the dump (see the description for --singletransaction).
In all cases, any action on logs happens at the exact moment of the dump.
mysqldump --all-databases --master-data --single-transaction > all_databases.sql
This backup acquires a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at
the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read
and the lock is released. If long updating statements are running when the FLUSH statement is issued,
the MySQL server may get stalled until those statements finish. After that, the dump becomes lock free
and does not disturb reads and writes on the tables. If the update statements that the MySQL server
receives are short (in terms of execution time), the initial lock period should not be noticeable, even
with many updates.
3.mydumper
和mysqldump类似，mydumper备份时也需要执行FTWRL来获取binlog位点，以下是mydumper github上的说明：
This is all done following best MySQL practices and traditions:
● As a precaution, slow running queries on the server either abort the dump, or get killed
● Global write lock is acquired ("FLUSH TABLES WITH READ LOCK")
● Various metadata is read ("SHOW SLAVE STATUS","SHOW MASTER STATUS")
● Other threads connect and establish snapshots ("START TRANSACTION WITH CONSISTENT SNAPSHOT")
● Once all worker threads announce the snapshot establishment, master executes "UNLOCK TABLES" and starts queueing jobs.
4.xtrabackup
Percona的物理备份工具xtrabackup备份过程中锁相关的流程如下，是先拷贝ibd文件，文件拷贝完成后，获取binlog位点前FTWRL，获取位点、拷贝完frm文件之后unlock tables，这个过程没有long query时也是很短的。
- copy InnoDB data
- FLUSH TABLES WITH READ LOCK;
- copy .frm, MyISAM, etc.
- get the binary log coordinates
- finalize the background copy of REDO log
- UNLOCK TABLES;
总结，当前主流的mysql备份软件在备份过程中都会调用FTWRL，在没有长查询的情况下，获取全局读锁、释放锁的过程是非常短的，所以通常几乎是无感知的。但是当数据库中存在长查询时，需要特别小心，可能会导致锁整个库，甚至无法登录。
所以如果在主库备份，备份前需要检查有没有long query。
另外一种安全的方式就是在slave上备份，mysqldump(--dump-slave)/mydumper(如果从库备份默认也会记录master的binlog position)/xtrabackup(--slave-info)都有相关的选项记录master的binlog位点。
参考材料：
mysql 5.7官方文档
https://www.percona.com/blog/2012/03/23/how-flush-tables-with-read-lock-works-with-innodb-tables/
https://www.percona.com/blog/2010/04/24/how-fast-is-flush-tables-with-read-lock/
https://www.percona.com/blog/2020/05/27/best-practices-for-mysql-backups/
https://www.percona.com/blog/2014/03/11/introducing-backup-locks-percona-server-2/
