使用PXB全量恢复+mysqlbinlog二进制日志增量恢复华为云RDS备份到本地MySQL库实现完全恢复
日常的数据备份及恢复测试,是DBA工作重中之重的事情,所以要做好备份及测试。MySQL数据库常见备份有mysqldump+binlog备份、xtrabackup+binlog备份,无论那一种,几乎都少不了对binlog的备份,说明了binlog在数据恢复中的重要性。
本文的恢复场景为:源库为华为云RDS for MySQL 5.7数据库,需要恢复到本地自建ECS中。本文演示的是一般恢复过程,详细日志没有写出。
恢复原理:华为云的RDS for MySQL的全备使用的是PXB进行的备份,没有增量备份,只有二进制日志的备份。
恢复工具:首先使用PXB(Percona XtraBackup)执行完全恢复,再使用mysqlbinlog工具解析二进制日志实现增量恢复。
一、本地ECS上安装相关软件
1.1 安装PXB软件(用于全量恢复,必须)
# PXB官网下载: https://www.percona.com/downloads/
#方法一: yum在线安装
yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum list | grep percona-xtrabackup
yum install -y percona-xtrabackup-24
#方法二: 二进制包安装
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL numactl
tar -zxvf percona-xtrabackup-2.4.20-Linux-x86_64.el7.tar.gz
mv percona-xtrabackup-2.4.20-Linux-x86_64 /usr/local/percona-xtrabackup-2.4.20-Linux-x86_64
ln -s /usr/local/percona-xtrabackup-2.4.20-Linux-x86_64 /usr/local/xtrabackup-2.4.20
ln -s /usr/local/percona-xtrabackup-2.4.20-Linux-x86_64/bin/innobackupex /usr/bin/innobackupex
ln -s /usr/local/percona-xtrabackup-2.4.20-Linux-x86_64/bin/xtrabackup /usr/bin/xtrabackup
ln -s /usr/local/percona-xtrabackup-2.4.20-Linux-x86_64/bin/xbstream /usr/bin/xbstream
innobackupex --version
xtrabackup --version
xtrabackup --help
1.2 安装qpress软件(用于解压操作,必须)
wget http://www.quicklz.com/qpress-11-linux-x64.tar
tar -xvf qpress-11-linux-x64.tar
mv qpress /usr/bin/qpress
1.3 安装mysqlbinlog软件(如果有MySQL Server环境,则不用单独安装,可选)
https://support.huaweicloud.com/usermanual-rds/rds_05_0138.html
1、MySQL的二进制日志(binary log)是一个二进制文件,主要记录了对MySQL数据库执行更改的所有操作,并且记录了语句发生时间、执行时长、操作数据等其它额外信息,但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志(binary log)主要用于数据库恢复和主从复制,以及审计(audit)操作。
2、mysqlbinlog工具用于解析binlog日志,包含在MySQL软件包中。您可在MySQL官网下载对应版本的MySQL软件包,解压并获取解压包中的mysqlbinlog工具(MySQL 5.6和5.7对应的mysqlbinlog版本均为3.4)。如果使用的mysqlbinlog工具版本太旧,就有可能无法正确解析MySQL binlog。软件包版本最好与当前您使用的msyqld大版本保持一致。3、binlog格式是二进制日志,mysql使用mysqlbinlog工具解析。
mysqlbinlog语法格式:mysqlbinlog mysql-bin.000001 | mysql -uroot -p 数据库名
mysqlbinlog 常用参数:
(1)-d:指定特定数据库的binlog
(2)-r:相当于重定向到指定文件,与>、<作用相同
(3)–start-position和–stop-position:按照指定位置解析binlog日志(精确),如不接–stop-positiion则一直到binlog日志结尾
(4)–start-datetime和–stop-datetime:按照指定时间解析binlog日志(模糊,不准确),如不接–stop-datetime则一直到binlog日志结尾
(5)-D --disable-log-bin:禁止恢复过程产生日志。指定-D时使用mysqlbinlog解析binlog时,会看到sql_log_bin=0。也可以再把binlog解析到普通SQL文件,在mysql命令行下执行SQL文件前,手工设定set sql_log_bin=0,执行恢复SQL的过程就不会产生日志,恢复后再恢复set sql_log_bin=1。sql_log_bin 是一个动态变量,修改该变量时,可以只对当前会话生效(Session)
(6)-v :显示SQL语句,在行事件中重构伪SQL语句,将自动生成带注释的SQL语句,这个并非原始SQL语句(Reconstruct pseudo-SQL statements out of row events)
(7)-vv:显示的SQL语句之后会加上字段属性注释,另外,若配置了参数binlog_rows_query_log_events,则会显示原始SQL语句
(8)–base64-output=decode-rows:不显示BINLOG内容部分,注意:做恢复操作时不能加该参数,否则不能增量恢复
安装mysqlbinlog软件的步骤如下所示:
# 解压
tar -zxvf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.30-linux-glibc2.12-x86_64 /usr/local/mysql
# 创建用户
groupadd mysql
useradd -r -g mysql mysql
chown -R mysql.mysql /usr/local/mysql
# 修改环境变量
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/bashrc ; source /etc/bashrc
二、从华为云下载RDS for MySQL的全量+binlog备份
全量备份下载:https://support.huaweicloud.com/usermanual-rds/rds_09_0031.html
Binlog备份下载:https://support.huaweicloud.com/usermanual-rds/rds_03_0100.html
注意:当二进制日志很多时,建议“合并下载Binlog备份文件”,但是该功能不支持下载大于500MB以上的Binlog合并文件。
三、恢复数据库到最新状态
3.1 PXB恢复全量数据
mkdir -p /root/backupdir
xbstream -x -p 4 < /soft/mysql-5.7.29-202007280130-rds-jspt-a682.qp -C /root/backupdir/
xtrabackup --parallel 4 --decompress --target-dir=/root/backupdir
find /root/backupdir/ -name '*.qp' | xargs rm -f
xtrabackup --prepare --apply-log-only --target-dir=/root/backupdir
xtrabackup --copy-back --target-dir=/root/backupdir --datadir=/usr/local/mysql/data
chown -R mysql:mysql /usr/local/mysql/data
#配置参数文件,注意添加skip-grant-tables参数
cat > /etc/my.cnf <<"EOF"
[mysqld]
port=3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
skip-grant-tables
EOF
# 启动数据库
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
chkconfig --level 345 mysqld on
systemctl start mysqld
systemctl status mysqld
#修改密码
mysql -uroot -p
flush privileges;
grant all privileges on *.* to root@'localhost' identified by 'lhr' with grant option;
grant all privileges on *.* to root@'%' identified by 'lhr' with grant option;
flush privileges;
select user,host from mysql.user;
# MySQL数据库大小:
select a.SCHEMA_NAME, a.DEFAULT_CHARACTER_SET_NAME,a.DEFAULT_COLLATION_NAME,
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)',
sum(truncate((data_length+index_length)/1024/1024, 2)) as '总大小(MB)',
sum(truncate(max_data_length/1024/1024, 2)) as '最大值(MB)',
sum(truncate(data_free/1024/1024, 2)) as '空闲空间(MB)'
from INFORMATION_SCHEMA.SCHEMATA a
left outer join information_schema.tables b
on a.SCHEMA_NAME=b.TABLE_SCHEMA
group by a.SCHEMA_NAME, a.DEFAULT_CHARACTER_SET_NAME,a.DEFAULT_COLLATION_NAME
order by sum(data_length) desc, sum(index_length) desc;
# 客户端访问
mysql -uroot -plhr -h192.168.59.220 -P33060
3.2 mysqlbinlog恢复增量数据
# 解压下载下来的二进制日志包
tar -zxvf increment_.tar.gz
# 获取最新的日志号,根据时间+gtid确定pos位置
[root@pxb backupdir]# more xtrabackup_binlog_info
mysql-bin.003909 817427 69daae36-8788-11ea-a44c-fa163e2caad0:1-3679848,6b260292-8788-11ea-8e39-fa163e7353ba:1-272439
[root@pxb backupdir]# more xtrabackup_info
uuid = 0fc4937d-d02f-11ea-9b23-fa163e7353ba
name =
tool_name = innobackupex
tool_command = --defaults-extra-file=/var/lib/uds/stream_backup_tmp/xtrabackup.conf --slave-info --no-timestamp --host=localhost --compress-threads=4 --stream=xbstream --show-size --tmpdir=/var/lib/uds/stream_backup_tmp/stream_tmp --lock-ddl-timeout=300 --get-data-size --compress /tmp
tool_version = 2.4.12-10
ibbackup_version = 2.4.12-10
server_version = 5.7.29-2-log
start_time = 2020-07-28 01:31:35
end_time = 2020-07-28 01:31:53
lock_time = 0
binlog_pos = filename 'mysql-bin.003909', position '817427', GTID of the last change '69daae36-8788-11ea-a44c-fa163e2caad0:1-3679848,6b260292-8788-11ea-8e39-fa163e7353ba:1-272439'
innodb_from_lsn = 0
innodb_to_lsn = 13546262100
partial = N
incremental = N
format = xbstream
compact = N
compressed = compressed
encrypted = N
# 查看日志,确定pos位置为150412
mysqlbinlog mysql-bin.merged.21 -vv --base64-output=decode-row | more
mysqlbinlog /soft/mysql-bin.merged.21 --start-datetime="2020-07-28 1:31:53" | grep "69daae36-8788-11ea-a44c-fa163e2caad0:3679849" -C 6
# 追加日志
# mysqlbinlog /soft/master-bin.003909 --start-position='817427' | mysql -uroot -plhr
# mysqlbinlog --start-datetime='2020-07-28 01:31:53' -D /soft/mysql-bin.merged.21 | mysql -uroot -plhr
mysqlbinlog /soft/mysql-bin.merged.21 --start-position=150412 -D | mysql -uroot -plhr
注意:
在华为云上,如果架构是主备环境,那么全备数据是来自于备实例,增备数据来自主实例,所以两边的binlog号是有差异的,不能直接拿来恢复。可以通过最后的GTID和时间范围再确定具体的binlog文件的pos位置。
一个报错:
[root@pxb soft]# mysqlbinlog /soft/mysql-bin.merged.21 –start-datetime=‘2020-07-28 01:31:53’ | mysql -uroot -plhr
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1781 (HY000) at line 18: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF.解决:
set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
在线修改GTID时,必须按照如下顺序 OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON,不能跳过其中环节,比如 gtid_mode 从off 不能直接变为on,否则MySQL会进行提示。
- OFF :不产生GTID,Slave只接受不带GTID的事务
- OFF_PERMISSIVE :不产生GTID,Slave即接受不带GTID的事务,也接受带GTID的事务
- ON_PERMISSIVE :产生GTID,Slave即接受不带GTID的事务,也接受带GTID的事务
- ON :产生GTID,Slave只能接受带GTID的事务。
- 点赞
- 收藏
- 关注作者
评论(0)