小白学习MySQL – mysqldump保证数据一致性的参数差异

MySQL数据库选择mysqldump做逻辑备份，以前可能就只写一条简单的指令，但实际上，为了保证数据的一致性，这里面是很有些讲究的，尤其是数据量大且同时存在并发的场景，

mysqldump -uroot -proot test > /home/mysql/20210603.sql
在导出的时候，mysqldump提供了这三个选项，值得我们关注，

-l, –lock-tables

Lock all tables for read. (Defaults to on; use –skip-lock-tables to disable.)

-x, –lock-all-tables

Locks all tables across all databases. This is achieved by taking a global read lock for the duration of the whole dump. Automatically turns –single-transaction and –lock-tables off.

–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.

为了了解这三个参数都做了什么，我们打开general_log，我们看下区别。首先，我们尝试默认参数为空，看下都做了什么，

mysqldump -udumper -pdumper test
我们看到，他会将数据库所有的表做LOCK TABLE，所有表导出完成，才会执行UNLOCK TABLES，因此在导出过程中所有的表都禁止操作，

2021-06-03T11:09:36.762913+08:00 1901 Connect root@localhost on using Socket
2021-06-03T11:09:36.763043+08:00 1901 Query /*!40100 SET @@SQL_MODE=” */
2021-06-03T11:09:36.763134+08:00 1901 Query /*!40103 SET TIME_ZONE=’+00:00′ */
2021-06-03T11:09:36.763232+08:00 1901 Query SHOW VARIABLES LIKE ‘gtid_mode’
2021-06-03T11:09:36.765575+08:00 1901 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘UNDO LOG’ AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘DATAFILE’ AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (‘test’))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2021-06-03T11:09:36.768961+08:00 1901 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘DATAFILE’ AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (‘test’)) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2021-06-03T11:09:36.770877+08:00 1901 Query SHOW VARIABLES LIKE ‘ndbinfo_version’
2021-06-03T11:09:36.772303+08:00 1901 Init DB test
2021-06-03T11:09:36.772490+08:00 1901 Query show tables
2021-06-03T11:09:36.773160+08:00 1901 Query LOCK TABLES test READ /*!32311 LOCAL */,test_0 READ /*!32311 LOCAL */,test_1 READ /*!32311 LOCAL */, …Q
2021-06-03T11:09:36.774203+08:00 1901 Query show table status like ‘t_test’
2021-06-03T11:09:36.774628+08:00 1901 Query SET SQL_QUOTE_SHOW_CREATE=1
2021-06-03T11:09:36.774751+08:00 1901 Query SET SESSION character_set_results = ‘binary’
2021-06-03T11:09:36.774858+08:00 1901 Query show create table t_test
2021-06-03T11:09:36.774994+08:00 1901 Query SET SESSION character_set_results = ‘utf8’
2021-06-03T11:09:36.775050+08:00 1901 Query show fields from t_test
2021-06-03T11:09:36.775390+08:00 1901 Query show fields from t_test
2021-06-03T11:09:36.775670+08:00 1901 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM t_test
2021-06-03T11:09:36.775830+08:00 1901 Query SET SESSION character_set_results = ‘binary’
2021-06-03T11:09:36.775889+08:00 1901 Query use test
2021-06-03T11:09:36.775933+08:00 1901 Query select @@collation_database
2021-06-03T11:09:36.776004+08:00 1901 Query SHOW TRIGGERS LIKE ‘t_test’
2021-06-03T11:09:36.776343+08:00 1901 Query SET SESSION character_set_results = ‘utf8’
…
2021-06-03T11:09:36.994734+08:00 1901 Query UNLOCK TABLES
2021-06-03T11:09:37.049721+08:00 1901 Quit
我们再来看下lock-all-tables，

mysqldump -udumper -pdumper –lock-all-tables test
他会在开始，执行一次FLUSH TABLES WITH READ LOCK，在导出完成，退出会话的时候，自动释放读锁，

2021-06-03T10:31:37.989701+08:00 1892 Query show variables like ‘log_output’
2021-06-03T10:32:08.981051+08:00 1892 Query show variables like ‘general_log’
2021-06-03T10:32:39.291966+08:00 1892 Quit
2021-06-03T10:33:06.146784+08:00 1893 Connect root@localhost on using Socket
2021-06-03T10:33:06.146963+08:00 1893 Query /*!40100 SET @@SQL_MODE=” */
2021-06-03T10:33:06.147069+08:00 1893 Query /*!40103 SET TIME_ZONE=’+00:00′ */
2021-06-03T10:33:06.147165+08:00 1893 Query FLUSH TABLES
2021-06-03T10:33:06.182528+08:00 1893 Query FLUSH TABLES WITH READ LOCK
2021-06-03T10:33:06.182675+08:00 1893 Query SHOW VARIABLES LIKE ‘gtid_mode’
2021-06-03T10:33:06.184634+08:00 1893 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘UNDO LOG’ AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘DATAFILE’ AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (‘test’))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2021-06-03T10:33:06.192668+08:00 1893 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘DATAFILE’ AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (‘test’)) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2021-06-03T10:33:06.194757+08:00 1893 Query SHOW VARIABLES LIKE ‘ndbinfo_version’
2021-06-03T10:33:06.196137+08:00 1893 Init DB test
2021-06-03T10:33:06.196195+08:00 1893 Query show tables
2021-06-03T10:33:06.196398+08:00 1893 Query show table status like ‘t_test’
2021-06-03T10:33:06.196592+08:00 1893 Query SET SQL_QUOTE_SHOW_CREATE=1
2021-06-03T10:33:06.196634+08:00 1893 Query SET SESSION character_set_results = ‘binary’
2021-06-03T10:33:06.196667+08:00 1893 Query show create table t\_test
2021-06-03T10:33:06.196748+08:00 1893 Query SET SESSION character_set_results = ‘utf8’
2021-06-03T10:33:06.196808+08:00 1893 Query show fields from t\_test
2021-06-03T10:33:06.197078+08:00 1893 Query show fields from t\_test
2021-06-03T10:33:06.197273+08:00 1893 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM test
2021-06-03T10:33:06.197395+08:00 1893 Query SET SESSION character_set_results = ‘binary’
2021-06-03T10:33:06.197429+08:00 1893 Query use test
2021-06-03T10:33:06.197462+08:00 1893 Query select @@collation_database
2021-06-03T10:33:06.197507+08:00 1893 Query SHOW TRIGGERS LIKE ‘t_’
2021-06-03T10:33:06.197791+08:00 1893 Query SET SESSION character_set_results = ‘utf8’
…
2021-06-03T10:33:06.458820+08:00 1893 Quit
再来看下single-transaction，

mysqldump -udumper -pdumper –single-transaction test
我们看到，在开始执行了SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ，设置了RR隔离级别，并通过START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */，开启了事务，UNLOCK TABLES解锁了所有的表，我们能注意到，他在执行导出操作前，设置了还原点，SAVEPOINT sp，并且在每张表的导出操作前，都执行了ROLLBACK TO SAVEPOINT sp，回到还原点，通过这种操作，保证了读数据的一致性，导出所有的表，然后再执行了RELEASE SAVEPOINT sp，

2021-06-03T11:07:08.423617+08:00 1899 Connect root@localhost on using Socket
2021-06-03T11:07:08.423747+08:00 1899 Query /*!40100 SET @@SQL_MODE=” */
2021-06-03T11:07:08.423816+08:00 1899 Query /*!40103 SET TIME_ZONE=’+00:00′ */
2021-06-03T11:07:08.423892+08:00 1899 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2021-06-03T11:07:08.423940+08:00 1899 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2021-06-03T11:07:08.424145+08:00 1899 Query SHOW VARIABLES LIKE ‘gtid_mode’
2021-06-03T11:07:08.426431+08:00 1899 Query UNLOCK TABLES
2021-06-03T11:07:08.426624+08:00 1899 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘UNDO LOG’ AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘DATAFILE’ AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (‘test’))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2021-06-03T11:07:08.431603+08:00 1899 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘DATAFILE’ AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (‘test’)) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2021-06-03T11:07:08.433579+08:00 1899 Query SHOW VARIABLES LIKE ‘ndbinfo_version’
2021-06-03T11:07:08.434791+08:00 1899 Init DB test
2021-06-03T11:07:08.434852+08:00 1899 Query SAVEPOINT sp
2021-06-03T11:07:08.434906+08:00 1899 Query show tables
2021-06-03T11:07:08.435113+08:00 1899 Query show table status like ‘t_test’
2021-06-03T11:07:08.435337+08:00 1899 Query SET SQL_QUOTE_SHOW_CREATE=1
2021-06-03T11:07:08.435388+08:00 1899 Query SET SESSION character_set_results = ‘binary’
2021-06-03T11:07:08.435434+08:00 1899 Query show create table t\_test
2021-06-03T11:07:08.435509+08:00 1899 Query SET SESSION character_set_results = ‘utf8’
2021-06-03T11:07:08.435567+08:00 1899 Query show fields from t\_test
2021-06-03T11:07:08.435859+08:00 1899 Query show fields from t\_test
2021-06-03T11:07:08.436082+08:00 1899 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM test
2021-06-03T11:07:08.436228+08:00 1899 Query SET SESSION character_set_results = ‘binary’
2021-06-03T11:07:08.436295+08:00 1899 Query use test
2021-06-03T11:07:08.436346+08:00 1899 Query select @@collation_database
2021-06-03T11:07:08.436406+08:00 1899 Query SHOW TRIGGERS LIKE ‘t_test’
2021-06-03T11:07:08.436702+08:00 1899 Query SET SESSION character_set_results = ‘utf8’
2021-06-03T11:07:08.436765+08:00 1899 Query ROLLBACK TO SAVEPOINT sp
…
2021-06-03T11:07:08.624624+08:00 1899 Query ROLLBACK TO SAVEPOINT sp
2021-06-03T11:07:08.624650+08:00 1899 Query RELEASE SAVEPOINT sp
2021-06-03T11:07:08.662944+08:00 1899 Quit
从导出过程对数据库正常使用的影响程度上，–single-transaction<–lock-all-tables<–lock-tables，依次递减，如果从数据一致性的角度，推荐使用–single-transaction，当然，如果导出的时候数据库无人使用，就随意选择了。因此，不同的场景需求，选择正确的选项。

小白学习MySQL，

《小白学习MySQL – 查询会锁表？》

《小白学习MySQL – 索引键长度限制的问题》

《小白学习MySQL – MySQL会不会受到“高水位”的影响？》

《小白学习MySQL – 数据库软件和初始化安装》

《小白学习MySQL – 闲聊聊》

近期更新的文章：

《最近碰到的几个问题》

《新增字段在数据块中的体现》

《SQL Server的索引碎片》

《MySQL客户端预读数据的区别》

《MySQL分组需求探秘》

文章分类和索引：

《公众号700篇文章分类和索引》