mysqldump大致原理以及mysqldump备份过程中进行DDL操作的影响
- MySQL版本:5.7.18
隔离级别:REPEATABLE-READ
如果隔离级别不是RR,启动事务快照读的时候会报错:
root@localhost : (none) 10:38:48> show variables like '%isola%';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)
root@localhost : (none) 10:38:50> START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Warning (Code 138): InnoDB: WITH CONSISTENT SNAPSHOT was ignored because this phrase can only be used with REPEATABLE READ isolation level.
root@localhost : (none) 10:39:01> set global tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)
#退出客户端重进
root@localhost : (none) 10:44:17> show variables like '%isola%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
root@localhost : (none) 10:44:38> START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ ;
Query OK, 0 rows affected (0.00 sec)
mysqldump大致原理
- 1.FLUSH /!40101 LOCAL / TABLES
关闭所有在内存中打开的表,清理查询缓存。 - 2.FLUSH TABLES WITH READ LOCK
加一个全局读锁,只允许读,不允许更新操作。 - 3.SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
设置当前会话的事务隔离等级为RR,RR可避免不可重复读和幻读,保证在备份期间,一个事务中所有相同的查询读取到同样的数据。 - 4.START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT /
获取当前数据库的快照,这个是由mysqldump中–single-transaction决定的,类似于开启事务并对所有表执行了一次SELECT操作,这样可保证备份时,在任意时间点执行select * from table得到的数据和执行START TRANSACTION WITH CONSISTENT SNAPSHOT时的数据一致。 - 5.SHOW MASTER STATUS
–master-data决定的,记录了开始备份时,binlog的状态信息,包括MASTER_LOG_FILE和MASTER_LOG_POS - 6.UNLOCK TABLES
释放锁,也就是说其实mysqldump这个语句,其实是有锁表,只不过锁了很短的时间 - 7.SHOW CREATE DATABASE IF NOT EXISTS
test
生成创库语句。 - 8.SAVEPOINT sp
设置SAVEPOINT,然后备份完每个表后再回滚到该SAVEPOINT。 - 9.show create table
test
生成创表语句 - 10.SELECT /!40001 SQL_NO_CACHE / * FROM
test
该语句会查询到表test1的所有数据,在备份文件中会生成相应的insert语句。
其中SQL_NO_CACHE的作用是查询的结果并不会缓存到查询缓存中。 - 11.SHOW TRIGGERS LIKE 'test';
备份触发器。 - 12.ROLLBACK TO SAVEPOINT sp
如果不执行ROLLBACK TO SAVEPOINT sp,可能会阻塞同时进行的DDL操作,执行了该语句后,DDL操作可以继续执行。 - 13.SHOW FUNCTION STATUS WHERE Db = 'test'
SHOW CREATE FUNCTIONmycat_seq_currval
SHOW PROCEDURE STATUS WHERE Db = 'test'
用于备份存储过程和函数
第一种情况
session 1 session 2
test 05:37:00> START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ ;
Query OK, 0 rows affected (0.00 sec)
test 05:37:02> savepoint sp;
Query OK, 0 rows affected (0.00 sec)
test 05:37:08> show create table uniq_test;
Query OK, 0 rows affected (0.00 sec)
test 05:37:14> alter table uniq_test add column name7 varchar(10);
Query OK, 0 rows affected (0.32 sec)
test 05:37:24> select * from uniq_test;
ERROR 1412 (HY000): Table definition has changed, please retry transaction
test 05:37:39> rollback to savepoint sp;
Query OK, 0 rows affected (0.00 sec)
- START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT之后 /到select * from uniq_test之前,这中间,如果session 2执行ddl操作,则session 1中接着执行select * from uniq_test;就会报错:Table definition has changed, please retry transaction
体现在mysqldump(unlock tables语句到SELECT /!40001 SQL_NO_CACHE / * FROM test 语句之间)出来的文件就是无数据,无insert语句,表结构是在的。
第二种情况
session 1 session 2
test 05:37:00> START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ ;
Query OK, 0 rows affected (0.00 sec)
test 05:37:02> savepoint sp;
Query OK, 0 rows affected (0.00 sec)
test 05:37:08> show create table uniq_test;
Query OK, 0 rows affected (0.00 sec)
test 05:37:24> select * from uniq_test;
+----+------+-------+-------+-------+-------+-------+
| id | name | name1 | name2 | name3 | name4 | name5 |
+----+------+-------+-------+-------+-------+-------+
| 1 | NULL | NULL | NULL | NULL | NULL | NULL |
| 1 | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | NULL | NULL | NULL | NULL | NULL | NULL |
+----+------+-------+-------+-------+-------+-------+
4 rows in set (0.00 sec)
test 05:37:44> alter table uniq_test add column name6 varchar(10);
test 05:38:10> rollback to savepoint sp;
test 05:38:46> alter table uniq_test add column name6 varchar(10);
Query OK, 0 rows affected (21.73 sec)
- 在select * from uniq_test;之后到rollback to savepoint sp;之前,这中间,如果session 2执行ddl操作,则session 2的ddl操作会hang住,此时show processlist:
test 05:37:52> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------------+
| 21 | root | localhost | test | Query | 4 | Waiting for table metadata lock | alter table uniq_test add column name6 varchar(10) |
| 22 | root | localhost | test | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------------+
2 rows in set (0.00 sec)
可以看到DDL操作在等待元数据锁。当session 1中执行rollback to savepoint sp;后,session 2的ddl操作才会进行下去。所以这也是为什么要设置savepoint的原因。开启一个事务后,只有rollback或者commit才能把锁释放,但是这个大事务START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT /无法简单的对整个事务进行rollback或者commit,所以采用了:备份表之前设立一个savepoint每备份完成一个表就rollback这张表从而释放掉此表的元数据锁,从而对并发DDL操作的影响降到最低。
show create table uniq_test语句不会持有元数据锁。