xtrabackup mysql 8_使用xtrabackup备份mysql8.0.16

最新推荐文章于 2024-08-06 14:40:07 发布

魏小韭

最新推荐文章于 2024-08-06 14:40:07 发布

阅读量279

点赞数

文章标签： xtrabackup mysql 8

本文链接：https://blog.csdn.net/weixin_35654499/article/details/113550873

版权

本文详细介绍了如何使用Percona XtraBackup 8.0备份和恢复mysql8.0.16，包括主库备份恢复、从库配置以及从从库备份创建新的主库从库的步骤，并着重强调了在特定环节中遇到的问题及解决办法。

摘要由CSDN通过智能技术生成

Percona XtraBackup 8.0支持备份mysql8，之前的Percona XtraBackup 2.x 只能备份到5.7

所以备份命令有所不同

一、主库备份恢复

/usr/bin/xtrabackup --defaults-file=/etc/3306.cnf --login-path=root3306 --backup --target-dir=/data/backup/***

xtrabackup --prepare --target-dir=/data/backup/***

mv /data/backup/*** /data/3306

chown -R mysql.mysql /data/3306

systemctl start 3306

二、配置一个从库

/usr/bin/xtrabackup --defaults-file=/etc/3306.cnf --login-path=root3306 --backup --target-dir=/data/backup/***

rsync -av /data/backup/*** rsync://......

xtrabackup --prepare --target-dir=/data/......

cp -a /data/...... /data/3306

chown -R mysql.mysql /data/3306

systemctl start 3306

cd /data/3306

cat xtrabackup_binlog_info

change master ...

三、从从库的备份目录上创建一个主库的从库 (重点)

在从库上备份

/usr/bin/xtrabackup --defaults-file=/etc/3306.cnf --login-path=root3306 --backup --slave-info --target-dir=/data/backup/***

多--slave-info 这个参数

同步到第二个从库机器上

rsync -av /data/backup/*** rsync://......

xtrabackup --prepare --target-dir=/data/......

cp -a /data/...... /data/3306

chown -R mysql.mysql /data/3306

systemctl start 3306

cd /data/3306

这里既不是看xtrabackup_slave_info，也不是xtrabackup_binlog_info，百度谷歌了全地球，发现这个主从配置信息在备份的时候根本就没更新到xtrabackup_slave_info文件里，也没更新到mysql.slave_master_info表里

最后想起来去看看备份的时候日志，才找到了正确的position

change master ...

start slave;

show slave status\G;

记录一下错误日志

2019-05-14T10:56:05.646593+08:00 0 [ERROR] [MY-010544] [Repl] Failed to open the relay log './mysql-relay-bin.000054' (relay_log_pos 1581814).

2019-05-14T10:56:05.646605+08:00 0 [ERROR] [MY-011059] [Repl] Could not find target log file mentioned in relay log info in the index file './mysql-relay-bin.index' during relay log initialization.

2019-05-14T10:56:05.649213+08:00 0 [ERROR] [MY-010426] [Repl] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.

2019-05-14T10:56:05.649228+08:00 0 [ERROR] [MY-010529] [Repl] Failed to create or recover replication info repositories.

2019-05-14T10:58:37.208543+08:00 9 [ERROR] [MY-013124] [Repl] Slave SQL for channel '': Slave failed to initialize relay log info structure from the repository, Error_code: MY-013124

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event 'mysql-bin.000031' at 1581814, the last event read from './mysql-bin.000031' at 124, the last byte read from './mysql-bin.000031' at 1581833.'