mysqldump备份时导致所有数据表锁定，无法提供服务？

解决mysqldump备份时导致所有数据表锁定，无法提供服务的问题

背景
有接到用户反馈：系统页面卡住不动。。

这个问题发生在生产环境下数据库的每日备份过程中，一个20+G的数据库，在定时备份时（关于如何配置线上数据库定时备份，可参考文章：Ubuntu下对MySQL指定数据库定时备份）导致数据库卡死，持续时长5分钟左右，在此期间线上服务无响应。。

分析
备份一个库：mysqldump -hhost -uroot -ppassword dbname > /opt/backup.sql

直接使用上述语句备份数据库，存在的问题是：备份完成之前，会把所有的表锁住，导致无法写入。。这在生产环境下是不可接受的，而且这才20+G，后续数据库Size会越来越大，备份的时间必然也越长。。

那么，有没有一种方法在完成备份的同时并不锁定表呢？？往下看。

解决
在使用mysqldump备份时，加一个参数：--single-transaction，这样，便可以在备份库的时候并不锁定数据表。

mysqldump -hhost -uroot -ppassword --single-transaction dbname > /opt/backup.sql

原理说明：

Some internals on how this actually works - before the utility starts fetching data from the server, it sends it a START TRANSACTION command. This command serves few goals in this case. The first one, is to have a consistent backup created, from a specific point in time, regardless of changes that occur after the backup started. The second goal is to prevent those locks from happening, as we are performing our actions as part of a database transaction.

Notes:
仅对支持事务的表有效，比如InnoDB，对非事务型的表比如MyISAM则没有效果。

另外需要注意的是，当使用--single-transaction这个参数备份大型数据库时，可考虑和--quick参数结合使用。--quick可以让mysqldump在备份的过程中每次只取一行数据，而不是把所有行都缓存到内存里，这主要考虑了服务器内存限制，以防备份失败。

mysqldump -hhost -uroot -ppassword --single-transaction --quick dbname > /opt/backup.sql

这样，在备份时并不会对数据表加锁，线上业务完全不受影响，经测试，备份耗时基本不变。

Reference: How to backup MySQL database using Mysqldump without locking?

使用 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 提交后方可看见第二条记录
————————————————
                        
原文链接：https://blog.csdn.net/cug_jiang126com/article/details/49824471