使用 mysqldump 备份数据库时避免锁表 --single-transaction

╰+哭是因爲堅強的太久メ 2022-03-16 04:30 1039阅读 0赞

**使用 mysqldump 备份数据库时避免锁表**

**1.导出表时默认会枷锁**

备份数据库时  用 命令 mysqldump -u user -p xxx>xxx.sql发现没有完全备份数据库记录，加上  --skip-opt选项后好了

对一个正在运行的数据库进行备份请慎重！！ 如果一定要 在服务运行期间备份，请添加 --skip-opt选项，

类似执行： mysqldump --skip-opt -u root --password=123456 dbname >mysql.SQL

**2.开启一致性事务**

\--opt 会lock 本次需要备份的所有表，因为本次备份的是 dbname数据库，所以会锁住dbname的所有表。  
\--master-data=2，--master-data=1 默认的话会--lock-all-tables，会锁住整个mysql数据库中的所有表。  
但是如果加上--single-transaction会加上事务，不会锁表

在用mysqldump备份使用那些参数选项是最完美的组合呢？  
\--skip-opt  
\--create-options            ----添加create相关的选项  
\--single-transaction     ----一致性备份  
\-q                                ----采用快速的dump方式(提高导出性能)  
\-e                         ----采用多重insert语句形式(提高还原性能)，当一个表的数据量很大情况下不知道会不会导致死锁？  
\--no-autocommit         ----采用批量提交方式(提高还原性能)  
\-R                               ----导出存储过程，函数，和触发器  
\--master-data=2            ----如果有写log-bin且版本为5.0以上的版本，则再加上 --master-data=2  
\--events                      ----如果是5.1以上的版本使用，包含事件

\*\*\*\*\*\*\*\*\*\*\*\*\*

在mysqldump过程中，之前其实一直不是很理解为什么加了--single-transaction就能保证innodb的数据是完全一致的，而myisam引擎无法保证，必须加--lock-all-tables，前段时间抽空详细地查看了整个mysqldump过程。  
理解master-data和--dump-slave

\--master-data=2表示在dump过程中记录主库的binlog和pos点，并在dump文件中注释掉这一行；

\--master-data=1表示在dump过程中记录主库的binlog和pos点，并在dump文件中不注释掉这一行，即恢复时会执行；

\--dump-slave=2表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中注释掉这一行；

\--dump-slave=1表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中不注释掉这一行；

注意:在从库上执行备份时，即--dump-slave=2，这时整个dump过程都是stop io\_thread的状态

深入理解--single-transaction：

打开general\_log，准备一个数据量较小的db，开启备份，添加--single-transaction和--master-data=2参数，查看general\_log，信息如下，每一步添加了我的理解

整个dump过程是同一个连接id 32，这样能保证在设置session级别的变量的时候不影响到其他连接

thread\_id: 32  
 argument: ucloudbackup@localhost on  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 14. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: /\*!40100 SET @@SQL\_MODE='' \*/  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 15. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: /\*!40103 SET TIME\_ZONE='+00:00' \*/  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 16. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: FLUSH /\*!40101 LOCAL \*/ TABLES  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 17. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: FLUSH TABLES WITH READ LOCK  
批注：因为开启了--master-data=2，这时就需要flush tables with read lock锁住全库，记录当时的master\_log\_file和master\_log\_pos点  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 18. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ  
批注：--single-transaction参数的作用，设置事务的隔离级别为可重复读，即REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，对该dump线程的数据并无影响，然而这个还不够，还需要看下一条  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 19. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: START TRANSACTION /\*!40100 WITH CONSISTENT SNAPSHOT \*/  
这时开启一个事务，并且设置WITH CONSISTENT SNAPSHOT为快照级别（如果mysql版本高于某一个版本值，我还不大清楚40100代表什么版本）。想象一下，如果只是可重复读，那么在事务开始时还没dump数据时，这时其他线程修改并提交了数据，那么这时第一次查询得到的结果是其他线程提交后的结果，而WITH CONSISTENT SNAPSHOT能够保证在事务开启的时候，第一次查询的结果就是事务开始时的数据A，即使这时其他线程将其数据修改为B，查的结果依然是A，具体的测试看我下面的测试结果  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 20. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: SHOW MASTER STATUS  
这时候执行这个命令来记录当时的master\_log\_file和master\_log\_pos点，注意为什么这个时候记录，而不是再18 row和19 row之间就记录，个人认为应该都是可以的，这里是测试结果，start  transaction并不会产生binlog的移动，而18 row和19 row的动作也在同一个thread id中  
mysql> show master status;  
\+------------------+----------+--------------+------------------+  
| File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB |  
\+------------------+----------+--------------+------------------+  
| mysql-bin.000003 |     1690 |              |                  |  
\+------------------+----------+--------------+------------------+  
1 row in set (0.00 sec)

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 21. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: UNLOCK TABLES  
等记录完成后，就立即释放了，因为现在已经在一个事务中了，其他线程再修改数据已经无所谓，在本线程中已经是可重复读，这也是这一步必须在19 rows之后的原因，如果20 rows和21 rows都在19 rows之前的话就不行了，因为这时事务还没开启，一旦释放，其他线程立即就可以更改数据，从而无法保证得到事务开启时最准确的pos点。\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 22. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: 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 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='mysql' AND TABLE\_NAME IN ('user'))) GROUP BY LOGFILE\_GROUP\_NAME, FILE\_NAME, ENGINE ORDER BY LOGFILE\_GROUP\_NAME  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 23. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: 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='mysql' AND TABLE\_NAME IN ('user')) ORDER BY TABLESPACE\_NAME, LOGFILE\_GROUP\_NAME  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 24. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: mysql  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 25. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: SHOW TABLES LIKE 'user'  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 26. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: show table status like 'user'  
dump表以前都需要show一下各自信息，确保表，视图等不损坏，可用，每一步错了mysqldump都会报错并中断，给出对应的错误码，常见的myqldump错误请参考我的另外一篇blog http://blog.csdn.net/cug\_jiang126com/article/details/49359699  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 27. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: SET OPTION SQL\_QUOTE\_SHOW\_CREATE=1  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 28. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: SET SESSION character\_set\_results = 'binary'  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 29. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: show create table \`user\`  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 30. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: SET SESSION character\_set\_results = 'utf8'  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 31. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: show fields from \`user\`  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 32. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 32  
 argument: SELECT /\*!40001 SQL\_NO\_CACHE \*/ \* FROM \`user\`  
这就是我们show processlist时看到的信息，而数据是怎么通过一条select语句就dump到本地文件里的呢，并且还转成成相应的create和insert语句，这就是mysqldump这个客户端工具的工作了，这里不做讨论  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 33. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
最后并没有看到commit，因为在整个事务中，其实并没有修改任何数据，只是为了保证可重复读得到备份时间点一致性的快照，dump完成后提交不提交应该无所谓了。

myisam引擎为什么无法保证在--single-transaction下得到一致性的备份？

因为它压根就不支持事务，自然就无法实现上述的过程，虽然添加了--single-transaction参数的myisam表处理过程和上面的完全一致，但是因为不支持事务，在整个dump过程中无法保证可重复读，无法得到一致性的备份。而innodb在备份过程中，虽然其他线程也在写数据，但是dump出来的数据能保证是备份开始时那个binlog pos的数据。  
myisam引擎要保证得到一致性的数据的话，他是如何实现的呢？

它是通过添加--lock-all-tables，这样在flush tables with read lock后，直到整个dump过程结束，断开线程后才会unlock tables释放锁(没必要主动发unlock tables指令)，整个dump过程其他线程不可写，从而保证数据的一致性  
如果我一定要在mysiam引擎中也添加--single-transaction参数，再用这个备份去创建从库或恢复到指定时间点，会有什么样的影响？

我个人的理解是如果整个dump过程中只有简单的insert操作，是没有关系的，期间肯定会有很多的主键重复错误，直接跳过或忽略就好了。如果是update操作，那就要出问题了，分几种情况考虑  
1） 如果是基于时间点的恢复，假设整个dump过程有update a  set id=5 where id=4之类的操作，相当于重复执行两次该操作，应该问题不大  
2） 如果是创建从库，遇到上面的sql从库会报错，找不到该记录，这时跳过就好

3）不管是恢复还是创建从库，如果dump过程中有update a set id=id+5 之类的操作，那就有问题，重复执行两次，数据全变了。

深入理解--lock-all-tables

打开general\_log，准备一个数据量较小的db，开启备份，添加--lock-all-tables(其实也是默认设置)和--master-data=2参数，查看general\_log，信息如下，理解--lock-all-tables怎么保证数据一致性

mysql> select thread\_id,argument from general\_log  where thread\_id=185\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 185  
 argument: ucloudbackup@10.10.108.15 on  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 185  
 argument: /\*!40100 SET @@SQL\_MODE='' \*/  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 185  
 argument: /\*!40103 SET TIME\_ZONE='+00:00' \*/  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 4. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 185  
 argument: FLUSH /\*!40101 LOCAL \*/ TABLES  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 5. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 185  
 argument: FLUSH TABLES WITH READ LOCK  
这里flush tables with read lock之后就不会主动unlock tables，保证整个dump过程整个db数据不可更改，也没有事务的概念了  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 6. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 185  
 argument: SHOW MASTER STATUS  
同样记录主库的位置  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 7. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 185  
 argument: 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 GROUP BY LOGFILE\_GROUP\_NAME, FILE\_NAME, ENGINE ORDER BY LOGFILE\_GROUP\_NAME  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 8. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 185  
 argument: SELECT DISTINCT TABLESPACE\_NAME, FILE\_NAME, LOGFILE\_GROUP\_NAME, EXTENT\_SIZE, INITIAL\_SIZE, ENGINE FROM INFORMATION\_SCHEMA.FILES WHERE FILE\_TYPE = 'DATAFILE' ORDER BY TABLESPACE\_NAME, LOGFILE\_GROUP\_NAME  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 9. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 185  
 argument: SHOW DATABASES  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 10. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 185  
 argument: jjj  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 11. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
thread\_id: 185  
 argument: SHOW CREATE DATABASE IF NOT EXISTS \`jjj\`

测试可重复读和快照读(WITH CONSISTENT SNAPSHOT )  
准备工作3.1（测试可重读）  
session 1:  
mysql> select \* from xx;  
\+------+  
| id   |  
\+------+  
|    1 |  
|    2 |  
|    3 |  
|    4 |  
\+------+  
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
Query OK, 0 rows affected (0.00 sec)  
设置事务隔离级别为可重复读

mysql> START TRANSACTION ;  
Query OK, 0 rows affected (0.00 sec)  
我们先不开快照读观察现象

session 2:  
mysql> insert into xx values (5);  
Query OK, 1 row affected (0.00 sec)

session 1:  
mysql> select \* from xx;  
\+------+  
| id   |  
\+------+  
|    1 |  
|    2 |  
|    3 |  
|    4 |  
|    5 |  
\+------+  
5 rows in set (0.00 sec)  
批注：这时因为没有设置快照读，所以当session 2有数据更新时，可查到该数据，接

下来我们继续在session 2 插入数据  
session 2:  
mysql> insert into xx values (6);  
Query OK, 1 row affected (0.00 sec)

这时再观察session 1的数据  
session 1  
mysql> select \* from xx;  
\+------+  
| id   |  
\+------+  
|    1 |  
|    2 |  
|    3 |  
|    4 |  
|    5 |  
\+------+  
5 rows in set (0.00 sec)  
查询发现还是只有5条，表示可重复实现了。  
准备工作3.2（测试快照读）  
session 1  
mysql> select \* from xx;  
\+------+  
| id   |  
\+------+  
|    1 |  
\+------+  
1 row in set (0.00 sec)  
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
Query OK, 0 rows affected (0.00 sec)  
mysql> START TRANSACTION /\*!40100 WITH CONSISTENT SNAPSHOT \*/;  
Query OK, 0 rows affected (0.00 sec)

这时我们在session 2插入数据  
session 2：  
mysql> insert into xx values (2);  
Query OK, 1 row affected (0.00 sec)

这时我们再观察session 1的结果  
session 1：  
mysql> select \* from xx;  
\+------+  
| id   |  
\+------+  
|    1 |  
\+------+  
1 row in set (0.00 sec)  
发现还是只有一条数据，证明实现了快照读  
mysql> commit;  
Query OK, 0 rows affected (0.00 sec)  
mysql> select \* from xx;  
\+------+  
| id   |  
\+------+  
|    1 |  
|    2 |  
\+------+  
2 rows in set (0.00 sec)  
事务1 提交后方可看见第二条记录  
\---------------------  
作者：胡儿胡儿  
来源：CSDN  
原文：https://blog.csdn.net/cug\_jiang126com/article/details/49824471  
版权声明：本文为博主原创文章，转载请附上博文链接！