MySQL数据库在线备份保证数据一致性

MySQL在线备份的问题

只读的数据备份都是按照表的顺序一个个表来拷贝，拷贝会有先后，因为拷贝过程中没有数据更新，整个库的数据会保持一致性。在线备份，mysql还在支持业务运行，拷贝过程中，不断有事务提交更新数据，如果事务更新涉及到已拷贝表和未拷贝的表，就会出现数据不一致性问题。

例如：备份过程中提交事务A，事务A涉及到已拷贝表order和未拷贝的account表，最终导致order表中没有事务A的操作的数据，account表中有事务A的变更数据。导致了备份文件中出现数据的不一致性。

1，开始在线备份。

2，备份order表完成。

3，用户下单，提交事务A，事务A的两个操作：

        3.1，account表中的balance由100变成了50（订单花费50，balance=100-50）。

        3.2，order表中新增一条记录。

4，备份account表。

5，在线备份完成。

-- 备份前数据 
select count(1) from order where user_id = 1;
-- 0 records
select balance from account;
-- balance = 100
​
-- 1.开始备份 
​
-- 2.备份order表完成  用户的订单记录为：0 records
​
-- 3事务A开始
start transaction
-- 3.1
update account balance = balance-50 where user_id = 1;
-- 3.2
insert order (order_id,user_id,goods_id) values (1,1,1);
-- 3事务提交
​
-- 提交事务A后的数据
select count(1) from order where user_id = 1;
-- 1 records 
select balance from account;
-- balance = 50
​
-- 4，备份balance表。balance=50
​
-- 5，在线备份完成
-- 备份文件中的数据
select count(1) from order where user_id = 1;  -- order表提前备份，没有将事务A的数据拷贝过来。
-- 0 records 
select balance from account; -- account表较晚备份，将事务A的订单数据拷贝了过来。
-- balance = 50
​
​

备份过程中新事务提交，导致备份文件出现了数据不一致：用户余额少了50，订单记录没有变化。

如何解决MySQL数据库在线备份中出现数据不一致性

有人说数据库添加全局锁

flush table with read lock -- 数据库处于只读状态，所有的增删改语句都会被阻塞
unlock tables -- 释放全局锁

使用全局锁确实会让整个数据库处于只读状态，然后使用mysqldump工具进行备份时，也不会出现数据不一致的情况，但是全局锁会导致数据库不能更新，在线业务停滞，全局锁不能解决在线备份数据不一致问题。

使用mysqldump添加--single-transaction参数，可以确保在线备份数据的一致性

mysqldump --single-transaction -u username -p database_name > backup.sql

--single-transaction参数作用：备份时开启一个事务B，这个事务会生成一个readview（一致性的视图），这个readview对事务B后的所有事务都不可见，从而保证了备份数据的一致性。

mysql的默认事务隔离级别时：可重复读。在这个隔离级别下，事务B开启后，生成一个readview，在整个事务B过程中，读取数据都是从readview中读取，readview是配合undolog日志来实现mvcc多版本并发访问机制，从而实现对readview之后开启的事务的数据的隔离，只读取到readview之前已经提交事务的数据，从而保证了数据的一致性。

--single-transaction的使用有范围：1，它需要创建一个事务，所以它依赖于能支持事务的存储引擎，InnoDB可以，MyISAM不可以；2，只有在事务隔离级别为：可重复读，才能够实现一个readview的可重复读并且忽略后续的事务更改的记录，只读取创建readview之前的已完成的事务的记录。