mysqldump备份数据不锁表的加参数方法及原理分析

mysqldump命令的备份用法分析—锁表不锁表

mysqldump默认的字符集是utf8

/etc/my.cnf添加如下两行文件，重启mysql服务，以输出日志方便分析

general_log=on
general_log_file=/var/log/mysql/general_log

1.直接运行备份命令不加参数

如果有增删改或事物语句进行到一半还没提交，则该备份命令会被卡着。

例如：
mysqldump -u root -p wjy > wjy.sql

查看general_log，会发现锁表语句
LOCK TABLES `t1` READ 
直到最后出现解锁语句
UNLOCK TABLES 
（多个表同时最后解锁，
如果备份时间有半个小时，
那么这半个小时就不能增删改）

2.加参数--master-data(排它锁，需要先开binlog)

如果有增删改或事物语句进行到一半还没提交，加了--master-data则可以顺利备份。

例如：
mysqldump -u root -p  --master-data  wjy > wjy.sql

查看general_log，会发现锁表语句
FLUSH TABLES WITH READ LOCK  （跟上面的不同）

也是在备份完成后才解锁，
备份过程新的增删改操作也会被卡，
可能很耗时不适合生产环境。

3.加参数--master-data和--single-transaction实现毫秒级阻塞

说明start transaction和start transaction with sonsistent snapshot的区别

start transaction
不是跟执行start transaction命令的时候保存的数据一致，而是跟执行start transaction命令后第一次查询的时候数据保持一致，如果执行start transaction后增加了数据再读，也可以读到增加的数据。但是读一次后再增加数据就看不到了。

start transaction with sonsistent snapshot
是跟执行完这条命令的状态保持一致，后续的增删改都看不到。

加了--single-transaction的时候，只会阻塞毫秒级，对对应的表照了个快照从快照里面备份数据，不影响表的增删改。采用的是start transaction with sonsistent snapshot方式。

例如：
mysqldump -u root -p  --master-data  --single-transaction wjy > wjy.sql

查看general_log，会发现锁表语句后很快就解锁了。
Query	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
Query	START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
Query	SHOW VARIABLES LIKE 'gtid\_mode'
Query	SHOW MASTER STATUS
Query	UNLOCK TABLES

生成的备份文件是sql语句，可以直接执行恢复数据，或者恢复部分的sql语句。

生产备份请加参数--master-data和--single-transaction以对业务影响最小