MySQL备份工具

Percona XtraBackup

下载

不能备份比 XtraBackup 版本高的数据库

1.安装依赖

yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev

2.安装

rpm -ivh percona-xtrabackup-80-8.0.33-28.1.el7.x86_64.rpm

3.使用

备份恢复过程分成3个阶段

1. 备份（backup）
2. 准备（prepare）
3. 恢复（restore）

全量备份

#备份
xtrabackup --backup --target-dir=/opt/mysqlbackup
#准备
xtrabackup --prepare --target-dir=/opt/mysqlbackup
#恢复
xtrabackup --copy-back --target-dir=/opt/mysqlbackup

或者直接将备份文件夹作为 datadir 启动 MySQL 实例

增量备份

增量备份（一次全量，两次增量）

#第一次全量
xtrabackup --backup --target-dir=/opt/mysqlbackup
#第一次增量（基于mysqlbackup）
xtrabackup --backup --target-dir=/opt/mysqlbackup1 --incremental-basedir=/opt/mysqlbackup
#第二次增量（基于mysqlbackup1）
xtrabackup --backup --target-dir=/opt/mysqlbackup2 --incremental-basedir=/opt/mysqlbackup1

#对全量备份集进行准备
xtrabackup --prepare --apply-log-only --target-dir=/opt/mysqlbackup
#将第一次的增量备份应用到全量备份上
xtrabackup --prepare --apply-log-only --target-dir=/opt/mysqlbackup --incremental-dir=/opt/mysqlbackup1
#将第二次的增量备份应用到全量备份上
xtrabackup --prepare --target-dir=/opt/mysqlbackup --incremental-dir=/opt/mysqlbackup2

#恢复
xtrabackup --copy-back --target-dir=/opt/mysqlbackup

--apply-log-only在最后一次应用增量备份时不用，恢复阶段和全量备份一样。

节流

#每次读写等待1倍的读写时间
xtrabackup --throttle=1

记录历史备份

使用--history指定备份名称 记录在数据库中的表percona_schema.xtrabackup_history中 增量备份可以使用--incremental-history-name或--incremental-history-uuid指定备份的基础

MySQL Enterprise Backup

下载

1.安装

rpm -ivh mysql-commercial-backup-8.0.34-1.1.el7.x86_64.rpm

2.使用

mysqlbackup --help 工具版本要和数据库版本匹配，否则报错。

全量备份

#command包括：
#backup：执行备份的初始阶段
#backup-and-apply-log：包括备份和应用备份过程中生成的日志两个阶段
mysqlbackup --backup_dir=/opt/mysqlbk command

#备份到单文件
mysqlbackup --backup_dir=/opt/mydir --backup-image=/opt/myimage backup-toimage
mysqlbackup --backup_dir=/u01/mydir2 --backup-image=- backup-toimage>/opt/myimage2

增量备份

#--incremental选项以执行增量备份
#--incremental-base选项将以前的备份指示为基础备份
#记录保存在 mysql.backup_history 表
mysqlbackup --incremental --incremental-base=history:last_backup --incrementalbackup_dir=/u01/inc1 backup

差异备份

#使用--incremental-base=history:last_full_backup指最后一次完整备份作为增量备份的基础，与增
量备份类似，只是基本备份始终是完全备份。
mysqlbackup --incremental --incremental-base=history:last_full_backup --incrementalbackup_dir=/u01/inc2 backup
#通过将backup命令替换为backup-to-image备份到单个文件，并指定--backup-image选项
# --start lsn 指定以前的完全备份中包含的最高LSN值
mysqlbackup --incremental --backup-image=/opt/myimage --incremental-base=history:last_full_backup --incrementalbackup_dir=/u01/inc2 backup-to-image

3.验证备份完整性

#验证备份映像
mysqlbackup --backup-image=/u01/myimage validate
#验证备份目录
mysqlbackup --backup-dir=/u01/inc1 validate

4.备份完整恢复

#关闭 MySQL 服务器，删除服务器数据目录中的所有文件
#如果备份时备份了日志使用 copy-back，否则使用 copy-back-and-apply-log
mysqlbackup --backup-dir=/u01/mydir copy-back
chown -R mysql. /var/lib/mysql

5.恢复增量备份

#在恢复增量备份或差异备份之前，必须先恢复正确的基础备份
#从增量备份目录进行恢复
mysqlbackup --incremental-backup-dir=incr-backup-dir --incremental copy-backand-apply-log
#从单个增量备份文件进行恢复
mysqlbackup --backup-dir=temp-backup-dir --backup-image=image-file --incremental copy-back-and-apply-log

6.更新操作

完全备份的目录备份可以使用以下方法进行更新： appy-log操作：

• mysqlbackup --backup-dir=backup-dir apply-log
• 使用backup命令进行的完全备份，再使用apply-log命令进行更新，然后使用copy-back命令进行恢复，而不是copy-back-and-apply-log命令。

mydumper

高性能多线程备份和恢复工具。

1.安装

下载地址

yum -y  install glib2-devel mysql-devel zlib-devel pcre-devel zlib gcc-c++ gcc cmake
wget https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz
tar zxf mydumper-0.9.1.tar.gz
cd mydumper-0.9.1/
cmake .
make
#默认生成mydumper和myloader到/usr/local/bin下
make install

mydumper 命令参数

-B, --database              要备份的数据库，不指定则备份所有库
-T, --tables-list           需要备份的表，名字用逗号隔开
-o, --outputdir             备份文件输出的目录
-s, --statement-size        生成的insert语句的字节数，默认1000000
-r, --rows                  将表按行分块时，指定的块行数，指定这个选项会关闭 --chunk-filesize
-F, --chunk-filesize        将表按大小分块时，指定的块大小，单位是 MB
-c, --compress              压缩输出文件
-e, --build-empty-files     如果表数据是空，还是产生一个空文件（默认无数据则只有表结构文件）
-x, --regex                 是同正则表达式匹配 'db.table'
-i, --ignore-engines        忽略的存储引擎，用都厚分割
-m, --no-schemas            不备份表结构
-k, --no-locks              不使用临时共享只读锁，使用这个选项会造成数据不一致
--less-locking              减少对InnoDB表的锁施加时间（这种模式的机制下文详解）
-l, --long-query-guard      设定阻塞备份的长查询超时时间，单位是秒，默认是60秒（超时后默认mydumper将会退出）
--kill-long-queries         杀掉长查询 (不退出)
-b, --binlogs               导出binlog
-D, --daemon                启用守护进程模式，守护进程模式以某个间隔不间断对数据库进行备份
-I, --snapshot-interval     dump快照间隔时间，默认60s，需要在daemon模式下
-L, --logfile               使用的日志文件名(mydumper所产生的日志), 默认使用标准输出
--tz-utc                    跨时区时使用
--skip-tz-utc               同上
--use-savepoints            使用savepoints来减少采集metadata所造成的锁时间，需要 SUPER 权限
--success-on-1146           Not increment error count and Warning instead of Critical in case of table doesn't exist
-h, --host                  连接的主机名
-u, --user                  备份所使用的用户
-p, --password              密码
-P, --port                  端口
-S, --socket                使用socket通信时的socket文件
-t, --threads               开启的备份线程数，默认是4
-C, --compress-protocol     压缩与mysql通信的数据
-V, --version               显示版本号
-v, --verbose               输出信息模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为 2

myloader 命令参数

-d, --directory                   备份文件的文件夹
-q, --queries-per-transaction     每次事物执行的查询数量，默认是1000
-o, --overwrite-tables            如果要恢复的表存在，则先drop掉该表，使用该参数，需要备份时候要备份表结构
-B, --database                    需要还原的数据库
-e, --enable-binlog               启用还原数据的二进制日志
-h, --host                        主机
-u, --user                        还原的用户
-p, --password                    密码
-P, --port                        端口
-S, --socket                      socket文件
-t, --threads                     还原所使用的线程数，默认是4
-C, --compress-protocol           压缩协议
-V, --version                     显示版本
-v, --verbose                     输出模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2

2.使用

#备份
mydumper -u root -p 123456  -P 3306 -h 127.0.0.1 -B mydb  -o /data/backup/mysql/
#目录中包含一个metadata文件。记录了备份数据库在备份时间点的二进制日志文件名，日志的写入位置，如果是在从库进行备份，还会记录备份时同步至主库的二进制日志文件及写入位置。

#恢复
myloader -u root -p 123456 -h 127.0.0.1 -B mydb -d /data/backup/mysql/

3.备份过程

1. 主线程 FLUSH TABLES WITH READ LOCK, 施加全局只读锁，保证数据的一致性
2. 读取当前时间点的二进制日志文件名和日志写入的位置并记录在metadata文件中，以供即使点恢复使用
3. N个（线程数可以指定，默认是4）dump线程把事务隔离级别改为可重复读 并开启读一致的事务
4. dump non-InnoDB tables, 首先导出非事物引擎的表
5. 主线程 UNLOCK TABLES 非事务引擎备份完后，释放全局只读锁
6. dump InnoDB tables, 基于事务导出InnoDB表
7. 事务结束

my2sql

生成被 delete 语句删除的数据的insert 语句。

限制：

1. binlog格式必须为row，且binlog_row_image=full（表示记录的二进制日志将包含所有列的值，包括旧值和新值）
2. 只能回滚DML，不能回滚DDL
3. 8.0 用户密码认证必须是mysql_native_password才能解析

1.安装

github地址，编译好的CentOS7版本文件地址 自己编译依赖 golang 环境

2.恢复

#在 binlog 中找到误操作开始和结束的位点
mysqlbinlog mysql-bin.000012 --start-datetime='2023-06-21 01:20:00'  --stop-datetime='2023-06-21 01:40:00'  --base64-output=decode-rows -v  > operation.sql
#生成回滚 SQL
./my2sql -user u_rollback -password 'IgdI8G_aUU' -host 127.0.0.1 -databases d_recover -tables del_t1 -work-type rollback -start-file mysql-bin.000012 -start-pos 3556 -stop-pos 3719 -output-dir recover_01
#应用回滚 SQL

apply-incremental-backup操作：

• mysqlbackup --incremental-backup-dir=incr-backup-dir --backup-dir=backup-dir apply-incremental-backup
• 备份目录中的完整备份可以使用，使用完整备份作为基础。
• 这可以加快恢复过程，因为它可以减少进行增量恢复次数。