centos6.5 Percona XtraBackup 2.4.12 安装 && 通过xtrabackup扩展mgr节点

Percona XtraBackup 简介参照： https://blog.csdn.net/eaglejiawo1120/article/details/84583186

centos安装

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm

yum list | grep percona

yum install percona-xtrabackup-24

  ##官方安装会很慢，请耐心等待。

卸载：

yum remove percona-xtrabackup

 

使用

在目前集群的写主机上执行如下sql创建备份用户：

mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO backup@'localhost' identified by 'backup';
mysql> FLUSH PRIVILEGES;

在读节点上执行全备份：

xtrabackup --defaults-file=/home/mysql_test/mysql_5.7/mgr_data/s3210/s3210.cnf -ubackup -pbackup -S /home/mysql_test/mysql_5.7/mgr_data/s3210/s3210.sock  --target-dir=/home/mysql_test/mysql_5.7/mgr_data/s3220 --backup

准备备份数据：

xtrabackup --use-memory=1G --prepare --target-dir=/home/mysql_test/mysql_5.7/mgr_data/s3220 2>prepare.log

还原数据:

（1）停止s3220的服务

kill -9 $(ps -ef | grep s3220.cnf | grep -v grep  | awk '{print $2}')

（2）chown -R mysql:mysql /home/mysql_test/mysql_5.7/mgr_data/s3220

 ( 3 ) 访问s3220实例:

查看已经执行的gtid: select @@GLOBAL.gtid_executed;

查看备份是的gtid:

cat s3220/xtrabackup_binlog_info
binlog.000011   3260    5ca9e710-f233-11e8-94a5-005056a17264:1-127,
f9713c8f-f232-11e8-9bd2-005056a17264:1-23
 

将s3220执行的gtid修改为上述值：

 reset master; set global gtid_purged="5ca9e710-f233-11e8-94a5-005056a17264:1-127,f9713c8f-f232-11e8-9bd2-005056a17264:1-23";

( 4 )  proxysql 管理端查看存活的主机。

mysqlsh>>cluster=dba.getCluster()

mysqlsh>>cluster.addInstance('mgr_admin@192.168.1.104:3220')

 

在备份的时候出现过如下的错误：

[ERROR] InnoDB: Page [page id: space=0, page number=325] log sequence number 2799769 is in the future! Current system log sequence number 2752030.
2018-12-03T03:09:23.151858Z 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.

原因是没有执行prapare操作，直接通过全备份的数据创建节点，导致的如上错误。

 

参照：

1. https://www.percona.com/doc/percona-xtrabackup/LATEST/installation/yum_repo.html

2.https://www.percona.com/blog/2018/11/05/how-to-quickly-add-a-node-to-an-innodb-cluster-or-group-replication/