mysqldump对mysql数据库的影响

小咪咪 2022-11-07 04:26 215阅读 0赞

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

# 1.首先的开启数据库的general\_log，如下所示 #

**开启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，长时间未提交的大事物。