使用 MySQL Shell 备份MySQL数据库

╰半橙微兮° 2024-03-25 17:28 46阅读 0赞

### 一 说明 ###

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][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][TEST_a_news.json]：记录了表相关的一些元数据信息, 如列名, 字段之间的分隔符(fieldsTerminatedBy)等

[TEST@a\_news.sql][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][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

[root_127.0.0.1]: mailto:root@127.0.0.1
[TEST_a_news.json]: mailto:TEST@a_news.json
[TEST_a_news.sql]: mailto:TEST@a_news.sql