转载

MySQL 5.7 用xtrabackup搭建gtid主从

mysql版本是5.7.17

操作系统是centos 7

mysql数据目录:/alidata1/mysql

mysql备份目录:/alidata1/backup/full_mysql

在master及slave机器安装xtrabackup软件

[root@iz2ze6jo3o3bqbcongnypqz innobackupex]# rpm -ivh percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
warning: percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
libev.so.4()(64bit) is needed by percona-xtrabackup-24-2.4.9-1.el7.x86_64
perl(DBD::mysql) is needed by percona-xtrabackup-24-2.4.9-1.el7.x86_64
perl(Digest::MD5) is needed by percona-xtrabackup-24-2.4.9-1.el7.x86_64
rsync is needed by percona-xtrabackup-24-2.4.9-1.el7.x86_64

libev.so.4()的解决到下面这里下载操作系统对应的版本，本例下载的是libev-4.15-7.el7.x86_64.rpm

http://rpmfind.net/linux/rpm2html/search.php?query=libev.so.4%28%29%2864bit%29&submit=Search+...&system=&arch=

perl(DBD::mysql)和perl(Digest::MD5)，需要安装mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm，在安装包里找到即可

在master机器操作

1、在数据库创建备份账号

CREATE USER xtrabk@'localhost' IDENTIFIED BY 'onlyxtrabk!@#$';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT,Process ON *.* TO xtrabk@'localhost';
FLUSH PRIVILEGES;

2、备份主库

innobackupex --defaults-file=/etc/my.cnf --user=xtrabk --password='onlyxtrabk!@#$' --parallel=4 /alidata1/backup/full_mysql --no-timestamp

在slave机器操作

1、停止mysql,删除或者重命名Mysql数据目录

2、应用日志及数据库还原

innobackupex --defaults-file=/etc/my.cnf --apply-log /alidata1/backup/full_mysql
innobackupex --defaults-file=/etc/my.cnf --copy-back /alidata1/backup/full_mysql

3、修改数据目录的宿主权限

chown -R mysql:mysql /alidata1/mysql

4、启动mysql

5.过滤掉已执行过的gtid

cat /alidata1/backup/full_mysql/xtrabackup_info |grep binlog_pos
[root@iz2ze6jo3o3bqbcongnyppz full_mysql]# cat /alidata1/backup/full_mysql/xtrabackup_info |grep binlog_pos
binlog_pos = filename 'bin.000131', position '615481029', GTID of the last change 'c9c73c70-c089-11e7-8544-00163e0ad76e:1-107089934'