使用xtrabackup备份mysql8.0.16

桃扇骨 2022-01-14 10:09 287阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

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文件里，最后想起来去看看备份时候的日志，才找到了正确的position。
    reset slave;
    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.'

转载于:https://my.oschina.net/longquan/blog/3049453

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220114/8bae847ff35e48a48ff9a6ffe239e995.png