概念描述
************************************************************
mysqldump进行逻辑备份时(innodb)，为保证事务的一致性，会进行加锁，通常使用以下两个参数，
锁定一个schema对象中的所有表，默认开启，可保证一个库中所有表的状态一致
-l, --lock-tables Lock all tables for read.(Defaults to on; use --skip-lock-tables to disable.)
必须是innodb存储引擎，备份过程中不能出现DDL语句，使用该参数则自动关闭lock-tables参数
--single-transaction
Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does); the dump is NOT guaranteed to beconsistent for other storage engines. While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE, as consistent snapshot is not isolated from them. Option automatically turns off --lock-tables.补充说明：因为single-transaction 使用了“ a consistent snapshot”一致性快照，它除了在开始生成一致性快照时锁定所有表外（很小的一段时间，几秒或者更小），在后续备份到某个表时才会锁定某个表，因此它对线上业务影响小，为在线备份推荐选项
测试准备
*********************************************************************
就是设计一套测试方案，来验证一下以上的观点
两台虚拟机，分别做时间校准，red6是mysql数据库（5.7.22版本），red4是备份恢复机
[root@red6 network-scripts]# /sbin/ntpdate 182.92.12.1130 Jul 14:40:52 ntpdate[7592]: step time server 182.92.12.11 offset 182.635092 sec[root@red4 ~]# /sbin/ntpdate 182.92.12.1130 Jul 14:38:04 ntpdate[10222]: adjust time server 182.92.12.11 offset 0.002791 sec创建测试用户
create database txdb character set utf8; create user 'automng'@'%' identified by 'Automng_123';ALTER USER 'automng'@'%' IDENTIFIED WITH mysql_native_password BY 'Automng_123'; GRANT ALL PRIVILEGES ON *.* TO 'automng'@'%' WITH GRANT OPTION;sysbench初始化数据，50张表，每张5万数据
sysbench --test=/opt/soft/sysbench/sysbench/tests/db/parallel_prepare.lua --oltp-tables-count=50 --oltp-table-size=50000 --num-threads=2 --mysql-table-engine=innodb --mysql-user=automng --mysql-password=Automng_123 --mysql-port=3318 --mysql-host=192.168.56.201 --mysql-db=txdb run sysbench --test=/opt/soft/sysbench/sysbench/tests/db/parallel_prepare.lua --oltp-tables-count=50 --oltp-table-size=50000 --num-threads=2 --mysql-table-engine=innodb --mysql-user=automng --mysql-password=Automng_123 --mysql-port=3318 --mysql-host=192.168.56.201 --mysql-db=txdb cleanuplock-tables备份语句
/usr/local/mysql/bin/mysqldump -h$host -uautomng -pAutomng_123 -P$port --log-error=$bkdir/$today_bklog --set-gtid-purged=OFF --add-drop-database --add-drop-table --flush-logs --lock-tables --triggers --routines --events --flush-privileges --master-data=2 --default-character-set=$charset $db_name > $bkdir/$today_bkfile准备好的更新测试语句
select now();update sbtest10 set pad='wa ka ka' where id=11;select now();update sbtest10 set pad='1234567' where id=12;select now();update sbtest10 set pad='wa ka ka' where id=13;select now();update sbtest10 set pad='1234567' where id=14;select now();single-transaction备份语句
/usr/local/mysql/bin/mysqldump -h$host -uautomng -pAutomng_123 -P$port --log-error=$bkdir/$today_bklog --set-gtid-purged=OFF --add-drop-database --add-drop-table --flush-logs --single-transaction --triggers --routines --events --flush-privileges --master-data=2 --default-character-set=$charset $db_name > $bkdir/$today_bkfile准备好的更新测试语句select now();update sbtest21 set pad='wa ka ka' where id=21;select now();update sbtest31 set pad='1234567' where id=22;select now();update sbtest41 set pad='wa ka ka' where id=23;select now();update sbtest41 set pad='1234567' where id=24;select now();备份过程在脚本中进行，会自动打印出备份的起始时间
lock-tables参数测试
********************************************************
备份的起始时间
start backup txdb 2018-07-30 14:53:53end backup 2018-07-30 14:54:32DML语句的执行，手工复制所有语句，一次性粘贴到SQL命令行
mysql> select now();+---------------------+| now() |+---------------------+| 2018-07-30 14:53:58 |+---------------------+1 row in set (0.04 sec)mysql> update sbtest10 set pad='wa ka ka' where id=11;Query OK, 1 row affected (33.98 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select now();+---------------------+| now() |+---------------------+| 2018-07-30 14:54:32 |+---------------------+1 row in set (0.00 sec)mysql> update sbtest10 set pad='1234567' where id=12;Query OK, 1 row affected (0.17 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select now();+---------------------+| now() |+---------------------+| 2018-07-30 14:54:32 |+---------------------+1 row in set (0.00 sec)mysql> update sbtest10 set pad='wa ka ka' where id=13;Query OK, 1 row affected (0.14 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select now();+---------------------+| now() |+---------------------+| 2018-07-30 14:54:32 |+---------------------+1 row in set (0.00 sec)mysql> update sbtest10 set pad='1234567' where id=14;Query OK, 1 row affected (0.11 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select now();+---------------------+| now() |+---------------------+| 2018-07-30 14:54:32 |+---------------------+1 row in set (0.00 sec)mysql> mysql> mysql> show warnings;Empty set (0.03 sec)
由于DML每条语句的执行几乎不耗时，而第一条语句的结束时间与备份的结束时间完全吻合。印证了以上lock-tables锁全表的观点完全。
single-transaction参数测试
*******************************************************************
备份的起止时间
start backup txdb 2018-07-30 15:20:33
end backup 2018-07-30 15:21:22DML语句的执行，手工复制所有语句，一次性粘贴到SQL命令行
mysql> select now();+---------------------+| now() |+---------------------+| 2018-07-30 15:20:37 |+---------------------+1 row in set (0.00 sec)mysql> update sbtest21 set pad='wa ka ka' where id=21;Query OK, 1 row affected (0.35 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select now();+---------------------+| now() |+---------------------+| 2018-07-30 15:20:38 |+---------------------+1 row in set (0.00 sec)mysql> update sbtest31 set pad='1234567' where id=22;Query OK, 1 row affected (0.09 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select now();+---------------------+| now() |+---------------------+| 2018-07-30 15:20:38 |+---------------------+1 row in set (0.01 sec)mysql> update sbtest41 set pad='wa ka ka' where id=23;Query OK, 1 row affected (0.07 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select now();+---------------------+| now() |+---------------------+| 2018-07-30 15:20:38 |+---------------------+1 row in set (0.00 sec)mysql> update sbtest41 set pad='1234567' where id=24;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select now();+---------------------+| now() |+---------------------+| 2018-07-30 15:20:38 |+---------------------+1 row in set (0.00 sec)
33秒开始备份，37秒开始执行DML，38秒时执行完毕，DML没有被事务锁阻塞。印证了single-transaction不是锁定所有表的观点。
补充说明，该验证方法是以抽样的形式（随机测试了其中一张表），从“结论的角度（lock-tables 会锁一个schema的所有表，另外一个则不会）”验证，并不严谨，但有一定参考价值。