结论：
1.不加 --single-transaction 参数时，锁定所有表：LOCK TABLES `t1` READ,`t2` READ,`t3` READ,`t4` READ ......加 --single-transaction 参数,没有锁。2.加 --lock-all-tables 或 --lock-tables 参数，会加锁。3.加 --master-data=2 或 --flush-logs 参数时，会执行：FLUSH TABLES;FLUSH TABLES WITH READ LOCK;不加锁，但是会导致备份时间延长，或无法备份完成。详细信息见我的另一篇博客：http://blog.itpub.net/29785807/viewspace-2951942/
FLUSH TABLES:
关闭所有打开的表，强制关闭所有正在使用的表，并刷新查询缓存和预准备语句缓存，不会刷新脏块,会被锁阻塞，会等待所有正在运行的SQL执行结束。
例如，当前有正在运行的慢SQL：
select sleep(100) from t1;
此时执行 FLUSH TABLES; 就会被阻塞，需要等待SQL执行结束。
+----+-----------------+-----------+------+---------+------+-------------------------+---------------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-----------------+-----------+------+---------+------+-------------------------+---------------------------+| 30 | bak | localhost | cjc | Query | 7 | Waiting for table flush | FLUSH TABLES |+----+-----------------+-----------+------+---------+------+-------------------------+---------------------------+
FLUSH TABLES WITH READ LOCK:
关闭所有打开的表并使用全局读锁锁定所有数据库的所有表，不会刷新脏块,会被锁阻塞。
如果存在锁，FLUSH会直接返回错误。
mysql> FLUSH TABLES WITH READ LOCK;ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transactionmysql> FLUSH TABLES;ERROR 1099 (HY000): Table 't4' was locked with a READ lock and can't be updated
测试过程如下：
环境说明：
数据库版本：MySQL 8.0.31数据库隔离级别：REPEATABLE-READ
mysql> select @@tx_isolation;+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set, 1 warning (0.00 sec)
新增测试数据
create database cjc;use cjc;create table t1(id int);create table t2(name varchar(20));create table t3(id int) engine=myisam;create table t4(id int) engine=myisam;
两个InnoDB引擎表，两个MyISAM引擎表
select TABLE_NAME,ENGINE from information_schema.tables where TABLE_SCHEMA='cjc';+------------+--------+| TABLE_NAME | ENGINE |+------------+--------+| t1 | InnoDB || t2 | InnoDB || t3 | MyISAM || t4 | MyISAM |+------------+--------+
插入测试数据
insert into t1 values(1),(2),(3);insert into t3 values(1),(2),(3);insert into t4 values(1),(2),(3);insert into t2 values('a'),('b'),('c');
创建备份用户
create user bak@'localhost' identified by '1';grant all privileges on *.* to bak@'localhost';flush privileges;
打开日志
mysql> show variables like 'general_log';+---------------+-------+| Variable_name | Value |+---------------+-------+| general_log | OFF |+---------------+-------+1 row in set (0.00 sec)
mysql> set global general_log=ON;
打开锁监控
select * from performance_schema.setup_instruments where name like '%lock%';UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';或者update performance_schema.setup_instruments set enabled = 'YES' where name like '%lock%';或添加到配置文件[mysqld]performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
全库备份
mysqldump -ubak -p --all-databases > /mysqldata/back/1.sql
备份日志，对所有表加锁 LOCK TABLES `t1` READ ...，部分日志如下：
......Init DBcjcQuerySHOW CREATE DATABASE IF NOT EXISTS `cjc`Queryshow tablesQueryLOCK TABLES `t1` READ /*!32311 LOCAL */,`t2` READ /*!32311 LOCAL */,`t3` READ /*!32311 LOCAL */,`t4` READ /*!32311 LOCAL */Queryshow table status like 't1'QuerySET SQL_QUOTE_SHOW_CREATE=1QuerySET SESSION character_set_results = 'binary'Queryshow create table `t1`QuerySET SESSION character_set_results = 'utf8mb4'Queryshow fields from `t1`Queryshow fields from `t1`QuerySELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`QuerySET SESSION character_set_results = 'binary'Queryuse `cjc`Queryselect @@collation_databaseQuerySHOW TRIGGERS LIKE 't1'QuerySET SESSION character_set_results = 'utf8mb4'QuerySET SESSION character_set_results = 'binary'QuerySELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'cjc' AND TABLE_NAME = 't1'QuerySET SESSION character_set_results = 'utf8mb4'......QueryUNLOCK TABLES
可以看到
LOCK TABLES `t1` READ /*!32311 LOCAL */,`t2` READ /*!32311 LOCAL */,`t3` READ /*!32311 LOCAL */,`t4` READ /*!32311 LOCAL */
测试 --single-transaction 参数
mysqldump -ubak -p --all-databases --single-transaction > /mysqldata/back/2.sql
......
QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READQuerySTART TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */QuerySHOW VARIABLES LIKE 'gtid\_mode'QuerySELECT @@GLOBAL.GTID_EXECUTEDQueryUNLOCK TABLES......Init DBcjcQuerySHOW CREATE DATABASE IF NOT EXISTS `cjc`QuerySAVEPOINT spQueryshow tablesQueryshow table status like 't1'QuerySET SQL_QUOTE_SHOW_CREATE=1QuerySET SESSION character_set_results = 'binary'Queryshow create table `t1`QuerySET SESSION character_set_results = 'utf8mb4'Queryshow fields from `t1`Queryshow fields from `t1`QuerySELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`QuerySET SESSION character_set_results = 'binary'Queryuse `cjc`Queryselect @@collation_databaseQuerySHOW TRIGGERS LIKE 't1'QuerySET SESSION character_set_results = 'utf8mb4'QuerySET SESSION character_set_results = 'binary'QuerySELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'cjc' AND TABLE_NAME = 't1'QuerySET SESSION character_set_results = 'utf8mb4'QueryROLLBACK TO SAVEPOINT spQueryshow table status like 't2'QuerySET SQL_QUOTE_SHOW_CREATE=1QuerySET SESSION character_set_results = 'binary'Queryshow create table `t2`QuerySET SESSION character_set_results = 'utf8mb4'Queryshow fields from `t2`Queryshow fields from `t2`QuerySELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`QuerySET SESSION character_set_results = 'binary'Queryuse `cjc`Queryselect @@collation_databaseQuerySHOW TRIGGERS LIKE 't2'QuerySET SESSION character_set_results = 'utf8mb4'QuerySET SESSION character_set_results = 'binary'QuerySELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'cjc' AND TABLE_NAME = 't2'QuerySET SESSION character_set_results = 'utf8mb4'QueryROLLBACK TO SAVEPOINT spQueryshow table status like 't3'QuerySET SQL_QUOTE_SHOW_CREATE=1QuerySET SESSION character_set_results = 'binary'Queryshow create table `t3`QuerySET SESSION character_set_results = 'utf8mb4'Queryshow fields from `t3`Queryshow fields from `t3`QuerySELECT /*!40001 SQL_NO_CACHE */ * FROM `t3`QuerySET SESSION character_set_results = 'binary'Queryuse `cjc`Queryselect @@collation_databaseQuerySHOW TRIGGERS LIKE 't3'QuerySET SESSION character_set_results = 'utf8mb4'QuerySET SESSION character_set_results = 'binary'QuerySELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'cjc' AND TABLE_NAME = 't3'QuerySET SESSION character_set_results = 'utf8mb4'QueryROLLBACK TO SAVEPOINT spQueryshow table status like 't4'QuerySET SQL_QUOTE_SHOW_CREATE=1QuerySET SESSION character_set_results = 'binary'Queryshow create table `t4`QuerySET SESSION character_set_results = 'utf8mb4'Queryshow fields from `t4`Queryshow fields from `t4`QuerySELECT /*!40001 SQL_NO_CACHE */ * FROM `t4`QuerySET SESSION character_set_results = 'binary'Queryuse `cjc`Queryselect @@collation_databaseQuerySHOW TRIGGERS LIKE 't4'QuerySET SESSION character_set_results = 'utf8mb4'QuerySET SESSION character_set_results = 'binary'QuerySELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'cjc' AND TABLE_NAME = 't4'QuerySET SESSION character_set_results = 'utf8mb4'QueryROLLBACK TO SAVEPOINT spQueryRELEASE SAVEPOINT sp
测试 --master-data=2 参数
......
mysqldump -ubak -p --all-databases --single-transaction --master-data=2 > mysqldata/back/3.sql2023-05-28T22:56:31.102647+08:00 26 Connectbak@localhost on using Socket2023-05-28T22:56:31.103025+08:00 26 Query/*!40100 SET @@SQL_MODE='' */2023-05-28T22:56:31.103257+08:00 26 Query/*!40103 SET TIME_ZONE='+00:00' */2023-05-28T22:56:31.103368+08:00 26 Query/*!80000 SET SESSION information_schema_stats_expiry=0 */2023-05-28T22:56:31.103456+08:00 26 QuerySET SESSION NET_READ_TIMEOUT= 86400, SESSION NET_WRITE_TIMEOUT= 864002023-05-28T22:56:31.103572+08:00 26 QueryFLUSH /*!40101 LOCAL */ TABLES2023-05-28T22:56:31.398182+08:00 26 QueryFLUSH TABLES WITH READ LOCK2023-05-28T22:56:31.398341+08:00 26 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2023-05-28T22:56:31.398481+08:00 26 QuerySTART TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */2023-05-28T22:56:31.398730+08:00 26 QuerySHOW VARIABLES LIKE 'gtid\_mode'2023-05-28T22:56:31.403145+08:00 26 QuerySELECT @@GLOBAL.GTID_EXECUTED2023-05-28T22:56:31.403360+08:00 26 QuerySHOW MASTER STATUS2023-05-28T22:56:31.403901+08:00 26 QueryUNLOCK TABLES
......
测试 --flush-logs 参数
mysqldump -ubak -p --all-databases --single-transaction --flush-logs > /mysqldata/back/4.sql
......
Connectbak@localhost on using SocketQuery/*!40100 SET @@SQL_MODE='' */Query/*!40103 SET TIME_ZONE='+00:00' */Query/*!80000 SET SESSION information_schema_stats_expiry=0 */QuerySET SESSION NET_READ_TIMEOUT= 86400, SESSION NET_WRITE_TIMEOUT= 86400QueryFLUSH TABLESQueryFLUSH TABLES WITH READ LOCKQuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READQuerySTART TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */QuerySHOW VARIABLES LIKE 'gtid\_mode'QuerySELECT @@GLOBAL.GTID_EXECUTEDQueryUNLOCK TABLES
......
模拟mysqldump锁
mysqldump -ubak -p --all-databases > /mysqldata/back/5.sql
会话30执行锁表，模拟mysqldump备份不加--single-transaction参数的场景。
---Connection id:30LOCK TABLES `t1` READ,`t2` READ,`t3` READ,`t4` READ;
会话31，执行truncate操作被会话30阻塞
---Connection id:31use cjc;truncate table t1;
被阻塞
查看进程
mysql> show processlist;+----+-----------------+-----------+------+---------+------+---------------------------------+-------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-----------------+-----------+------+---------+------+---------------------------------+-------------------+| 5 | event_scheduler | localhost | NULL | Daemon | 5463 | Waiting on empty queue | NULL || 30 | bak | localhost | cjc | Sleep | 14 | | NULL || 31 | root | localhost | cjc | Query | 50 | Waiting for table metadata lock | truncate table t1 || 33 | root | localhost | NULL | Query | 0 | init | show processlist |+----+-----------------+-----------+------+---------+------+---------------------------------+-------------------+4 rows in set (0.01 sec)
会话34，执行select操作被会话31阻塞
Connection id:34use cjcselect * from t1;
被阻塞
查看进程
mysql> show processlist;+----+-----------------+-----------+------+---------+------+---------------------------------+-------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-----------------+-----------+------+---------+------+---------------------------------+-------------------+| 5 | event_scheduler | localhost | NULL | Daemon | 5618 | Waiting on empty queue | NULL || 30 | bak | localhost | cjc | Sleep | 17 | | NULL || 31 | root | localhost | cjc | Query | 205 | Waiting for table metadata lock | truncate table t1 || 33 | root | localhost | NULL | Query | 0 | init | show processlist || 34 | root | localhost | cjc | Query | 59 | Waiting for table metadata lock | select * from t1 |+----+-----------------+-----------+------+---------+------+---------------------------------+-------------------+5 rows in set (0.00 sec)
查看锁信息
select * from performance_schema.metadata_locks where OWNER_THREAD_ID !=sys.ps_thread_id(connection_id())\G;
*************************** 1. row ***************************OBJECT_TYPE: TABLEOBJECT_SCHEMA: cjcOBJECT_NAME: t1COLUMN_NAME: NULLOBJECT_INSTANCE_BEGIN: 140108480348128LOCK_TYPE: SHARED_READ_ONLYLOCK_DURATION: TRANSACTIONLOCK_STATUS: GRANTEDSOURCE: sql_parse.cc:6084OWNER_THREAD_ID: 71OWNER_EVENT_ID: 10*************************** 2. row ***************************OBJECT_TYPE: TABLEOBJECT_SCHEMA: cjcOBJECT_NAME: t2COLUMN_NAME: NULLOBJECT_INSTANCE_BEGIN: 140108481355856LOCK_TYPE: SHARED_READ_ONLYLOCK_DURATION: TRANSACTIONLOCK_STATUS: GRANTEDSOURCE: sql_parse.cc:6084OWNER_THREAD_ID: 71OWNER_EVENT_ID: 10*************************** 3. row ***************************OBJECT_TYPE: TABLEOBJECT_SCHEMA: cjcOBJECT_NAME: t3COLUMN_NAME: NULLOBJECT_INSTANCE_BEGIN: 140108485324816LOCK_TYPE: SHARED_READ_ONLYLOCK_DURATION: TRANSACTIONLOCK_STATUS: GRANTEDSOURCE: sql_parse.cc:6084OWNER_THREAD_ID: 71OWNER_EVENT_ID: 10*************************** 4. row ***************************OBJECT_TYPE: TABLEOBJECT_SCHEMA: cjcOBJECT_NAME: t4COLUMN_NAME: NULLOBJECT_INSTANCE_BEGIN: 140108481614272LOCK_TYPE: SHARED_READ_ONLYLOCK_DURATION: TRANSACTIONLOCK_STATUS: GRANTEDSOURCE: sql_parse.cc:6084OWNER_THREAD_ID: 71OWNER_EVENT_ID: 10*************************** 5. row ***************************OBJECT_TYPE: SCHEMAOBJECT_SCHEMA: cjcOBJECT_NAME: NULLCOLUMN_NAME: NULLOBJECT_INSTANCE_BEGIN: 140108678397360LOCK_TYPE: INTENTION_EXCLUSIVELOCK_DURATION: EXPLICITLOCK_STATUS: GRANTEDSOURCE: dd_schema.cc:107OWNER_THREAD_ID: 72OWNER_EVENT_ID: 11*************************** 6. row ***************************OBJECT_TYPE: GLOBALOBJECT_SCHEMA: NULLOBJECT_NAME: NULLCOLUMN_NAME: NULLOBJECT_INSTANCE_BEGIN: 140108678387216LOCK_TYPE: INTENTION_EXCLUSIVELOCK_DURATION: STATEMENTLOCK_STATUS: GRANTEDSOURCE: sql_base.cc:5475OWNER_THREAD_ID: 72OWNER_EVENT_ID: 11*************************** 7. row ***************************OBJECT_TYPE: BACKUP LOCKOBJECT_SCHEMA: NULLOBJECT_NAME: NULLCOLUMN_NAME: NULLOBJECT_INSTANCE_BEGIN: 140108678387312LOCK_TYPE: INTENTION_EXCLUSIVELOCK_DURATION: TRANSACTIONLOCK_STATUS: GRANTEDSOURCE: sql_base.cc:5482OWNER_THREAD_ID: 72OWNER_EVENT_ID: 11*************************** 8. row ***************************OBJECT_TYPE: SCHEMAOBJECT_SCHEMA: cjcOBJECT_NAME: NULLCOLUMN_NAME: NULLOBJECT_INSTANCE_BEGIN: 140108678387120LOCK_TYPE: INTENTION_EXCLUSIVELOCK_DURATION: TRANSACTIONLOCK_STATUS: GRANTEDSOURCE: sql_base.cc:5462OWNER_THREAD_ID: 72OWNER_EVENT_ID: 11*************************** 9. row ***************************OBJECT_TYPE: TABLEOBJECT_SCHEMA: cjcOBJECT_NAME: t1COLUMN_NAME: NULLOBJECT_INSTANCE_BEGIN: 140108678386864LOCK_TYPE: EXCLUSIVELOCK_DURATION: TRANSACTIONLOCK_STATUS: PENDINGSOURCE: sql_parse.cc:6084OWNER_THREAD_ID: 72OWNER_EVENT_ID: 11*************************** 10. row ***************************OBJECT_TYPE: TABLEOBJECT_SCHEMA: cjcOBJECT_NAME: t1COLUMN_NAME: NULLOBJECT_INSTANCE_BEGIN: 140108210681424LOCK_TYPE: SHARED_READLOCK_DURATION: TRANSACTIONLOCK_STATUS: PENDINGSOURCE: sql_parse.cc:6084OWNER_THREAD_ID: 75OWNER_EVENT_ID: 1410 rows in set, 1 warning (0.31 sec)ERROR:No query specified
查看 THREAD_ID,PROCESSLIST_ID信息
select THREAD_ID,PROCESSLIST_ID,name,type from performance_schema.threads where PROCESSLIST_ID is not NULL;
+-----------+----------------+--------------------------------+------------+| THREAD_ID | PROCESSLIST_ID | name | type |+-----------+----------------+--------------------------------+------------+| 46 | 5 | thread/sql/event_scheduler | FOREGROUND || 48 | 7 | thread/sql/compress_gtid_table | FOREGROUND || 71 | 30 | thread/sql/one_connection | FOREGROUND || 72 | 31 | thread/sql/one_connection | FOREGROUND || 74 | 33 | thread/sql/one_connection | FOREGROUND || 75 | 34 | thread/sql/one_connection | FOREGROUND |+-----------+----------------+--------------------------------+------------+6 rows in set (0.30 sec)
可以看到执行LOCK TABLES的会话PROCESSLIST_ID=30，THREAD_ID=71 锁定类型LOCK_TYPE: SHARED_READ_ONLY，锁定状态 LOCK_STATUS: GRANTED。
可以看到执行TRUNCATE TABLE的会话PROCESSLIST_ID=31，THREAD_ID=72 锁定类型LOCK_TYPE: LOCK_TYPE: INTENTION_EXCLUSIVE，锁定状态 LOCK_STATUS: PENDING。
可以看到执行SELECT的会话PROCESSLIST_ID=34，THREAD_ID=75 锁定类型LOCK_TYPE: LOCK_TYPE: SHARED_READ,锁定状态 LOCK_STATUS: PENDING。
当前被锁定的表
mysql> show OPEN TABLES where In_use > 0;+----------+-------+--------+-------------+| Database | Table | In_use | Name_locked |+----------+-------+--------+-------------+| cjc | t4 | 1 | 0 || cjc | t1 | 1 | 0 || cjc | t2 | 1 | 0 || cjc | t3 | 1 | 0 |+----------+-------+--------+-------------+4 rows in set (0.01 sec)
查看状态
mysql> show engine innodb status\G;
查询最后一次执行的SQL
select * from performance_schema.events_statements_current where thread_id=71\G;
阻塞关系：
会话34被会话31阻塞会话31被会话30阻塞可能存在锁不释放的问题：A会话执行mysqldump备份，没有加 --single-transaction 参数，导致备份前锁定所有表。B会话在A会话执行备份后，释放锁之前，执行了truncate操作，被会话A注释。A会话释放锁的前提是备份结束，也就是需要经历锁表、全表扫描查询所有表、...、释放锁等，如果备份过程中的查询操作被阻塞，也会导致备份无法完成，锁无法释放。可能有些第三方备份工具修改了mysqldump备份方式，为了提高备份速度，采用并行备份表，也就是多个会话并行执行不同表的导出操作。基于这种场景，可能出现会话C在导出备份时执行查询表操作被会话B阻塞。这时锁关系就出现了死循环，多个会话相互锁死无法执行完成。
mysqldump备份有哪些常用参数：
参数说明：
(1)--all-databases:Dump all the databases. This will be same as --databases with all databases selected.转储所有数据库。这将与选中所有数据库的--databases相同。
(2)--hex-blob:Dump binary strings (BINARY, VARBINARY, BLOB) in hexadecimal format.以十六进制格式转储二进制字符串(BINARY, VARBINARY, BLOB)。避免导出的二进制字符串乱码。
(3)--master-data=2:This causes the binary log position and filename to be appended to the output.If equal to 1, will print it as a CHANGE MASTER command;if equal to 2, that command will be prefixed with a comment symbol.This option will turn --lock-all-tables on, unless --single-transaction is specified too (in which case a global read lock is only taken a short time at the beginning of the dump; don't forget to read about --single-transaction below).In all cases, any action on logs will happen at the exact moment of the dump.Option automatically turns --lock-tables off.这将导致二进制日志位置和文件名被附加到输出中。如果等于1，则将其打印为CHANGE MASTER命令；如果等于2，则该命令将以注释符号为前缀。此选项将打开--lock-all-tables ，除非也指定了 --single-transaction（在这种情况下，全局读取锁定只在转储开始时占用很短的时间；不要忘记阅读下面的--single transaction）。在任何情况下，对日志的任何操作都将在转储的确切时刻发生。选项会自动关闭--lock-tables。
(4)--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 be consistent 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.通过在单个事务中转储所有表来创建一致的快照。仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB支持）；不能保证转储对于其他存储引擎是一致的。当--single-transaction转储正在进行时，为了确保有效的转储文件（正确的表内容和二进制日志位置），任何其他连接都不应使用以下语句：ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE，因为一致性快照并没有与它们隔离。选项会自动关闭--lock-tables。解释：mysqldump为了保证导出数据的一致性，通常需要加全局锁：flush tables with read lock;备份结束后，释放锁：unlock tables ;全局锁虽然能解决数据备份不一致问题，但是在数据库中加全局锁，是一个比较重的操作，会影响备份期间正常业务更新等操作。在InnoDB引擎中，可以在备份时加上参数 --single-transaction参数来完成不加锁的一致性数据备份。这个指令会在执行时对当前数据库生成一份快照，并对快照进行备份。什么是数据一致性？例如，mysqldump在08:00开始备份，08:30备份完成。理论上导致的所有数据应该都是8:00这一时刻的数据，如果不加锁，可能会出现A表是8:05的数据，B表是8:10的数据库，这种情况下，备份出的数据可能无法正常使用。
(5)--routines:Dump stored routines (functions and procedures).转储存储过程（函数和过程）。
(6)--triggers:Dump triggers for each dumped table.(Defaults to on; use --skip-triggers to disable.)转储触发器，默认关闭。
(7)--events:转储events;
(8)--set-gtid-purged=OFF:Add 'SET @@GLOBAL.GTID_PURGED' to the output. Possible values for this option are ON, COMMENTED, OFF and AUTO.If ON is used and GTIDs are not enabled on the server, an error is generated.If COMMENTED is used, 'SET @@GLOBAL.GTID_PURGED' is added as a comment.If OFF is used, this option does nothing.If AUTO is used and GTIDs are enabled on the server, 'SET @@GLOBAL.GTID_PURGED' is added to the output.If GTIDs are disabled, AUTO does nothing.If no value is supplied then the default (AUTO) value will be considered.在输出中添加'SET @@GLOBAL.GTID_PURGED'。此选项的可能值为ON、COMMENTED、OFF和AUTO。如果在服务器上使用ON并且未启用GTID，则会生成一个错误。如果使用了COMMENTED，则会添加'SET @@GLOBAL.GTID_PURGED'作为注释。如果使用OFF（关闭），此选项将不起任何作用。如果使用AUTO并且在服务器上启用了GTID，则会将'SET @@GLOBAL.GTID_PURGED'添加到输出中。如果GTID被禁用，AUTO将不执行任何操作。如果未提供任何值，则将考虑默认值（AUTO）。
(9)--flush-logs:Flush logs file in server before starting dump.Note that if you dump many databases at once (using the option --databases= or --all-databases), the logs will be flushed for each database dumped.The exception is when using --lock-all-tables or --master-data: in this case the logs will be flushed only once, corresponding to the moment all tables are locked.So if you want your dump and the log flush to happen at the same exact moment you should use --lock-all-tables or --master-data with --flush-logs.在开始转储之前刷新服务器中的日志文件。请注意，如果一次转储多个数据库（使用选项--databases=或--all databases），则会为转储的每个数据库刷新日志。使用--lock-all-tables或--master-data: 时会出现异常：在这种情况下，日志将只刷新一次，对应于所有表被锁定的时刻。因此，如果您希望转储和日志刷新同时发生，则应该使用--lock all table或--master data 和 --flush logs一起使用。
(10)--flush-privileges: 根据实际情况添加Emit a FLUSH PRIVILEGES statement after dumping the mysql database.This option should be used any time the dump contains the mysql database and any other database that depends on the data in the mysql database for proper restore.在转储mysql数据库后，发出一个FLUSH PRIVILEGES语句。每当转储包含mysql数据库和任何其他依赖于mysql数据库中数据进行正确恢复的数据库时，都应该使用此选项。
(11)--extended-insert=TRUE:Use multiple-row INSERT syntax that include several VALUES lists.(Defaults to on; use --skip-extended-insert to disable.)使用包含多个值得多行语法生成INSERT语句。这会缩小dump文件得体积，并且在加快dump文件的insert操作。默认开启，使用--skip-extended-insert关闭。
(12)--net-buffer-length=#The buffer size for TCP/IP and socket communication.TCP/IP和套接字通信的缓冲区大小。需要注意的是net_buffer_length的导出值不能比目标数据库的值大；mysql> show variables like 'net_buffer_length';讲net-buffer-length之前,先讲另外一个mysqldump的参数--extended-insert这个参数的意思就是是否开启合并insert(默认是开启的,不想开启直接加skip-extended-insert).用白话讲就是用mysqldump导出生成的insert数据合并成一条。现实情况是开启了extended-insert参数,如果数据超过1M,也会生成多个insert这就引入net-buffer-length这个参数了mysqldump(5.7.5以后,官方建议使用mysqlpump)的net-buffer-length 官方的解释就是通信时缓存数据的大小.最小4k,最大16M,默认是1M.msyqldump导出的数据就包括两部分,一部分是DDL(包含建表,建存储,建视图等sql语句),另一部分就是insert了,所有的数据都是生成insert了,所以insert这部分才是mysqldump的最大部分.结合上面说到的情况.启用extended-insert,理论上应该一个表只生成一个insert,但如果一个insert的数据超过1M(默认值),就会生成第二个insert,如果在超过1M,就生成第三个insert,以此类推,直到数据全部导完在导入的时候还涉及另外一个参数max_allowed_packet,如果这个值设置过低,会导致数据无法导入的.如下:mysql> set global max_allowed_packet=1048576;max_allowed_packet设置成了1M,t4.sql是之前的5M的sql,就会导入失败.在看看错误日志:2017-01-19T13:48:09.975902+08:00 5 [Note] Aborted connection 5 to db: 'tt2' user: 'root' host: 'localhost' (Got a packet bigger than 'max_allowed_packet' bytes)--net-buffer-length 设置越大，客户端与数据库交互次数越少，导入越快。
(13)--max-allowed-packet=#The maximum packet length to send to or receive from server.发送到服务器或从服务器接收的最大数据包长度。mysql> show variables like 'max_allowed_packet';需要注意的是max_allowed_packet的导出值不能比目标数据库的值大；
###chenjuchao 20230528###
文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
