小白学习MySQL - mysqldump保证数据一致性的参数差异
小白学习MySQL - mysqldump保证数据一致性的参数差异
bisal
发布于 2021-09-06 15:57:21
发布于 2021-09-06 15:57:21
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，当然，如果导出的时候数据库无人使用，就随意选择了。因此，不同的场景需求，选择正确的选项。
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2021-07-19 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读