mysqldump对mysql数据库的影响

对于想入门或者初级，中级mysql数据库运维人员，了解mysqldump对mysql数据库的影响，是非常必要的，当执行mysqldump命令之后，mysql后台执行了什么，下面就带大家看看，在这里使用general_log进行分析

1.首先的开启数据库的general_log，如下所示

[root@localhost] 17:30:41 [(none)]>show variables like 'general_log%';
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | OFF                             |
| general_log_file | /data/mysql/data/3306/mysql.log |
+------------------+---------------------------------+
2 rows in set (0.00 sec)

开启general_log
[root@localhost] 17:41:40 [(none)]>set global general_log=on;
Query OK, 0 rows affected (0.42 sec)

[root@localhost] 17:41:50 [(none)]>show variables like 'general_log%';
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | ON                              |
| general_log_file | /data/mysql/data/3306/mysql.log |
+------------------+---------------------------------+
2 rows in set (0.00 sec)

2.执行mysqldump备份命令

mysqldump --socket=/data/mysql/run/3306/mysql.sock --skip-tz-utc --all-databases --single-transaction -ER -u root -proot> db_script.sql

执行之后，看看general_log的内容

2020-08-20T17:44:58.473668+08:00            6 Connect   root@localhost on  using Socket
2020-08-20T17:44:58.473911+08:00            6 Query     /*!40100 SET @@SQL_MODE='' */
2020-08-20T17:44:58.474234+08:00            6 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2020-08-20T17:44:58.474325+08:00            6 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2020-08-20T17:44:58.474514+08:00            6 Query     SHOW VARIABLES LIKE 'gtid\_mode'
2020-08-20T17:44:58.478784+08:00            6 Query     SELECT @@GLOBAL.GTID_EXECUTED
2020-08-20T17:44:58.478972+08:00            6 Query     UNLOCK TABLES

备份命令首先设置会话的隔离级别为RR，然后开始设置事物一致性快照，看到这里，有同学会问，为什么后台没有执行FTWRL（flush table with read lock），这是因为备份的时候没有添加--master-data参数，那看看添加这个参数之后，会有什么不同呢

mysqldump --socket=/data/mysql/run/3306/mysql.sock  --master-data=2 --skip-tz-utc --all-databases --single-transaction -ER -u root -proot> db_script.sql

再次看看general_log的内容

2020-08-20T17:52:33.612990+08:00            7 Connect   root@localhost on  using Socket
2020-08-20T17:52:33.613504+08:00            7 Query     /*!40100 SET @@SQL_MODE='' */
2020-08-20T17:52:33.614493+08:00            7 Query     FLUSH /*!40101 LOCAL */ TABLES
2020-08-20T17:52:33.617383+08:00            7 Query     FLUSH TABLES WITH READ LOCK
2020-08-20T17:52:33.617559+08:00            7 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2020-08-20T17:52:33.617628+08:00            7 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2020-08-20T17:52:33.617804+08:00            7 Query     SHOW VARIABLES LIKE 'gtid\_mode'
2020-08-20T17:52:33.625696+08:00            7 Query     SELECT @@GLOBAL.GTID_EXECUTED
2020-08-20T17:52:33.625876+08:00            7 Query     SHOW MASTER STATUS
2020-08-20T17:52:33.626051+08:00            7 Query     UNLOCK TABLES

这里就会发现，多了2个操作
FLUSH /*!40101 LOCAL */ TABLES
FLUSH TABLES WITH READ LOCK

这是因为添加--master-data参数，是表示从复制的主库上导出备份，可以利用此 备份文件直接建立另一个复制从库，FLUSH TABLES WITH READ LOCK主要用于备份工具获取一致性备份(数据与binlog位点匹配 )。

在这里非常有必要了解一下FLUSH TABLES WITH READ LOCK，因为操作不好，所有DML，DDL，for Update都会被堵塞，造成业务无法做任何操作

FTWRL主要包括3个步骤：
1.上全局读锁(lock_global_read_lock)
2.清理表缓存(close_cached_tables)
3.上全局COMMIT锁(make_global_read_lock_block_commit)

在第一个步骤中，由于上的是全局读锁，他会堵塞所有的DML，DDL，for Update操作，但是这一步非常快，业务感觉不到

在第二个步骤中，清理表缓存，在这里需要注意了，如果有一个慢查询，FTWRL就会被堵塞了，而且，涉及的表上所有的操作，是所有的操作，包括select，都会被堵塞，而且现在就算终止mysqldump备份命令，堵塞也不会解除，除非你kill掉慢查询语句，所以在凌晨做备份的同学，可以要注意了

在第三个操作中，会申请全局commit锁，如果这个时候有大事物，长时间没有提交，FTWRL就会被堵塞了

综上所述，在执行mysqldump命令之前，先检查一下，数据库中是否有慢sql，长时间未提交的大事物。