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 默认开启, 不用设置

  1. 输出格式 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.<server_uuid>.progress: 在导入的过程中, 会在备份目录生成一个 progressFile, 用于记录加载过程中的进度信息, 可用来实现断点续传功能

三 命令说明

1. 备份命令

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

util.dumpInstance(outputUrl[, options])

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

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

  1. 备份指定表的数据. 第二个参数必须为数组, 如 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 的关键特性

  1. util.loadDump 的关键特性如下:

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

支持断点续传功能

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

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

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

支持延迟创建二级索引

支持边备份, 边导入

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

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

  1. 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(不压缩)

  1. 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.<server_uuid>.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

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

贺浦力特

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值