本文基于mysql(8.0.20)及xtrabackup(8.0.13)最新版本,实现了完整的mysqldump逻辑备份、binlog增量备份、xtrabackup物理备份恢复,帮你快速掌握操作要点
TOC
本文相关环境均基于docker实现,下面是启动一个最基本的mysql数据库:
docker run --name mysql-dump-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=admin -e MYSQL_DATABASE=user -d mysql
在开展备份之前,需要先准备如下的测试数据:
create table if not exists t_user1
(
id bigint(20) auto_increment primary key comment '主键',
name varchar(64) comment '用户名称',
birth_date timestamp comment '生日',
assert decimal(10, 2) comment '资产'
) engine = InnoDB
charset utf8mb4 comment '用户表';
insert into t_user1(name, birth_date, assert)
values ('zhangsan', now(), 1123000.99);
insert into t_user1(name, birth_date, assert)
values ('lisi', now(), 1159000.99);
insert into t_user1(name, birth_date, assert)
values ('wangwu', now(), 12341234.00);
如上,创建一张测试表,并插入3条测试数据。
全量备份基本模式:
主要命令如下:
# 级联创建数据备份目录
mkdir -p /data/backups/dmp
# 实现所有数据库备份
mysqldump --opt --single-transaction --master-data=2 --host=localhost --user=root --password=admin --all-databases > /data/backups/dmp/dmp1.sql
mysqldump相关参数说明:
通过在上述库中,执行drop table t_user1
,删除该表后,开展恢复:
mysql -h localhost -u root -p < /data/backups/dmp/dmp1.sql
1、导出指定数据库的指定表:
mysqldump --opt --single-transaction --master-data=2 --host=localhost --user=root -p --databases user --tables t_user1 > /data/backups/dmp/dmp2.sql
2、只导出建表语句:
mysqldump --host=localhost --user=root -p --databases user --tables t_user1 --no-data > /data/backups/dmp/dmp3.sql
3、条件备份:
mysqldump --single-transaction --no-create-db --no-create-info --default-character-set=utf8 --host=localhost --user=root --password=admin --databases user --tables t_user1 --where="id >=3" > /data/backups/dmp/dmp4.sql
当前xtrabackup的8.0.13已经支持 mysql 8.0.20版本(8.0.20版本对innodb的数据文件模式进行了修改)
与mysql环境一样,需要将xtrabackup安装到mysql容器中:
percona-xtrabackup-80_8.0.13-1.buster_amd64.deb
/etc/apt/sources.list
内容替换为国内镜像,例如阿里云的。原/etc/apt/sources.list文件内容如下:
# deb http://snapshot.debian.org/archive/debian/20200422T000000Z buster main
deb http://deb.debian.org/debian buster main
# deb http://snapshot.debian.org/archive/debian-security/20200422T000000Z buster/updates main
deb http://security.debian.org/debian-security buster/updates main
# deb http://snapshot.debian.org/archive/debian/20200422T000000Z buster-updates main
deb http://deb.debian.org/debian buster-updates main
将内容全部替换为:
deb http://mirrors.aliyun.com/debian/ buster main non-free contrib
deb http://mirrors.aliyun.com/debian-security buster/updates main
deb http://mirrors.aliyun.com/debian/ buster-updates main non-free contrib
deb http://mirrors.aliyun.com/debian/ buster-backports main non-free contribb
具体命令操作:
# 将容器中的文件拷贝出来
docker cp mysql-dump-test:/etc/apt/sources.list D:\dev2\test\mysqldump
# 将修改后的文件覆盖回容器中
docker cp D:\dev2\test\mysqldump\sources.list mysql-dump-test:/etc/apt/
# 进入容器以后,执行下面命令更新apt信息
apt-get update
完成上述准备工作以后,即可开始安装xtrabackup:
具体命令操作如下:
# 将下载文件拷贝到容器中
docker cp D:\dev2\test\mysqldump\percona-xtrabackup-80_8.0.13-1.buster_amd64.deb mysql-dump-test:/data
# 执行第一次安装,此时会出现错误提示
dpkg -i percona-xtrabackup-80_8.0.13-1.buster_amd64.deb
# 执行libev4 安装,也会出错
apt install libev4
# 执行相关依赖安装
apt --fix-broken install
# 再次执行,完成安装
dpkg -i percona-xtrabackup-80_8.0.13-1.buster_amd64.deb
安装成功后,即可测试全量备份:
具体命令操作如下:
# 启动全量备份
xtrabackup --backup --target-dir=/data/backups/base1 --user=root --password=admin
# 通过执行drop table t_user1来模拟误操作
# 准备全量恢复
xtrabackup --prepare --target-dir=/data/backups/base1
# 将备份文件同步到mysql数据文件目录中
rsync -avrP /data/backups/base1/ /var/lib/mysql/
# 退出容器后,执行容器重启,完成恢复
docker restart mysql-dump-test
同时,上述rsync也可以用如下命令替代,但需要保证datadir(也即/var/lib/mysql/)是空的:
xtrabackup --copy-back --target-dir=/data/backups/base1
增量备份及恢复过程如下:
备份具体操作:
# 全量备份
xtrabackup --backup --target-dir=/data/backups/base2 --user=root --password=admin
# 第一次增量备份
xtrabackup --backup --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/base2 --user=root --password=admin
# 第二次增量备份
xtrabackup --backup --target-dir=/data/backups/inc2 --incremental-basedir=/data/backups/inc1 --user=root --password=admin
恢复具体操作:
# 先恢复全量备份
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base2
# 逐项恢复增量备份
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base2 --incremental-dir=/data/backups/inc1
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base2 --incremental-dir=/data/backups/inc2
# 将恢复文件进行同步
rsync -avrP /data/backups/base2/ /var/lib/mysql/
# 退出容器后,执行容器重启,完成恢复
docker restart mysql-dump-test
binlog增量备份及恢复主要过程:
先执行全量备份:
# 执行全量备份
mysqldump --opt --single-transaction --master-data=2 --flush-logs --host=localhost --user=root --password=admin --databases user > /data/backups/dmp/dmp3.sql
下面是模拟误操作(新增一条记录后,删除数据表):
insert into t_user1(name, birth_date, assert)
values ('maliu', now(), 12341234.00);
drop table t_user1;
接下来是具体binlog定位及分析过程:
1、通过show variables like 'log_bin%'
命令,查看binlog路径,具体输出如下:
Variable_name,Value
log_bin,ON
log_bin_basename,/var/lib/mysql/binlog
log_bin_index,/var/lib/mysql/binlog.index
log_bin_trust_function_creators,OFF
log_bin_use_v1_row_events,OFF
2、通过show master logs
,查看当前binlog明细
Log_name,File_size,Encrypted
binlog.000001,3101453,No
binlog.000002,179,No
binlog.000003,3382334,No
binlog.000004,684,No
3、通过show master status
查看最新binlog状态
File,Position,Binlog_Do_DB,Binlog_Ignore_DB,Executed_Gtid_Set
binlog.000004,684,"","",""
4、将误操作时binlog备份到执行目录,然后通过全量备份恢复数据
# 备份binlog
mkdir -p /data/backups/binlog/
cp /var/lib/mysql/binlog.000004 /data/backups/binlog/
# 先通过全量备份还原数据
mysql -h localhost -u root -p < /data/backups/dmp/dmp3.sql
5、分析binlog,确定具体误操作位置
mysqlbinlog --base64-output=DECODE-ROWS /data/backups/binlog/binlog.000004 | more
例如,通过上述命令,可查看binlog详情(也可以通过show binlog events in 'binlog.000004'
来分析),输出如下:
# at 552
#200719 14:48:19 server id 1 end_log_pos 684 CRC32 0x2e00f1bb Query thread_id=8 exec_time=0 error_code=0 Xid = 1534
use `user`/*!*/;
SET TIMESTAMP=1595170099/*!*/;
DROP TABLE `t_user1` /* generated by server */
分析后,即可定位到552位置即drop table的时刻
6、通过mysqlbinlog及定位到的位置,实现增量恢复
# 基于备份的binlog,将数据库恢复到552位置
mysqlbinlog --stop-position=552 /data/backups/binlog/binlog.000004 | mysql -u root -p
至此,binlog增量备份及恢复就完成了。
补充一个配置,在mysql8中,默认的binlog_format是row,可通过show variables like “binlog_format”
查看,因此就不需要单独,但因为docker默认是标准时区,为了便于后续开发,还是需要配置为+8时区:
default_time_zone='+8:00'
相关配置修订后的my.cnf文件:
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
default_time_zone='+8:00'
主要操作命令:
# 将配置文件拷贝出来
docker cp mysql-dump-test:/etc/mysql/my.cnf D:\dev2\test\mysqldump
# 将修改后的配置文件覆盖回去
docker cp D:\dev2\test\mysqldump\my.cnf mysql-dump-test:/etc/mysql
# 重启mysql数据库
docker restart mysql-dump-test
# 查看日志情况
docker logs mysql-dump-test
欢迎关注我的公众号【技术路漫漫】,反馈相关问题和意见:
更多参考资料:
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
https://www.percona.com/doc/percona-xtrabackup/8.0/index.html
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。