备份篇 | MySQL的三种逻辑备份工具实践讲解

本文约 3869字，预计阅读需要 15分钟。

前言：鉴于最近的滴滴、阿里云的互联网大事件，验证了搞基础运维的那句话：高可用、数据备份、数据容灾、备份验真、切换演练、一致性保障、应急预案等，真真一个都不能少。建议后面把保障系统稳定性纳入OKR。

数据库备份工具通常有物理备份工具和逻辑备份工具两大类。常用物理备份工具有Percona XtraBackup、MySQL Enterprise Backup；常用逻辑备份工具有mysqldump、mydumper、mysqlshell等实现。

鉴于数据库备份和恢复时长、及易操作性的综合考量。一般来说，100GB内的数据库通常考虑使用轻巧灵活的逻辑备份方式，可每天定时进行全量备份，备份时间在业务低峰期发起，保留最近两周的备份恢复窗口。100GB以上的数据库通常考虑使用备份恢复速度快的物理备份方式，一般选择一周一个全备，其余每天进行增量备份，备份时间建议在业务低峰期发起，建议至少保留最近两周的备份恢复窗口。

逻辑备份的另一种场景是帮助业务用户、DBA方便、简洁的迁移小量数据（通常认为在5GB内），尤其是在跨操作系统、跨数据库版本的背景下，相比物理备份, 逻辑备份更为灵活方便和易操作。本文将讲述3种常用的MySQL数据库逻辑备份工具（mysqldump、mydumper、mysqlshell），结合对表级、库级和实例级对象进行备份和恢复来帮助读者学习和理解。

三种常用MySQL逻辑备份工具有：

1. mysqldump，单线程工具，MySQL官方备份工具，特点是备份速度慢，命令和操作简单。

2. mydumper，多线程工具，开源备份工具，特点是可手动指定备份线程数，备份速度较之mysqldump快，命令和操作稍显复杂。

3. mysqlshell，多线程工具，MySQL官方推荐的一款快速备份恢复工具，工具中集成了备份函数，该工具需单独部署才可使用。特点是备份速度较之mydumper又有提升，命令和操作简洁，推荐在MySQL8.0环境中使用。

（1）mysqldump逻辑备份工具讲解

先来看如下这条常用的mysqldump逻辑备份示例命令，此条逻辑备份命令是对整库数据发起的逻辑备份，包含备份数据库的触发器、事件、存储过程和函数；且在备份期间该条备份session使用RR（REPEATABLE READ）的隔离机制，不影响其他session会话对数据库的读写操作。具体来看如下命令：

# mysqldump -uroot -proot -h127.0.0.1 --single-transaction --master-data=2 --routines --events --triggers  --all-databases | gzip > `date +%Y%m%d`.sql.gz

参数介绍：

--single-transaction ：

1. 备份开始时事务隔离级别设置成session级 repeatable read可重复读；

2. 然后启动一个事务（执行begin），备份结束时提交备份事务（执行commit）。

--master-data=2：

备份过程中记录主库的binlog和pos点，在备份文件中注释掉这一行。

--routines：导出数据库中的存储过程和函数

--events：导出数据库中的事件

--triggers：导出数据库中的触发器

表1 mysqldump逻辑备份执行步骤

接下来通过命令讲解如何使用mysqldump逻辑备份工具对表级、库级和实例级对象进行备份和恢复演练。

1.mysqldump备份表级tables

# mysqldump -uroot -proot -h127.0.0.1 -P3306 --single-transaction --master-data=2 --routines --events --triggers --tables table1,table2 | gzip > `date +%Y%m%d`.sql.gz

2.mysqldump备份库级schemas

# mysqldump -uroot -proot -h127.0.0.1 -P3306 --single-transaction --master-data=2 --routines --events --triggers --databases schema1,schema2 | gzip > `date +%Y%m%d`.sql.gz

3.mysqldump备份实例级instance

# mysqldump -uroot -proot -h127.0.0.1 -P3306 --single-transaction --master-data=2 --routines --events --triggers  --all-databases | gzip > `date +%Y%m%d`.sql.gz

4.mysqldump恢复数据

因备份出来的是SQL执行语句，表级、库级和实例级对象的逻辑备份恢复数据命令是一致的，示例命令如下：

# mysql -uroot -proot -h127.0.0.1 -P3306 < 20231118.sql

（2）mydumper 逻辑备份工具讲解

mydumper 是一款社区开源的逻辑备份工具。该工具主要由C语言编写，由MySQL、Facebook等公司人员开发维护。mysqldump是单线程逻辑备份方式，备份速度较慢，为提升逻辑备份的速度，mydumper应运而生。mydumper常被用于MySQL跨平台迁移，字符集转换重导库，大版本升级，误操作大表误删除恢复等场景。

mydumper主要有以下几点特性：

1.C语言编写，使用glibc库；

2.执行速度比mysqldump快约10倍；

3.支持事务性表和非事务性表一致的快照；

4.支持快速文件压缩，支持到备份文件切块；

5.多线程备份(因是多线程逻辑备份，备份后会产生多个备份文件)；

6.多线程恢复(适用于0.2.1以上版本)；

7.备份时对Myisam表加FTWRL(flush tables with read lock)；

8.以守护进程的方式工作，支持定时快照；

9.基于GNU GPLv3协议开源。

还是先来看一条常用的mydumper 逻辑备份示例命令，此条备份命令是备份除指定库（mysql|sys|information_schema|performance_schema）外的数据备份。

mydumper -uroot -proot -h127.0.0.1 -P3306 --regex '^(?!(mysql|sys|information_schema|performance_schema))' -c -e -G -E -R --use-savepoints -t 8 -L data/backup/mydumper.log -o data/backup/

参数介绍：

--regex '^(?!(mysql|sys|information_schema|performance_schema|test))'：使用正则表达式排除特定的数据库（mysql、sys、information_schema、performance_schema、test）。

-c：启用压缩模式，以减小备份文件的大小

-e：备份事件（Events）

-G：备份存储过程（Stored Procedures）

-E：备份事件调度器（Event Scheduler）

-R：备份表结构和数据

--use-savepoints：使用保存点进行事务处理，以提高备份的一致性

-t 8：指定使用的线程数，这里设置为8个线程并行备份

-L 日志推荐输出到文件，方便后续验证结果和排错

-o：/data/backup/：指定备份文件的输出目录

mydumper进行数据备份时的主逻辑由一个主线程和多个备份子线程共同完成，默认情况下为4个子线程（可以手动设置）。

mydumper主要工作步骤：

表2 mydumper 主线程执行步骤

表3 mydumper 子进程执行步骤

备份主线程和备份子线程的协作使得mydumper能够并发备份数据库，提高备份速度和效率。值得注意的是，虽然mydumper支持表级别的并行操作，且在导出的时候会对大的表数据进行分块chunk导出，同一个表的chunks是在同一个线程中处理的，并非多线程并行的。

接下来通过命令讲解如何使用mydumper逻辑备份工具对表级、库级和实例级对象进行备份和恢复演练。

1.mydumper备份表级tables（备份一个库的两个表）

mydumper -uroot -proot -h127.0.0.1 -P3306 -B schema1 -T table1,table2 -t 4 -r 100000 -c --less-locking -v 3 -D -L /data/backup/mydumper.log -o /data/backup/

2.mydumper备份库级schemas

mydumper -uroot -proot -h127.0.0.1 -P3306 -B schema1 -t 4 -r 100000 -c --less-locking -v 3 -D -L /data/backup/mydumper.log -o /data/backup/

3.mydumper备份库级instance

mydumper -uroot -proot -h127.0.0.1 -P3306 --regex '^(?!(mysql|sys|information_schema|performance_schema))' -c  -t 8 -L /data/backup/mydumper.log -o /data/backup/

参数介绍：

--regex '^(?!(mysql|sys|information_schema|performance_schema|test))'：使用正则表达式排除特定的数据库（mysql、sys、information_schema、performance_schema、test）。

-r, --rows 尝试将表的数据按多少行分隔为块，当使用--chunk-filesize 时

-c, --compress 压缩输出文件

--less-locking 在 InnoDB 表使用最小的锁表时间

-v, --verbose 输出信息的等级 0 = silent, 1 = errors, 2 = warnings, 3 = info 默认为 2

--daemon参数用于将mydumper进程以守护进程（daemon）的形式运行。当使用--daemon参数启动mydumper时，它将在后台运行并不会占用终端窗口。

mydumper和myloader是相对应的一组可执行程序，myloader用于恢复mydumper备份文件，myloader从备份文件中恢复数据不需要先创建数据库。myloader支持恢复到指定时间点的备份文件，可支持指定要恢复的数据表和数据行。

4.myloader 恢复源库数据表级tables和schemas（同一条语句）

因备份出来的是SQL执行语句，表级、库级和实例级对象的逻辑备份恢复数据命令是一致的，示例命令如下：

myloader -uroot -proot -h127.0.0.1 -P3306 -B schema1 -e -t 4 -d /data/backup/ --overwrite-tables -v 3

参数介绍：

-o, --overwrite-tables 若表存在则删除

-C, --compress-protocol 在 mysql 连接的时候使用压缩协议

-v, --verbose 输出日志的等级, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为 2

-t, --threads 恢复使用的线程数，默认是 4

-d, --directory 数据恢复的目录

-B, --database 需要恢复的数据库

-e, --enable-binlog 恢复数据的时候开启 binlog

（3）逻辑备份工具mysqlshell讲解

mysql shell是MySQL官方的一款逻辑备份工具，该工具中集成了备份函数。2020年7月，MySQL官方在mysqlshell 8.0.21中集成了实例和库级别的备份函数；2020年10月，8.0.22版本又发布了表级备份函数。至此8.0.22版本后的mysql shell能够实现对表、库和实例级别的备份函逻辑备份。MySQL数据库版本在5.7以上，推荐使用mysqlshell 8.0工具进行运维管理。

mysqlshell包含多种实用程序集（工具集），可使用mysqlsh -- util --help命令查看其工具集。mysqlshell主要使用三个备份工具（备份表dump-tables、备份库dump-schemas、备份进程dump-instance），以及恢复工具load-dump（恢复dump-tables/dump-schemas/dump-instance备份的数据）。

接下来通过命令讲解如何使用mysqlshell逻辑备份工具对表级、库级和实例级对象进行备份和恢复演练。

1.mysqlshell备份表级tables

mysqlsh root@127.0.0.1:3306 -- util dump-tables schema1 'table1,table2' --output-url='/data/backup/' --threads=4 --consistent=false --dryRun=true

2.mysqlshell备份schemas

mysqlsh root@127.0.0.1:3306 -- util dump-schemas 'schema1,schema2' --output-url='/data/backup/' --threads=4 --consistent=false --dryRun=true

3.mysqlshell备份instance

mysqlsh root@127.0.0.1:3306 -- util dump-instance '/data/backup/' --threads=4 --consistent=false --dryRun=true

参数介绍：

--util dump-tables：使用MySQL Shell的util工具，并选择dump-tables子命令来导出数据表。

--output-url='/data/backup/'：指定导出文件的输出路径和前缀。

--threads=4：指定使用的线程数。

--consistent=false：禁用一致性读取，即在导出期间不使用事务。

--dryRun=true：模拟导出操作，而不实际执行。

--consistent: [ true | false ] 是否开启一执性快照备份，默认true。开启时，通过flush tables with read lock获取全局读锁，每个备份线程设置会话隔离级别RR，开启一致性快照事务。当所有线程开启事务后，获取实例级备份锁（lock intance for backup），释放全局读锁。

4.mysqlshell 恢复表级tables

备份schema1库table1/table2的数据，恢复至schema2库。

mysqlsh root@127.0.0.1:3306 -- util load-dump '/data/backup/' --analyzeTables=on --skipBinlog=true --updateGtidSet=off --schema=schema2 --dryRun=false

5.恢复dump-schemas/dump-instance备份数据

mysqlsh root@127.0.0.1:3306 -- util load-dump '/data/backup/' --analyzeTables=on --skipBinlog=true --updateGtidSet=off --dryRun=true

参数介绍：

--dryRun=true：模拟导出操作，而不实际执行

--analyzeTables=on：在加载数据后对表进行分析

--skipBinlog=true：跳过写入二进制日志。

--updateGtidSet=off：禁用更新GTID。

文章至此。

以下是个人微信公众号，欢迎关注：