[聚合文章] MySQL 5.7使用Xtrabackup搭建GTID主从

MySQL版本是5.7.17

操作系统是 CentOS 7

MySQL数据目录:/alidata1/mysql

在master及slave机器安装xtrabackup软件

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

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

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

1. CREATE USER xtrabk@'localhost' IDENTIFIED BY 'onlyxtrabk!@#$';
2. GRANT RELOAD , LOCK TABLES , REPLICATION CLIENT , Process ON * . * TO xtrabk @ 'localhost' ;
3. 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数据目录

1. systemctl stop mysqld . service
2. rm -rf / alidata1 / mysql / data
3. rm -rf / alidata1 / mysql / redolog

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

systemctl start mysqld.service

5.过滤掉已执行过的gtid

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

6.查看slave已执行的gtid是否为空，如果不为空，需要执行reset MASTER进行清理，否则无法设置gtid。

1. mysql > show master status \G ;
2. * * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *
3.              File: bin . 000001
4.           Position : 154
5.      Binlog_Do_DB:
6.  Binlog_Ignore_DB:
7. Executed_Gtid_Set: c9c73c70-c089-11e7-8544-00163e0ad76e:1-106016597
8. 1 row in set ( 0 . 00 sec )

7.执行reset master

8.执行 GTID_PURGED

1. SET @ MYSQLDUMP_TEMP_LOG_BIN = @ @ SESSION . SQL_LOG_BIN ;
2. SET @ @ SESSION . SQL_LOG_BIN = 0 ;
3. SET @ @ GLOBAL . GTID_PURGED = 'c9c73c70-c089-11e7-8544-00163e0ad76e:1-107089934' ;
4. SET @ @ SESSION . SQL_LOG_BIN = @ MYSQLDUMP_TEMP_LOG_BIN ;

9.change master