MySQLdump里的秘密，终于被我发现了

腾讯云数据库 2020-08-14

1519

Part1 引言

在日常数据库运维中，经常要对数据库进行热备。热备的一个关键点是保证数据的一致性，即在备份进行时发生的数据更改，不会在备份结果中出现。mysqldump是实际场景中最常使用的备份工具之一，通过选择合适的选项做备份，mysqldump可以保证数据的一致性，同时尽可能保证进行中的业务不受影响。

那么mysqldump是如何实现一致性备份的？以下我将结合mysqldump过程中mysqld生成的general log与mysqldump的源码来解释mysqldump一致性备份的原理。

注：以下的实例基于MySQL 8.0.18，在不同版本上mysqldump的部分实现会有不同

首先用mysqldump执行一次一致性备份：

$ mysqldump -uroot -p --skip-opt --default-character-set=utf8  --single-transaction --master-data=2 --no-autocommit -B d1> backup.sql
复制

1. 关键参数解释：

-single-transaction：执行一致性备份；

-master-data=2：要求dump结果中以注释形式保存备份时的binlog位置信息；

-B：指定要dump的数据库，在这里d1是一个使用InnoDB作为存储引擎的库，其中只有一个表t1。

执行完成后可以得到mysqld生成的general log，里面记录了mysqldump在备份过程中传给server的指令。

其中关键的步骤我用框框作了标记，具体的解释请看下文。

2. mysqldump一致性备份的主要执行流程

连接server；

两次关闭所有表，第二次关表同时加读锁；

设置隔离级别为“可重复读”，开始事务并创建快照；

获取当前binlog位置；

解锁所有表；

对指定的库与表进行dump。

下面结合SQL内容与源码对以上主要步骤进行依次介绍。

Part2 流程剖析

1. 连接server

mysqldump首先与server建立连接，并初始化session，set一些session级的变量，对应SQL如下图：

其在main函数中对应的源码就是一个对connect_to_db函数的调用：

if (connect_to_db(current_host, current_user, opt_password)) {   free_resources();   exit(EX_MYSQLERR);}
复制

2. 两次关闭所有表，第二次关表同时加读锁

连接建立后，mysqldump紧接着执行两次关表操作，并在第二次关表同时给所有表加上读锁，对应SQL如下图：

这一部分在main函数中对应的源码为：

if ((opt_lock_all_tables || opt_master_data || (opt_single_transaction && flush_logs)) && do_flush_tables_read_lock(mysql)) goto err;
复制

可以看到实际操作由do_flush_tables_read_lock函数进行，但是这里需要注意操作执行的前提条件，观察代码我们可以知道，这个关表操作只会在三种情况下进行：

通过--lock-all-tables选项显式要求给所有表加锁。
通过--master-data选项要求dump出来的结果中包含binlog位置。
通过--single-transaction指定了进行单事务的一致性备份，同时通过--flush-logs要求刷新log文件。

看到这里不难知道，除了第一种情况显式要求加锁之外，情况3要求刷新log前没有其他事务在进行写操作，自然要对所有表加上读锁。情况2要求dump结果中准确记录dump进行时刻的binlog位置，为了准确地得到当前binlog的位置，自然就需要给所有的表加共享锁，防止其他并行事务进行写操作导致binlog更新，因此这里才有一个关表、加读锁的动作。

这里有一个细节，我们知道--single-transaction选项可以执行一致性备份，那么在只有--single-transaction选项时为什么不需要进行关表与加读锁的动作呢？这是因为--single-transaction所保证的一致性备份依赖于支持事务的存储引擎（如InnoDB），在后面会提到，mysqldump通过执行START TRANSACTION WITH CONSISTENT SNAPSHOT会创建一个数据库当前的快照与一个事务id，所有在该事务之后的事务所进行的数据更新都会被过滤，以此来保证备份的一致性。这种方式的优势在于不会在进行一致性备份时干扰其他事务的正常进行，实现了所谓的“热备”，但是缺点在于其依赖事务型存储引擎，对于使用MyISAM等不支持事务的存储引擎的表，--single-transaction无法保证它们的数据一致性。

接着查看do_flush_tables_read_lock函数的源码：

static int do_flush_tables_read_lock(MYSQL *mysql_con) { return (mysql_query_with_error_report( mysql_con, 0, ((opt_master_data != 0) ? "FLUSH *!40101 LOCAL */ TABLES" : "FLUSH TABLES")) || mysql_query_with_error_report(mysql_con, 0, "FLUSH TABLES WITH READ LOCK"));}
复制

可以看到逻辑比较简单，就是向server传入执行两个query，依先后次序分别时FLUSH TABLES和FLUSH TABLES WITH READ LOCK，这里核心的动作在于后面一个query，之所以需要前面的FLUSH TABLES是基于性能的考量，以尽可能减少加锁对其他事务的影响。

3. 设置隔离级别为“可重复读”，开始事务并创建快照

关表操作执行完后，mysqldump接着开启一个新事务并创建快照，对应SQL如下图：

这一部分在main函数中对应的源码为：

if (opt_single_transaction && start_transaction(mysql)) goto err;
复制

可以看到，只有在指定--single-transaction选项时这一步骤才会执行。实际上这一步就是mysqldump实现一致性热备的基础，我们接着查看start_transaction函数的源码：

static int start_transaction(MYSQL *mysql_con) {  省略部分非关键代码与注释 return ( mysql_query_with_error_report(mysql_con, 0, "SET SESSION TRANSACTION ISOLATION " "LEVEL REPEATABLE READ") || mysql_query_with_error_report(mysql_con, 0, "START TRANSACTION " "/*!40100 WITH CONSISTENT SNAPSHOT */"));}
复制

可以看到核心动作是传给server执行的两个query，先是SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ确保当前会话的隔离级别是“可重复读”，然后通过START TRANSACTION *!40100 WITH CONSISTENT SNAPSHOT */来开始一个新事务，产生一个新事务id，同时创建一个快照，dump过程中所使用的数据都基于这个快照。这样，所有在该事务之后的事务所进行的数据更新都会被过滤，备份的数据一致性因此得以保证。

但是，这样的热备方法，依赖于像InnoDB这样支持事务的存储引擎。相反，如MyISAM这种不支持事务的存储引擎在备份过程中的数据一致性则不能被保证。

4. 获取当前binlog位置

随后mysqldump执行一个SHOW MASTER STATUS的query，以获取当前binlog的位置信息：

查看main函数中对应部分的源码可以看到，只有在指定--master-data选项时才会去获取、记录当前的binlog位置：

 

if (opt_master_data && do_show_master_status(mysql)) goto err;
复制

查看do_show_master_status函数的实现，可以看到核心动作就是向server传入执行一个SHOW MASTER STATUS的query，最后将得到的binlog位置信息写入dump结果中。

static int do_show_master_status(MYSQL *mysql_con) {  MYSQL_ROW row;  MYSQL_RES *master;  const char *comment_prefix =      (opt_master_data == MYSQL_OPT_MASTER_DATA_COMMENTED_SQL) ? "-- " : "";  if (mysql_query_with_error_report(mysql_con, &master, "SHOW MASTER STATUS")) {    return 1;  } else {    row = mysql_fetch_row(master);    if (row && row[0] && row[1]) {      print_comment(md_result_file, 0,                    "\n--\n-- Position to start replication or point-in-time "                    "recovery from\n--\n\n");      // 写入dump结果      fprintf(md_result_file,              "%sCHANGE MASTER TO MASTER_LOG_FILE='%s', MASTER_LOG_POS=%s;\n",              comment_prefix, row[0], row[1]);      check_io(md_result_file);    }    // ...  }  return 0;}
复制

5. 解锁所有表

在正式开始dump操作之前，mysqldump会把前面操作中可能加了锁的表全部解锁：

查看main函数中对应部分代码：

 

if (opt_single_transaction &&     do_unlock_tables(mysql)) /* unlock but no commit! */   goto err;
复制

可以看到，只有在指定了--single-transaction选项时才会解锁所有先前被加锁的表，结合前面的思考可以推断，--single-transaction下所进行的备份通过事务性质可以保证数据的一致性，没有必要再保留对所有表所加的锁，因此这里执行解锁，以免阻塞其他事务的进行。

6. 对指定的库与表进行dump

前面的准备操作进行完成后，mysqldump开始正式进行选定库、表的dump操作：

对指定数据库的实际dump由dump_databases函数执行（当指定了--all-databases要求dump所有库时，则由dump_all_databases函数执行）。

查看dump_databases函数的实现：

static int dump_databases(char **db_names) {  int result = 0;  char **db;  DBUG_TRACE;  for (db = db_names; *db; db++) {    if (is_infoschema_db(*db))      die(EX_USAGE, "Dumping \'%s\' DB content is not supported", *db);    if (dump_all_tables_in_db(*db)) result = 1;  }  if (!result && seen_views) {    for (db = db_names; *db; db++) {      if (dump_all_views_in_db(*db)) result = 1;    }  }  return result;} /* dump_databases */
复制

逻辑比较清晰，先dump每个指定的数据库中所有的表，之后如果存在视图，则将对应视图也进行dump。我们的考察重点放在对表的dump上。

实际dump一个表的操作逻辑也比较清晰，就是先获取表的结构信息，得到表的创建语句，然后获取表中每行的实际数据并生成对应的insert语句。

不过，前面的general log中有个值得注意的点是SAVEPOINT的出现，这一点在MySQL 5.5的mysqldump中是没有的，查看dump_all_tables_in_db函数的实现，可以找到设置savepoint的对应代码：

  // 创建savepoint  if (opt_single_transaction && mysql_get_server_version(mysql) >= 50500) {    verbose_msg("-- Setting savepoint...\n");    if (mysql_query_with_error_report(mysql, 0, "SAVEPOINT sp")) return 1;  }  while ((table = getTableName(0))) {    char *end = my_stpcpy(afterdot, table);    if (include_table(hash_key, end - hash_key)) {      dump_table(table, database); // 对表进行dump      // 省略部分代码...      // ROLLBACK操作      /**        ROLLBACK TO SAVEPOINT in --single-transaction mode to release metadata        lock on table which was already dumped. This allows to avoid blocking        concurrent DDL on this table without sacrificing correctness, as we        won't access table second time and dumps created by --single-transaction        mode have validity point at the start of transaction anyway.        Note that this doesn't make --single-transaction mode with concurrent        DDL safe in general case. It just improves situation for people for whom        it might be working.      */      if (opt_single_transaction && mysql_get_server_version(mysql) >= 50500) {        verbose_msg("-- Rolling back to savepoint sp...\n");        if (mysql_query_with_error_report(mysql, 0, "ROLLBACK TO SAVEPOINT sp"))          maybe_exit(EX_MYSQLERR);      }
复制

可以看到创建savepoint是在dump表之前，之后遍历库中的每个表，每当dump完一个表之后，便执行一次ROLLBACK TO SAVEPOINT sp操作，为什么呢？其实上面代码的注释已经解释清楚了：

简单来说，当我们dump完一个表后后面都不再需要使用这个表，这时其他事务的DDL操作不会影响我们dump得到数据的正确性，增加savepoint的意义在于，假如我们要dump表A，savepoint记录了dump表A之前尚未给表A加MDL锁的状态，当开始dump表A时，由于要进行一系列select操作，会给表A加上MDL锁防止其他事务的DDL操作改变表结构导致读动作出错；最后当对表A的dump完成后，后续都不会再访问表A了，此时没有释放的MDL锁没有意义，反而会阻塞其他并行事务对表A的DDL操作。

对此，MySQL的解决方法是在访问表A前通过SAVEPOINT sp记录一个savepoint，在dump完表A之后通过ROLLBACK TO SAVEPOINT sp回到当时的状态，即可释放对表A加的MDL锁，放行其他事务对该表的DDL操作。

Part3 小结

以上是mysqldump基于MySQL 8.0的一致性备份原理介绍，相比MySQL 5.5，现如今MySQL 8.0在mysqldump的实现存在一定改进，除了上面提到的savepoint机制是一个显著区别之外，还有诸如对GTID的支持、对column statistics的dump操作在本文中没有提及，但总体而言，mysqldump在一致性备份上的实现原理并没有多少改变。

Part4 扩展阅读—Percona的实现

MySQL从出现到普及，中途也出现了其他不少优秀的发行版，MySQL中一致性备份的实现其实也并不完美，因此如果能够考量其他发行版在这方面上的实现，也是一件有意义的事情。

1. Backup Lock

在前面我有提到，mysqldump中--single-transaction选项所实现的一致性备份不需要对表加锁，但这一特性基于事务型的存储引擎，因此只对InnoDB表或使用其他事务型存储引擎类型的表能够保证备份时过滤掉其他并行事务的更新操作；但对使用了MyISAM这种不支持事务的存储引擎的表，--single-transaction无法保证其数据的一致性，即若备份过程中出现了来自其他并行事务的更新操作，其很有可能被写入了备份中。

既然如此，若想对MyISAM的表进行备份，又想保证其一致性该怎么办？一种方式可以是在执行mysqldump时传入--lock-all-tables选项，这个选项会使得dump操作进行之前执行一个FLUSH TABLES WITH READ LOCK语句，并保证在dump的全程保持对所有表的读锁。但是无疑这是一种overkill，仅仅是为了保证一部分非事务型存储引擎的表的一致性，就需要对所有表加锁，进而业务上所有对server的写操作被阻塞一段时间（若备份的数据量大，这简直会造成一场灾难）。

这一问题，我尚未在MySQL 8.0中找到相应的好的解决方式，不过Percona对此给出了一个方案：在Percona发行版的mysqldump中，执行时可以传入一个--lock-for-backup选项，这个选项会使得mysqldump在dump之前，执行一个LOCK TABLES FOR BACKUP语句，这是一个Percona独有的query，其主要做以下几件事情：

阻塞对MyISAM, MEMORY, CSV, ARCHIVE表的更新操作；
阻塞对任何表的DDL操作；
不阻塞对临时表与log表的更新操作。

显然，有了以上的特性，当同时传入--lock-for-backup与--single-transaction两个选项同时，mysqldump可以保证所有表的数据一致性，并且尽可能保证造成最少的线上业务干扰。

这一部分逻辑可以在Percona Server 8.0中mysqldump的代码中找到，在main函数中：

  if (opt_lock_all_tables ||      (opt_master_data &&       (!has_consistent_binlog_pos || !has_consistent_gtid_executed)) ||      (opt_single_transaction && flush_logs)) {    if (do_flush_tables_read_lock(mysql)) goto err;    ftwrl_done = true;  } else if (opt_lock_for_backup && do_lock_tables_for_backup(mysql))    goto err;
复制

细心的朋友会发现，这是对上面的“关表加读锁操作”进行的逻辑改写，其增加了一个else if逻辑分支，取代了之前的FLUSH TABLES; FLUSH TABLES WITH READ LOCK;操作，主要目的是为了与--single-transaction进行的一致性备份更好地兼容，实现对线上业务尽可能少的阻塞。

接着查看do_lock_tables_for_backup函数的实现，可以看到就是简单地向server传入一个Percona独有的LOCK TABLES FOR BACKUP语句：

static int do_lock_tables_for_backup(MYSQL *mysql_con) noexcept {  return mysql_query_with_error_report(mysql_con, 0, "LOCK TABLES FOR BACKUP");}
复制

2. Binlog Snapshot

在MySQL 8.0的实现中，有一个常用的选项，仍然会导致“讨人厌”的FLUSH TABLES WITH READ LOCK的执行，即--master-data选项。

前面提到，--master-data选项要求在dump之后的结果中存有当前备份开始时的binlog位置，为了满足所获得binlog位置的一致性，需要在执行SHOW MASTER STATUS前，获取对所有表的读锁以阻塞所有binlog的提交事件，因此要求执行一次FLUSH TABLES WITH READ LOCK。但是有没有更好的方式？Percona同样给出了自己的解决方法。

在Percona Server中，新增了两个全局status：Binlog_snapshot_file和Binlog_snapshot_pos，分别用来记录当前的binlog文件与binlog位置，通过SHOW STATUS LIKE 'binlog_snapshot_%'即可获取两个status的值。那么使用这个方式，跟SHOW MASTER STATUS有什么区别？

二者的区别在于，Binlog_snapshot_file和Binlog_snapshot_pos这两个status具有事务性，只要在执行SHOW STATUS LIKE 'binlog_snapshot_%'这个语句之前通过START TRANSACTION WITH CONSISTENT SNAPSHOT创建了新事务与一致性快照，Binlog_snapshot_file和Binlog_snapshot_pos所记录的则正是该事务开始时的binlog文件与位置信息，进而binlog信息的一致性得到保证，而这一过程的全程都不需要FLUSH TABLES WITH READ LOCK的执行。

相对的，SHOW MASTER STATUS是不具备事务性的，每次执行该语句返回的都是当前最新的binlog位置信息，这也是为什么执行它之前需要对所有表上读锁。

3. 参考阅读：

mysqldump — A Database Backup Program
Introducing backup locks in Percona Server

特惠体验云数据库

↓↓更多惊喜优惠请点这儿~

mysql tencentdb

文章转载自腾讯云数据库，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。