现网中数据库运维时,要经常对数据库做热备。为保证恢复时数据的完整性与一致性, 一种方法是在备份之前锁表,但锁表会影响正在运行的业务。
mysqldump是当前MySQL中最常用的备份工具,通过mysqldump --help可以查看很多选项。
在mysqldump开始备份后,执行其它的更新数据库操作,mysqldump备份的结果会不会包含备份结束前对数据库的更改呢?在一个时间点备份, 最理想的结果是备份的结果就是备份开始时的数据库快照。通过选择合适的选项做备份,mysqldump可以保证数据一致性且不影响业务的运行。
mysqldump产生的备份,最终是要结合binlog进行恢复。mysqldump也可以准确得到binlog的恢复点。
那么mysqldump中如何保证数据一致性并生成备份的呢?下面通过一个常用示例来解释mysqldump的原理。(仅针对InnoDB存储引擎举例)
mysqldump –uuser -p --skip-opt -q -R --single-transaction --default-character-set=utf8 --master-data=2 --create-option --no-autocommit –S ${sock} -B ${DBName} > backup.sql
执行上述命令后,会得到一个可以用于恢复的backup.sql文件。bauckup.sql中主要有一系列的create语句与insert语句,恢复的过程就是创建原来存在的数据库及表,并将所有表数据直接insert到表中。
在执行mysqldump命令前,在MySQL中执行set global general_log = on来打开通用日志,该日志默认是关闭的。通用日志记录了MySQL服务器响应的所有SQL语句信息,mysqldump命令实际上是通过构造一系列 SQL语句并发送到数据库服务器,利用服务器的响应信息从而构造出备份文件backup.sql。
下图是执行上述mysqldump命令后,general log中记录的部分内容。
第一行,执行connect是通过mysqldump选项中的-u, -p, -S来进行端口、用户验证,然后连接服务器。
其中的flush tables 、flush tables with read lock、 unlock tables及其中的show master status是响应选项--master-data. 通过一个瞬间的锁表,利用show master status来得到binlog的位置。在backup.sql中,可以找到类似下面的信息。
进行瞬间的锁表就是要保证得到正确的binlog位置。flush tables后,当前数据库快照就是我们要备份的,通过show master status得到binlog位置信息。那么如何保证对当前数据库快照进行备份呢?结合选项--single-transaction,mysqldump的处理是start transaction。由于INNODB的MVCC机制,start transacion会产生一个事务id,利用这个事务id可以过滤该事务之后的事务对数据库的更新操作,从而得到当前快照的备份。
有个细节要注意,flush tables ;flush tables with read lock; 为什么不直接就加上read lock,这样写的好处是什么? 其实这样做可以尽可能少的减少加锁的影响,减少冲突。
另一个细节要注意是start transaction要放在flush tables 与 unlock tables之间,不能放在前面或者后面。flush tables后,当前数据库快照就是我们要备份的,然后show master status也得到了binlog位置信息。而start transaction是通过begin一个事务来获取这个快照的,如果放在前面或者后面,会造成数据丢失或者数据的重复插入。
另外,还有很多general log信息,来得到当前快照中的数据库、表、存储过程及数据等。下面仅以test库中一个t1表的部分内容为例说明。
show create database if not exists ‘test’; 服务器响应该语句得到test库的创建语句
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test`;
加上if not exists保证在create时不存在test库才创建test。
show tables 获取test库中所有的表。
show table status like ‘t1’ 得到t1表的状态信息,便于进一步处理。
show create table `t1` 生成t1表的创建语句。
SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1; 该语句得到表t1的所有数据,在backup.sql中会生成相应的insert语句,恢复时执行这些数据的insert操作。其中sql_no_cache的作用是避免查询结果缓存(不是不在缓存中查询结果)。
在general log中还可以看到一系列mysqldump处理后发送到服务器的语句,然后mysqldump利用服务器返回的结果进行处理,从而得到备份文件backup.sql。
来源:
mysqldump备份原理 | 腾讯游戏DBA团队 http://tencentdba.com/blog/mysqldump-backup-principle/