使用 MySQL Shell 备份MySQL数据库_mysql-shell备份

一 说明

1. MySQL Shell 介绍

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

2. MySQL Shell 备份功能

在之前，我们备份MySQL方法有: mysqldump , mypump,mydumper, xtrabackup , mysqlbackup

mysqldump , mypump,mydumper都是逻辑备份， mysqldump可以保证一致性但速度比较慢不能并行， mypump无法获取当前备份对应的binlog位置。基于表进行并行备份,对于大表来说性能较差。mydumper可定制性差

mysqlbackup和xtrabackup 是物理备份, 前者是官方收费软件, 后者是开源软件, 功能雷同但又有差异，各有亮点. 例如mysqlbackup支持的特性多一点, 但是是收费软件. xtrabackup 免费但特性少一点，例如仅支持linux平台

我们最常用的基本上就是mysqldump, 对于不是太大的数据库还是很好用的, 但数据量稍微一上去, 备份时间就很长了.

现在有了MySQL Shell 可以大大解决这个问题

3. MySQL Shell 的安装(以 8.0.31 为例)

yum install mysql-shell-commercial-8.0.31-1.1.el9.x86_64.rpm

4. 登录数据库

可以通过 mysqlsh --help 查看到有很多种连接方法:

$ mysqlsh root@localhost/schema
$ mysqlsh mysqlx://root@some.server:3307/world_x
$ mysqlsh --uri root@localhost --py -f sample.py sample param
$ mysqlsh root@targethost:33070 -s world_x -f sample.js
$ mysqlsh -- util check-for-server-upgrade root@localhost --output-format=JSON
$ mysqlsh mysqlx://user@host/db --import ~/products.json shop

我们此处使用 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：这两个文件只有一些注释信息. 导入数据时, 我们可以通过这两个文件自定义一些 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) 备份实例 其中

util.dumpInstance(outputUrl[, options])

2) 备份指定库的数据. 第一个参数必须为数组, 如 util.dumpSchemas(['TEST'],'/backup/schema')

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

3) 备份指定表的数据. 第二个参数必须为数组, 如 util.dumpTables('TEST',['t'],'/backup/table')

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')"

四 备份脚本

例如数据库有 TEST1，TEST2 数据库，我比较喜欢每个数据库分别备份, 每天备份，自动覆盖上周的备份， 可以编写以下脚本并加入计划任务

#!/bin/bash
for v_db in TEST1 TEST2
do
  v_target=/backup/full/$(date +%w)/${v_db}
  [ -d ${v_target} ] && rm -rf ${v_target}
  [ -d ${v_target} ] || mkdir -p ${v_target}
  mysqlsh --uri root@127.0.0.1:3308/mysql -e "util.dumpInstance('${v_target}')"
done