MySQL8备份恢复（一）

MySQL8备份恢复（二）

MySQL8备份恢复（三）

MySQL8备份恢复（四）

MySQL8备份恢复（五）

锁定实例备份恢复

MySQL8.x 中新增了一个轻量级的备份锁,它允许在 online 备份的时候进行 DML 操作,同时可防止快照不一致. 这个锁禁止的操作很少,它禁止的操作包括:

1. 文件的创建, 删除, 改名 2. 账号的管理 3. REPAIR TABLE 4. TRUNCATE TABLE 5. OPTIMIZE TABLE

长查询对 flush tables with read lock 是有影响的,整个系统会 hung 住, 这时其它的数据库查询也不能用, 包括 use database

而 lock instance for backup 没有这个问题. Oracle自己的备份产品 (MySQL Enterprise Backup 8) 就用这个锁, Percona 的 Xtrabackup 在 8 的版本也是用这个锁

percona 自己的 MySQL 也有一种轻量级的备份锁 lock table for backup 也是一种比 flush tables with read lock 要轻量级的锁, 它不会刷新表, 即存储引擎不会强制关闭表, 并且表不会从表缓存中排出. 因此, LOCK TABLES FOR BACKUP 仅等待冲突的语句完成, 它不会等待 SELECT 或更新 InnoDB 表来完成

#开始备份之前,请锁定需要备份的实例:
mysql> LOCK INSTANCE FOR BACKUP;
#执行备份
rsync -av /var/lib/mysql /backups
#完成后解锁实例:
mysq1> UNLOCK INSTANCE;

测试结果: 锁定后可以 DML,但是会阻塞 EVENT事件

二进制日志备份

1.在服务器上创建一个复制用户

CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '********';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
flush privileges;

2.检查服务器上的二进制日志:

SHOW BINARY LOGS ;

在服务器上找到第一个可用的二进制日志,可以从这里开始备份. 例如 binlog.000005

3.登录到备份服务器并执行以下命令,会将二进制日志从MySQL服务器复制到备份服务器. 你可以使用 nohup 或 disown

mysqlbinlog -h 192.168.55.44 -u repl -p******** --read-from-remote-server --stop-never --to-last-log --raw binlog.000005 &
shell> disown -a #disown效果相当于忘记输入 nohup, 同样的还有 setsid

4.验证是否正在备份二进制日志

ls -ltr binlog.*

MySQL Shell备份恢复

一 说明

• MySQL Shell 介绍

MySQL Shell 是 MySQL 的一个高级客户端和代码编辑器, 是第二代 MySQL 客户端. 第一代 MySQL 客户端即我们常用的 mysql

• MySQL Shell 关键特性

支持 Python 和 JavaScript 两种语言模式. 基于此, 我们可以很容易地进行一些脚本开发工作

支持 AdminAPI. AdminAPI 可用来管理 InnoDB Cluster、InnoDB ClusterSet 和 InnoDB ReplicaSet

支持 X DevAPI. X DevAPI 可对文档( Document )和表( Table )进行 CRUD(Create, Read, Update, Delete)操作

• MySQL Shell 核心优势

1. 并行 Parallelization 默认4个线程进行导出或者导入数据, 可根据情况自己定义

2. 分块 Chunking 默认开启, 将每个表数据分成大小相等的数据文件, 默认64兆一个文件, 由参数bytesPerChunk控制, 最小128k（针对那些小表, 小于64兆的表）

3. 压缩 Compression

默认开启压缩, 压缩类型为时下最新的zstd压缩, 另外还支持gzip压缩, zstd在压缩和解压之间获得了一个折中的平衡, 相对比gzip压缩率高的情况下还要比gzip解压时间更短.compression：zstd 默认开启, 不用设置

4. 输出格式 Output format

MySQL Shell Utilities工具默认使用load data语句预期的默认格式进行数据导出和导入, 这会让输出文件更小, 同时导入速度也更加快速.

mysqldump, msqlpump,mydumper这三类导出工具都是以insert语句的形式导出数据, 这在速度上明显要落后于load data语句, 官方文档中描述load data方式要比insert方式快20倍

• MySQL Shell 使用时注意

通过 dumpInstance, dumpSchemas, dumpTables 生成的备份只能通过 loadDump 来恢复

通过 exportTable 生成的备份只能通过 importTable 来恢复

使用限制: MySQL Shell工具目前在数据导出导入时,目标库的版本只适用于MySQL5.7和8.0版本,不适用低于5.7的版本

MySQL Shell 的安装

yum install mysql-shell-commercial-8.0.31-1.1.el8.x86_64.rpm
#登录
mysqlsh --uri root@127.0.0.1:3308/mysql

二 例子

1. 备份例子

util.dumpInstance('/backup/full',{compression: "none"})
Acquiring global read lock
Global read lock acquired
Initializing - done
1 out of 5 schemas will be dumped and within them 8 tables, 0 views.
3 out of 6 users will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
124% (198.22K rows / ~158.99K rows), 133.81K rows/s, 0.00 B/s
Dump duration: 00:00:00s
Total duration: 00:00:01s
Schemas dumped: 1
Tables dumped: 8
Data size: 598.90 MB
Rows written: 198221
Bytes written: 598.90 MB
Average throughput: 598.90 MB/s

2. 备份产生的内容

@.done.json：会记录备份的结束时间, 备份集的大小. 备份结束时生成

@.json：会记录备份的一些元数据信息, 包括备份时的一致性位置点信息：binlogFile, binlogPosition 和 gtidExecuted, 这些信息可用来建立复制

@.sql, @.post.sql：这两个文件只有一些注释信息. 在通过 util.loadDump 导入数据时, 我们可以通过这两个文件自定义一些 SQL. 其中, @.sql 是数据导入前执行, @.post.sql 是数据导入后执行

*.tsv：数据文件. 我们看看数据文件的内容

TEST@a_news.json：记录了表相关的一些元数据信息, 如列名, 字段之间的分隔符(fieldsTerminatedBy)等

TEST@a_news.sql：建表语句

TEST.json：记录数据库中已经备份的表、视图、定时器、函数和存储过程

TEST.sql：建库语句. 如果这个库中存在存储过程、函数、定时器, 也是写到这个文件中

@.users.sql：创建账号及授权语句. 默认不会备份 mysql.session, mysql.session, mysql.sys 这三个内部账号

3. 恢复例子

util.loadDump("/backup/full")
Loading DDL and Data from '/backup/full' using 4 threads.
Opening dump...
Target is MySQL 8.0.31. Dump was produced from MySQL 8.0.31
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
1 thds loading  100% (598.90 MB / 598.90 MB), 20.59 MB/s, 7 / 8 tables done
Executing common postamble SQL
Recreating indexes - done
23 chunks (198.22K rows, 598.90 MB) for 8 tables in 1 schemas were loaded in 50 sec (avg throughput 12.13 MB/s)
0 warnings were reported during the load.

4. 恢复产生的内容

load-progress..progress: 在导入的过程中, 会在备份目录生成一个 progressFile, 用于记录加载过程中的进度信息, 可用来实现断点续传功能

三 命令说明

1. 备份命令

1) 备份实例 其中, outputUrl 是备份目录, 其必须为空. options 是可指定的选项

util.dumpInstance(outputUrl[, options])

2) 备份指定库的数据. 第一个参数必须为数组, 如 util.dumpSchemas(['TEST'],'/backup/schema') 从 MySQL Shell 8.0.28 开始, 可直接使用 util.dumpInstance 中的 includeSchemas 选项进行指定库的备份

util.dumpSchemas(schemas, outputUrl[, options])

3) 备份指定表的数据. 第二个参数必须为数组, 如 util.dumpTables('TEST',['t'],'/backup/table') 从 MySQL Shell 8.0.28 开始, 可直接使用 util.dumpInstance 中的 includeTables 选项进行指定表的备份

util.dumpTables(schema, tables, outputUrl[, options])

2. 恢复命令

util.loadDump(url[, options])

3. 不进入mysqlsh, 操作系统命令行执行备份

mysqlsh --uri root@127.0.0.1:3308/mysql -e "util.dumpInstance('/backup/full1')"

四 特性说明

1. util.dumpInstance 的关键特性

多线程备份. 并发线程数由 threads 决定, 默认是 4

支持单表 chunk 级别的并行备份, 前提是表上存在主键或唯一索引

默认是压缩备份

支持备份限速. 可通过 maxRate 限制单个线程的数据读取速率

util.loadDump 的关键特性

2. util.loadDump 的关键特性如下：

多线程恢复. 并发线程数由 threads 决定, 默认是 4

支持断点续传功能

在导入的过程中, 会在备份目录生成一个进度文件, 用于记录导入过程中的进度信息

文件名由 progressFile 指定, 默认是 load-progress..progress

导入时, 如果备份目录中存在 progressFile, 默认会从上次完成的地方继续执行. 如果要从头开始执行, 需将 resetProgress 设置为 true

支持延迟创建二级索引

支持边备份, 边导入

通过 LOAD DATA LOCAL INFILE 命令来导入数据

如果单个文件过大, util.loadDump 在导入时会自动进行切割, 以避免产生大事务

3. util.dumpInstance 与 mysqldump 不同的地方

util.dumpInstance 会加备份锁. 备份锁可用来阻塞备份过程中的 DDL

util.dumpInstance 是并行备份, 相对于 mysqldump 的单线程备份, 备份效率更高

五 参数说明

1. util.dumpInstance 的参数解析

过滤相关的选项:

excludeSchemas：忽略某些库的备份, 多个库之间用逗号隔开, 如, excludeSchemas: ["db1", "db2"]

includeSchemas：指定某些库的备份

excludeTables：忽略某些表的备份, 表必须是 schema.table 的格式, 多个表之间用逗号隔开, 如, excludeTables: ["sbtest.sbtest1", "sbtest.sbtest2"]

includeTables：指定某些表的备份

events：是否备份定时器, 默认为 true

excludeEvents：忽略某些定时器的备份

includeEvents：指定某些定时器的备份

routines：是否备份函数和存储过程, 默认为 true

excludeRoutines：忽略某些函数和存储过程的备份

includeRoutines：指定某些函数和存储过程的备份

users：是否备份账号信息, 默认为 true

excludeUsers：忽略某些账号的备份, 可指定多个账号

includeUsers：指定某些账号的备份, 可指定多个账号

triggers：是否备份触发器, 默认为 true

excludeTriggers：忽略某些触发器的备份

includeTriggers：指定某些触发器的备份

ddlOnly：是否只备份表结构, 默认为 false

dataOnly：是否只备份数据, 默认为 false

并行备份相关的选项

chunking：是否开启 chunk 级别的并行备份功能, 默认为 true

bytesPerChunk：每个 chunk 文件的大小, 默认 64M

threads：并发线程数, 默认为 4

OCI(甲骨文云)相关:

ocimds：是否检查备份集与甲骨文云的 MySQL 云服务 MySQL Database Service(简称 MDS )的兼容性, 默认为 false, 不检查. 如果设置为 true, 会输出所有的不兼容项及解决方法. 不兼容项可通过下面的 compatibility 来解决.

compatibility：如果要将备份数据导入到 MDS 中, 为了保证与后者的兼容性, 可在导出的过程中进行相应地调整. 具体来说：

1) create_invisible_pks：对于没有主键的表, 会创建一个隐藏主键：my_row_id BIGINT UNSIGNED AUTO_INCREMENT INVISIBLE PRIMARY KEY. 隐藏列是 MySQL 8.0.23 引入的

2) force_innodb：将表的引擎强制设置为 InnoDB

3) ignore_missing_pks：忽略主键缺失导致的错误, 与 create_invisible_pks 互斥, 不能同时指定

4) skip_invalid_accounts：忽略没有密码, 或者使用了 MDS 不支持的认证插件的账号

5) strip_definers：去掉视图、存储过程、函数、定时器、触发器中的 DEFINER=account 子句

6) strip_restricted_grants：去掉 MDS 中不允许 GRANT 的权限

7) strip_tablespaces：去掉建表语句中的 TABLESPACE=xxx 子句

osBucketName, osNamespace, ociConfigFile, ociProfile, ociParManifest, ociParExpireTime：OCI 对象存储相关

其它选项:

tzUtc：是否设置 TIME_ZONE = '+00:00', 默认为 true

consistent：是否开启一致性备份, 默认为 true. 若设置为 false, 则不会加全局读锁, 也不会开启事务的一致性快照

dryRun：试运行. 此时只会打印备份信息, 不会执行备份操作

maxRate：限制单个线程的数据读取速率, 单位 byte, 默认为 0, 不限制

showProgress：是否打印进度信息, 如果是 TTY 设备(命令行终端), 则为 true, 反之, 则为 false

defaultCharacterSet：字符集, 默认为 utf8mb4

compression：备份文件的压缩算法, 默认为 zstd. 也可设置为 gzip 或 none(不压缩)

2. util.loadDump 的参数解析

过滤相关:

excludeEvents：忽略某些定时器的导入

excludeRoutines：忽略某些函数和存储过程的导入

excludeSchemas：忽略某些库的导入

excludeTables：忽略某些表的导入

excludeTriggers：忽略某些触发器的导入

excludeUsers：忽略某些账号的导入

includeEvents：导入指定定时器

includeRoutines：导入指定函数和存储过程

includeSchemas：导入指定库

includeTables：导入指定表

includeTriggers：导入指定触发器

includeUsers：导入指定账号

loadData：是否导入数据, 默认为 true

loadDdl：是否导入 DDL 语句, 默认为 true

loadUsers：是否导入账号, 默认为 false. 注意, 即使将 loadUsers 设置为 true, 也不会导入当前正在执行导入操作的用户

ignoreExistingObjects：是否忽略已经存在的对象, 默认为 off

并行导入相关:

backgroundThreads：获取元数据和 DDL 文件内容的线程数. 备份集如果存储在本地, backgroundThreads 默认和 threads 一致

threads：并发线程数, 默认为 4

maxBytesPerTransaction：指定单个 LOAD DATA 操作可加载的最大字节数. 默认与 bytesPerChunk 一致. 这个参数可用来规避大事务

断点续传相关:

progressFile：在导入的过程中, 会在备份目录生成一个 progressFile, 用于记录加载过程中的进度信息, 这个进度信息可用来实现断点续传功能. 默认为 load-progress..progress

resetProgress：如果备份目录中存在 progressFile, 默认会从上次完成的地方继续执行. 如果要从头开始执行, 需将 resetProgress 设置为 true. 该参数默认为 off

OCI 相关:

osBucketName, osNamespace, ociConfigFile, ociProfile

二级索引相关:

deferTableIndexes：是否延迟(数据加载完毕后)创建二级索引. 可设置：off(不延迟), fulltext(只延迟创建全文索引, 默认值), all(延迟创建所有索引)

loadIndexes：与 deferTableIndexes 一起使用, 用来决定数据加载完毕后, 最后的二级索引是否创建, 默认为 true

其它选项:

analyzeTables：表加载完毕后, 是否执行 ANALYZE TABLE 操作. 默认是 off(不执行), 也可设置为 on 或 histogram(只对有直方图信息的表执行)

characterSet：字符集, 无需显式设置, 默认会从备份集中获取

createInvisiblePKs：是否创建隐式主键, 默认从备份集中获取. 这个与备份时是否指定了 create_invisible_pks 有关, 若指定了则为 true, 反之为 false

dryRun：试运行

ignoreVersion：忽略 MySQL 的版本检测. 默认情况下, 要求备份实例和导入实例的大版本一致

schema：将表导入到指定 schema 中, 适用于通过 util.dumpTables 创建的备份

showMetadata：导入时是否打印一致性备份时的位置点信息

showProgress：是否打印进度信息

skipBinlog：是否设置 sql_log_bin=0 , 默认 false. 这一点与 mysqldump(开启 GTID 的情况下)、mydumper 不同, 后面这两个工具默认会禁用 Binlog

updateGtidSet：更新 GTID_PURGED. 可设置：off(不更新, 默认值), replace(替代目标实例的 GTID_PURGED), append(追加)

waitDumpTimeout：util.loadDump 可导入当前正在备份的备份集. 处理完所有文件后, 如果备份还没有结束(具体来说, 是备份集中没有生成 @.done.json), util.loadDump 会报错退出, 可指定 waitDumpTimeout 等待一段时间, 单位秒

六 注意事项

1. 表上存在主键或唯一索引才能进行 chunk 级别的并行备份. 字段的数据类型不限. 不像 mydumper, 分片键只能是整数类型

2. 对于不能进行并行备份的表, 目前会备份到一个文件中. 如果该文件过大, 不用担心大事务的问题, util.loadDump 在导入时会自动进行切割

3. util.dumpInstance 只能保证 InnoDB 表的备份一致性

4. 默认不会备份 information_schema, mysql, ndbinfo, performance_schema, sys

5. 备份实例支持 MySQL 5.6 及以上版本, 导入实例支持 MySQL 5.7 及以上版本

6. 备份的过程中, 会将 BLOB 等非文本安全的列转换为 Base64, 由此会导致转换后的数据大小超过原数据. 导入时, 注意 max_allowed_packet 的限制

7. 导入之前, 需将目标实例的 local_infile 设置为 ON

速度比较

数据库 73G :

mysqldump: 34 分钟输出10G ,

myshell dump线程4(默认): 30 分钟 输出 10G,

myshell dump线程8: 5 分钟 输出 10G,

myshell dump线程12: 5 分钟 输出10G,

xtrabackup: 34 分钟输出73G