mysqldump的实现原理

191次阅读

没有评论

共计 5750 个字符，预计需要花费 15 分钟才能阅读完成。

对于 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 表空间快照占用的空间。但这样做，就无法实现对所有表的一致性备份。

使用 mysqldump 进行 MariaDB 的备份 http://www.linuxidc.com/Linux/2015-07/120294.htm

使用 mysqldump 导出数据库 http://www.linuxidc.com/Linux/2014-10/108192.htm

基于 mysqldump 快速搭建从库 http://www.linuxidc.com/Linux/2015-04/116170.htm

恢复 mysqldump 创建的备份集 http://www.linuxidc.com/Linux/2015-02/113631.htm

使用 mysqldump 命令行工具创建逻辑备份 http://www.linuxidc.com/Linux/2015-02/113629.htm

mysqldump 实现数据库逻辑备份 http://www.linuxidc.com/Linux/2015-08/121551.htm

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-05/131566.htm

正文完

星哥玩云-微信公众号