MySQL8备份恢复（二）

客官°小女子只卖身不卖艺 2024-03-24 13:58 64阅读 0赞

接上次 ，这次整理了下 mysqlpump 和 mydumper备份恢复。

这两个工具我也只是测了一下备份和恢复， 平时也不怎么用， 具体描述，用途， 优缺点大家可以自行查一下。

我把我的备份恢复步骤贴一下。

### mysqlpump备份恢复 ###

#### 并行处理 ####

可以通过指定线程数量 (根据CPU数量) 加速备份过程. 例如,使用8个线程进行完整备份

mysqlpump --default-parallelism=8 > full_backup.sql

甚至可以指定每个数据库的线程数. 在我们的案例中, employees 数据库比 company 数据库大很多. 所以可以为 employees 数据库指定4个线程,为 company 数据库指定2个线程

mysqlpump -u root --password --parallel-schemas=4:employees --default-parallelism=2 > full_backup.sql

3个线程用于 db1 和 db2 数据库,2个线程用于 db3 和 db4 数据库, 还有4个线程用于其他数据库

mysqlpump --parallel-schemas=3:db1,db2 --parallel-schemas=2:db3,db4 --default-parallelism=4 > full_backup.sql

#### 使用正则表达式排除/包含数据库对象 ####

对以 prod 结尾的所有数据库进行备份

mysqlpump --include-databases=%prod --result-file=db_prod.sql

假设某些数据库中有一些测试表,我们希望将它们从备份中排除. 可以使用 --exclude-tables 选项来指定,该选项将排除所有数据库中名称为 test 的表:

mysqlpump --exclude-tables=test --result-file=backup_excluding_test.sql

#### 备份用户 ####

在 mysqldump 中,你不会在 CREATE USER 或 GRANT 语句中获得用户的备份; 相反,你必须备份 mysql.user 表. 使用 mysqlpump 可以将用户账号备份为账号管理语句( CREATE USER 和 GRANT ),而不是将用户账号插入 mysql 系统数据库中

mysqlpump --exclude-databases=% --users > users_backup.sql

还可以通过指定 --exclude-users 选项来排除某些用户:

mysqlpump --exclude-databases=% --exclude-users=root --users >users_backup.sql

#### 压缩备份 ####

可以通过压缩备份来减少磁盘空间和网络带宽的占用. 可以使用 --compress-output = lz4 或 --compress-output = zlib 请注意,你需要有相应的解压缩工具

mysqlpump -u root -pxxxx --compress-output=lz4 > dump.lz4

执行下面的语句进行解压缩:

lz4_decompress dump.lz4 dump.sql

使用zlib执行此下面的语句:

mysqlpump -u root -pxxxx --compress-output=zlib > dump.zlib

执行下面的语句进行解压缩:

zlib_decompress dump.zlib dump.sql

#### 加速重新加载 ####

你会注意到, 在输出中, 辅助索引从 CREATETABLE 语句中省略了. 这将加速恢复过程. 我们将使用 ALTER TABLE 语句在 INSERT 结尾处添加这些索引

以前我们可以 dump mysql 系统数据库中的所有表. 从 MySQL8 开始,mysqldump 和 mysqlpump 仅能 dump 该数据库中的非数据字典表

#### 备份 ####

mysqlpump -h 192.168.55.44 -u root -p******** --single-transaction --master-data=2 -B TEST> test.sql

#### 恢复 ####

mysqlpump 无法获取当前备份对应的 binlog 位置, 不保持一致性, 所以基于时间点的恢复无法测试

mysql -h 192.168.55.44 -u root -p******** TEST < ./test.sql

### mydumper备份恢复 ###

#### 安装mydumper ####

yum install -y "https://github.com/maxbube/mydumper/releases/download/v0.9.5/mydumper-0.9.5-2.el6.x86_64.rpm"  #YUM
    #APT
    wget "https://github.com/maxbube/mydumper/releases/download/v0.9.5/mydumper_0.9.5-2.jessie_amd64.deb"
    dpkg -i mydumper_0.9.5-2.jessie_amd64.deb
    apt-get install -f

#### mydumper参数 ####

可以使用 mydumper --help查看说明和默认值  
参数 说明

> \-h, --host=name 主机名  
> \-u, --user=name 账号  
> \-p, --password\[=name\] 密码  
> \-B, --database 要导出的数据库  
> \-T, --tables-list 要导出的表  
> \-o, --outputdir 导出的目录  
> \-G, --triggers 备份触发器  
> \-E, --events 备份事件调度器  
> \-R, --routines 备份存储过程和函数  
> \-d, --no-data 不导出数据  
> \-r, --rows 将表分成这些行的块,每个块将被写人一个单独的文件中  
> \-t, --threads 线程, 默认4  
> –trx-consistency-only 如果只使用事务表,例如 InnoDB 那么使用此选项将使锁定最小化. 相当于 mysqldump 的 --single-transaction, 但具有 binlog 位置. 显然, 这个位置只适用于事务表.  
> 使用这个选项的好处之一是全局读锁只保持在线程的协调过程中,所以-一旦事务开始,该选项就会被释放  
> \-K, --kill-long-queries 终止阻塞查询,而不是中止mydumper  
> –use-savepoints 减少元数据锁定问题(需要SUPER权限)  
> \-c, --compress 压缩备份  
> \-m, --no-schemas 跳过 schema 并且仅备份数据

#### mydumper备份 ####

#完全备份
    mydumper -h 192.168.55.44 -u root -p ******** -o /backups
    #备份库
    mydumper -h 192.168.55.44 -u root -p ******** -B TEST -o /backups
    #备份单独表
    mydumper -h 192.168.55.44 -u root -p ******** -B TEST -T T1 -GER -o /backups  #G:触发器 E:事件 R:存储过程和函数
    #压缩备份
    mydumper -h 192.168.55.44 -u root -p ******** -B TEST -T T1 -c --trx-consistency-only -o /backups/T1_compress  #c:压缩备份
    #仅备份数据
    mydumper -h 192.168.55.44 -u root -p ******** -B TEST -T T1 -m -c --trx-consistency-only -o /backups/T1_data #m:不包含table schema
    #使用正则表达式来备份特定的数据库
    可以使用regex选项包含/排除特定数据库. 以下命令将从备份中排除mysql和test数据库
    mydumper -h 192.168.55.44 -u root -p ******** --regex '^(?! (mysql|test))' -o /backups
    #分成小块备份大表 对于每一个块, 都会创建一个名为<DB>.<TABLE>.<number>.sql的文件,其中数字用零填充,补足5位
    mydumper -h 192.168.55.44 -u root -p ******** -B TEST -T T1 -GER --rows=10000 --trx-consistency-only -o /backups/t1_table_chunks
    
    #无阻塞备份
    为了提供一致的备份, mydumper 通过执行 FLUSH TABLES WITH READ LOCK 来获取 GLOBAL LOCK
    我们已经在第5章中介绍过,如果有任何长时间运行的事务,使用 FLUSH TABLES WITH READ LOCK 是多么危险. 为了避免这种情况, 可以传递 --kill-long-queries 选项来终止阻塞查询,而不是中止 mydumper
    #一致备份
    备份目录中的元数据文件包含用于一致备份的二进制日志坐标
    在主服务器上, 备份目录中的元数据文件 metadata 会捕获二进制日志位置
    在从服务器上, 备份目录中的元数据文件 metadata 会捕获主服务器和从服务器的二进制日志位置
    #文件的命名规则
    <DB>-schema-create.sql  包含 CREATE DATABASE 语句
    <DB>.<TABLE>-schema.sql 包含 CREATE TABLE 语句
    <DB>-schema-post.sql    包含 ROUTINES, TRIGGERS 和 EVENTS
    <DB>.<TABLE>.sql        包含 INSERT 语句形式表示的实际数据

#### myloader参数 ####

可以使用 myloader --help 查看说明和默认值

参数 说明

> \-d, --directory 指定要导入的转储目录  
> \-q, --queries-per-transaction 指定每个事务的查询数量,默认值是1000  
> \-o, --overwrite-tables 这个选项会删除已经存在的表  
> \-t, --threads 指定要使用的线程数量,默认值是4  
> \-C, --compress-protocol 该选项在MySQL连接上使用压缩  
> \-s, --source-db 仅恢复单个数据库

#### myloader恢复 ####

#恢复完整的数据库
    myloader -h 192.168.55.44 -u root -p ******** -d /backups -o
    #恢复单个数据库
    myloader -h 192.168.55.44 -u root -p ******** -d /backups -s TEST -o
    #恢复单个表
    mydumper 将每个表的备份写人单独的 .sql 文件. 你可以选择这个 .sql 文件并恢复
    mysql -h 192.168.55.44 -u root -p ******** -A -f < TEST.T1.sql
    如果这个表被拆分为 chunk (块), 则可以将与这个表相关的所有 chunk 和信息复制到一个目录并指定其位置
    复制所需的文件:
    cp /backups/t1_table_chunks/TEST.T1.* \
    /backups/t1_table_chunks/TEST.T1-schema.sql \
    /backups/t1_table_chunks/TEST-schema-create.sql \
    /backups/t1_table_chunks/metadata \
    /backups/single_table/
    使用 myloader 加载, 它会自动检测 chunk 并加载它们
    myloader -h 192.168.55.44 -u root -p ******** -d /backups/sing1e_table/ -o

未完待续， 后面还有 使用文件copy方式备份, xtrabackup, mysqlbackup方式备份， mysqlshell备份等几种， 下周再更新。