你的位置:首页 > 数据库

[数据库]mysqldump的备份原理


对于MySQL的备份,可分为以下两种:

1. 冷备

2. 热备

其中,冷备,顾名思义,就是将数据库关掉,利用操作系统命令拷贝数据库相关文件。而热备指的是在线热备,即在不关闭数据库的情况下,对数据库进行备份。实际生产中基本上都是后者。

关于热备,也可分为两种方式:

1. 逻辑备份

2. 物理备份

对于前者,常用的工具是MySQL自带的mysqldump,对于后者,常用的工具是Percona提供的XtraBackup。

对于规模比较小,业务并不繁忙的数据库,一般都是选择mysqldump。

那么,mysqldump的备份原理是什么呢?

抛开源码不谈,其实我们可以通过打开general log,查看mysqldump全库备份时执行的命令来了解mysqldump背后的原理。

 

打开general log

mysql> set global general_log=on;

其中,general log的存放路径可通过以下命令查看

mysql> show variables like '%general_log_file%';

 

执行全库备份

# mysqldump --master-data=2  -R --single-transaction -A -phello > 3306_20160518.sql

其中

--master-data指定为2指的是会在备份文件中生成CHANGE MASTER的注释。具体在本例中,指的是

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql2-bin.000049', MASTER_LOG_POS=587;

如果该值设置为1,则生成的是CHANGE MASTER的命令,而不是注释。

-R 备份存储过程与函数

--single-transaction 获取InnoDB表的一致性备份。

-A 相当于--all-databases。

 

下面来看看general log中的内容

160518 11:00:59  14 Connect  root@localhost on          14 Query   /*!40100 SET @@SQL_MODE='' */          14 Query   /*!40103 SET TIME_ZONE='+00:00' */          14 Query   FLUSH /*!40101 LOCAL */ TABLES          14 Query   FLUSH TABLES WITH READ LOCK          14 Query   SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ          14 Query   START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */          14 Query   SHOW VARIABLES LIKE 'gtid\_mode'          14 Query   SHOW MASTER STATUS          14 Query   UNLOCK TABLES          14 Query   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          14 Query   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          14 Query   SHOW DATABASES          14 Query   SHOW VARIABLES LIKE 'ndbinfo\_version'

其中,比较重要的有以下几点:

1. FLUSH /*!40101 LOCAL */ TABLES

    Closes all open tables, forces all tables in use to be closed, and flushes the query cache.

2. FLUSH TABLES WITH READ LOCK

    执行flush tables操作,并加一个全局读锁,很多童鞋可能会好奇,这两个命令貌似是重复的,为什么不在第一次执行flush tables操作的时候加上锁了,其实,这样做的原因在于可以尽量减少加锁的影响。

    加上全局读锁,只允许读,不允许更新操作。

3. SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

    设置当前会话的事务隔离等级为RR,RR可避免不可重复读和幻读。

4. START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

    获取当前数据库的快照,这个是由mysqldump中--single-transaction决定的。

   这个只适用于支持事务的表,在MySQL中,只有Innodb。

   注意:START TRANSACTION和START TRANSACTION WITH CONSISTENT SNAPSHOT并不一样,

   START TRANSACTION WITH CONSISTENT SNAPSHOT相当于在执行完START TRANSACTION后对每个Innodb表执行了SELECT操作。

   不明白事务的童鞋可能觉得这点会比较绕,其实所谓的不可重复读和幻读可简单理解为,在同一个事务内,两次SELECT的结果并不相同。

   之所以要使用START TRANSACTION WITH CONSISTENT SNAPSHOT,因为每个表的备份时间并不相同,这就要求在对第一张表进行备份的期间,对第二个表进行的操作,并不会反映到第二张表开始备份时执行的SELECT操作中。(注:mysqldump备份的底层实现即是select * from tab)。而这用START TRANSACTION就无法实现。

5.  SHOW MASTER STATUS

     这个是由--master-data决定的,记录了开始备份时,binlog的状态信息,包括MASTER_LOG_FILE和MASTER_LOG_POS

6.  UNLOCK TABLES

     释放锁。

 

因为我的数据库中只有以下四个库

mysql> show databases;+--------------------+| Database      |+--------------------+| information_schema || mysql       || performance_schema || test        |+--------------------+4 rows in set (0.03 sec)

备份的时候可以发现只备份了mysql和test,并没有备份information_schema和performance_schema。

 

下面来看看备份mysql和test的日志输出信息,

因日志输出信息太多,在这里,只选择test库的日志信息。test库中一共有两张表test和test1。

          14 Init DB  test          14 Query   SHOW CREATE DATABASE IF NOT EXISTS `test`          14 Query   SAVEPOINT sp          14 Query   show tables          14 Query   show table status like 'test'          14 Query   SET SQL_QUOTE_SHOW_CREATE=1          14 Query   SET SESSION character_set_results = 'binary'          14 Query   show create table `test`          14 Query   SET SESSION character_set_results = 'utf8'          14 Query   show fields from `test`          14 Query   SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`          14 Query   SET SESSION character_set_results = 'binary'          14 Query   use `test`          14 Query   select @@collation_database          14 Query   SHOW TRIGGERS LIKE 'test'          14 Query   SET SESSION character_set_results = 'utf8'          14 Query   ROLLBACK TO SAVEPOINT sp          14 Query   show table status like 'test1'          14 Query   SET SQL_QUOTE_SHOW_CREATE=1          14 Query   SET SESSION character_set_results = 'binary'          14 Query   show create table `test1`          14 Query   SET SESSION character_set_results = 'utf8'          14 Query   show fields from `test1`          14 Query   SELECT /*!40001 SQL_NO_CACHE */ * FROM `test1`          14 Query   SET SESSION character_set_results = 'binary'          14 Query   use `test`          14 Query   select @@collation_database          14 Query   SHOW TRIGGERS LIKE 'test1'          14 Query   SET SESSION character_set_results = 'utf8'          14 Query   ROLLBACK TO SAVEPOINT sp          14 Query   RELEASE SAVEPOINT sp                   14 Query   use `test`          14 Query   select @@collation_database          14 Query   SET SESSION character_set_results = 'binary'          14 Query   SHOW FUNCTION STATUS WHERE Db = 'test'          14 Query   SHOW CREATE FUNCTION `mycat_seq_currval`          14 Query   SHOW PROCEDURE STATUS WHERE Db = 'test'          14 Query   SET SESSION character_set_results = 'utf8'          14 Quit

从上述输出可以看出:

1. 备份的核心是SELECT /*!40001 SQL_NO_CACHE */ * FROM `test1`语句。

    该语句会查询到表test1的所有数据,在备份文件中会生成相应的insert语句。

    其中SQL_NO_CACHE的作用是查询的结果并不会缓存到查询缓存中。

2. SHOW CREATE DATABASE IF NOT EXISTS `test`,show create table `test1`

    生成创库语句和创表语句。

3. SHOW TRIGGERS LIKE 'test1'

    可以看出,如果不加-R参数,默认是会备份触发器的。

4. SHOW FUNCTION STATUS WHERE Db = 'test'

    SHOW CREATE FUNCTION `mycat_seq_currval`

    SHOW PROCEDURE STATUS WHERE Db = 'test'

    用于备份存储过程和函数。

5. 设置SAVEPOINT,然后备份完每个表后再回滚到该SAVEPOINT。

    不知道为什么要这么设置,感觉这样完全没必要,

    因为前面通过START TRANSACTION WITH CONSISTENT SNAPSHOT开启的事务只能通过commit或者rollback来结束,而不是ROLLBACK TO SAVEPOINT sp。

 

总结:

1. mysqldump的本质是通过select * from tab来获取表的数据的。

2. START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */必须放到FLUSH TABLES WITH READ LOCK和UNLOCK TABLES之间,放到之前会造成START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */和FLUSH TABLES WITH READ LOCK之间执行的DML语句丢失,放到之后,会造成从库重复插入数据。

3. mysqldump只适合放到业务低峰期做,如果备份的过程中数据操作很频繁,会造成Undo表空间越来越大,undo表空间默认是放到共享表空间中的,而ibdata的特性是一旦增大,就不会收缩。

4. mysqldump的效率还是比较低下,START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */只能等到所有表备份完后才结束,其实效率比较高的做法是备份完一张表就提交一次,这样可尽快释放Undo表空间快照占用的空间。但这样做,就无法实现对所有表的一致性备份。

 

参考:

http://tencentdba.com/blog/mysqldump-backup-principle/