案例：
测试xtrabackup等待'Waiting for table flush'
窗口1：
select count(1) from t as a1,t as a2 ,t as a3 ;
等待很长时间
窗口2：
开始备份xtrabackup备份
2019-12-30 20:04:15.331821394 ======================== START BACKUP
查看日志：
191230 20:04:15 version_check Connected to MySQL server
191230 20:04:15 version_check Executing a version check against the server...
191230 20:04:15 version_check Done.
191230 20:04:15 Connecting to MySQL server host: 127.0.0.1, user: xtrabk, password: set, port: 13306, socket: /home/db/mysql/mysql.sock
Using server version 5.7.20-log
innobackupex version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mysql/data
xtrabackup: open files limit requested 0, set to 4096
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:24M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 4
xtrabackup: innodb_log_file_size = 1073741824
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
191230 20:04:15 >> log scanned up to (48367179)
InnoDB: Opened 4 undo tablespaces
InnoDB: 0 undo tablespaces made active
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 6 for mysql/plugin, old maximum was 4
xtrabackup: Starting 4 threads for parallel data files transfer
191230 20:04:16 [01] Streaming ./ibdata1
191230 20:04:16 [02] Streaming /mysql/data/undo001
191230 20:04:16 [03] Streaming /mysql/data/undo002
191230 20:04:16 [04] Streaming /mysql/data/undo003
191230 20:04:16 >> log scanned up to (48367179)
191230 20:04:17 >> log scanned up to (48367179)
191230 20:04:18 [03] ...done
191230 20:04:18 >> log scanned up to (48367179)
191230 20:04:19 [02] ...done
191230 20:04:19 [02] Streaming /mysql/data/undo004
191230 20:04:19 [04] ...done
191230 20:04:19 [04] Streaming ./mysql/plugin.ibd
191230 20:04:19 [04] ...done
191230 20:04:19 [03] Streaming ./mysql/servers.ibd
191230 20:04:19 [01] ...done
191230 20:04:19 [03] ...done
191230 20:04:19 >> log scanned up to (48367179)
191230 20:04:20 [02] ...done
191230 20:04:20 [02] Streaming ./mysql/help_category.ibd
191230 20:04:20 [02] ...done
191230 20:04:20 [04] Streaming ./mysql/help_relation.ibd
191230 20:04:20 [04] ...done
191230 20:04:20 [01] Streaming ./mysql/time_zone.ibd
191230 20:04:20 [01] ...done
191230 20:04:20 [02] Streaming ./mysql/time_zone_name.ibd
191230 20:04:20 [03] Streaming ./mysql/help_topic.ibd
191230 20:04:20 [02] ...done
191230 20:04:20 [04] Streaming ./mysql/help_keyword.ibd
191230 20:04:20 [01] Streaming ./mysql/time_zone_transition.ibd
191230 20:04:20 [04] ...done
191230 20:04:20 [02] Streaming ./mysql/time_zone_leap_second.ibd
191230 20:04:20 [01] ...done
191230 20:04:20 [02] ...done
191230 20:04:20 [04] Streaming ./mysql/innodb_index_stats.ibd
191230 20:04:20 [04] ...done
191230 20:04:20 [01] Streaming ./mysql/innodb_table_stats.ibd
191230 20:04:20 [01] ...done
191230 20:04:20 [04] Streaming ./mysql/gtid_executed.ibd
191230 20:04:20 [04] ...done
191230 20:04:20 [03] ...done
191230 20:04:20 [01] Streaming ./mysql/time_zone_transition_type.ibd
191230 20:04:20 [01] ...done
191230 20:04:20 [02] Streaming ./mysql/server_cost.ibd
191230 20:04:20 [02] ...done
191230 20:04:20 [04] Streaming ./mysql/slave_master_info.ibd
191230 20:04:20 [04] ...done
191230 20:04:20 [02] Streaming ./mysql/engine_cost.ibd
191230 20:04:20 [01] Streaming ./mysql/slave_worker_info.ibd
191230 20:04:20 [04] Streaming ./mysql/slave_relay_log_info.ibd
191230 20:04:20 [01] ...done
191230 20:04:20 [04] ...done
191230 20:04:20 [02] ...done
191230 20:04:20 >> log scanned up to (48367179)
191230 20:04:21 [02] Streaming ./percona/checksums.ibd
191230 20:04:21 [02] ...done
191230 20:04:21 [03] Streaming ./test/t.ibd
191230 20:04:21 [04] Streaming ./mha/z.ibd
191230 20:04:21 [04] ...done
191230 20:04:21 [01] Streaming ./sys/sys_config.ibd
191230 20:04:21 [01] ...done
191230 20:04:21 >> log scanned up to (48367179)
191230 20:04:22 >> log scanned up to (48367179)
191230 20:04:23 >> log scanned up to (48367179)
191230 20:04:24 >> log scanned up to (48367179)
191230 20:04:25 >> log scanned up to (48367179)
191230 20:04:26 [03] ...done
191230 20:04:26 >> log scanned up to (48367179)
191230 20:04:27 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
191230 20:04:27 >> log scanned up to (48367179)
191230 20:04:28 >> log scanned up to (48367179)
191230 20:04:29 >> log scanned up to (48367179)
191230 20:04:30 >> log scanned up to (48367179)
191230 20:04:31 >> log scanned up to (48367179)
从上看xtrabackup 备份完innodb表之后，执行Executing FLUSH NO_WRITE_TO_BINLOG TABLES 后，就一直
log scanned up to (48367179)
log scanned up to (48367179)
log scanned up to (48367179)
log scanned up to (48367179)
备份文件大小没变
查看会话信息：
root@localhost20:15:29[(none)]>show processlist ;
+----+--------+-----------------------+------+------------------+-------+---------------------------------------------------------------+-----------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+--------+-----------------------+------+------------------+-------+---------------------------------------------------------------+-----------------------------------------------+
| 22 | root | localhost | test | Query | 700 | Sending data | select count(1) from t as a1,t as a2 ,t as a3 |
| 26 | xtrabk | localhost:43748 | NULL | Query | 665 | Waiting for table flush | FLUSH NO_WRITE_TO_BINLOG TABLES |
| 27 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+--------+-----------------------+------+------------------+-------+---------------------------------------------------------------+-----------------------------------------------+xtrabk用户是xtrabackup访问库的用户，状态一直是Waiting for table flush ，一直在执行FLUSH NO_WRITE_TO_BINLOG TABLES
FLUSH NO_WRITE_TO_BINLOG TABLES 是抑制写表的binlog日志
正常备份过程:
1,开启thread
2,streaming ./ibdata1
3,streaming ./undo
4,log scanned up to
5,备份innodb表
6,执行完FLUSH NO_WRITE_TO_BINLOG TABLES后，
7,开始Executing FLUSH TABLES WITH READ LOCK，
8,备份non-InnoDB tables 和 files
9,FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS
10,Executing UNLOCK TABLES
11,Transaction log of lsn (48367170) to (48367179) was copied
91230 20:22:20 completed OK!
将窗口1 查询退出ctrl+c， 窗口2备份 ，开始正常执行。
+----+------+-----------------------+--------------------+------------------+-------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------------+--------------------+------------------+-------+---------------------------------------------------------------+------------------+
| 22 | root | localhost | test | Sleep | 3636 | | NULL |
| 27 | root | localhost | information_schema | Query | 0 | starting | show processlist |
+----+------+-----------------------+--------------------+------------------+-------+---------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)如下内容来自于：https://www.cnblogs.com/moss_tan_jun/p/9886506.html
SET SESSION lock_wait_timeout=31536000的作用是：因为如果某个会话中使用了lock tables语句对某表加了锁，或者某个会话在进行DDL，又或者某个会话正在进行一个大的事务，那么flush tables和flush tables with read lock会被阻塞。设置锁等待时间是为了防止innobackup执行获取全局锁超时而导致备份失败退出。
FLUSH NO_WRITE_TO_BINLOG TABLES的作用是： 关闭所有打开的表，强制关闭所有正在使用的表，并刷新查询缓存和预准备语句缓存。还会从查询缓存中删除查询结果。默认情况下flush语句会写入binlog，这里使用no_write_to_binlog禁止记录。查看Binlog发现，binlog内真的啥都没记录。
FLUSH TABLES WITH READ LOCK的作用是：关闭所有被打开的表，并且使用全局锁锁住所有库的所有表（锁住之后只能被select，不能做其他操作）。当我们备份或者需要数据库的一致状态时，这个是最高效的方式。如果有事务存在，那么该事务提交时会hang住，不会回滚。但是不会阻止数据库往log tables（比如general_log和slow log）里插入数据。
FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS的作用是：将innodb层的重做日志持久化到磁盘，然后再进行拷贝。说白了就是在所有的事务表和非事务表备份完成，获取全局读锁，且使用了show master status语句获取了binlog的pos之后，执行刷新redo log buffer中的日志到磁盘中，然后redo log copy线程拷贝这最后的redo log日志数据。为啥这样数据就是完整的？因为获取了全局读锁到unlock tables释放之前，不会再有请求进来。
UNLOCK TABLES的作用是：释放全局读锁。
在flush tables with read lock和unlock tables之间，执行了下面操作
a、 拷贝所有非事务表，如系统MyISAM表
b、 将redo log buffer落盘
c、 拷贝redo log
扫一扫
1435
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
