MySQL 5.7使用Xtrabackup搭建GTID主从
MySQL版本是5.7.17
操作系统是CentOS 7
MySQL数据目录:/alidata1/mysql
MySQL备份目录:/alidata1/backup/full_mysql
在master及slave机器安装xtrabackup软件libev.so.4()的解决到下面这里下载操作系统对应的版本，本例下载的是libev-4.15-7.el7.x86_64.rpm
perl(DBD::mysql)和perl(Digest::MD5)，需要安装mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm，在安装包里找到即可
在master机器操作2、备份主库2、应用日志及数据库还原
3、修改数据目录的宿主权限
5.过滤掉已执行过的gtid6.查看slave已执行的gtid是否为空，如果不为空，需要执行reset MASTER进行清理，否则无法设置gtid。7.执行reset master
8.执行GTID_PURGED
9.change master
操作系统是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
- http://rpmfind.net/linux/rpm2html/search.php?query=libev.so.4%28%29%2864bit%29&submit=Search+...&system=&arch=
在master机器操作
1、在数据库创建备份账号
- CREATE USER xtrabk@'localhost' IDENTIFIED BY 'onlyxtrabk!@#$';
- GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT,Process ON *.* TO xtrabk@'localhost';
- FLUSH PRIVILEGES;
- innobackupex --defaults-file=/etc/my.cnf --user=xtrabk --password='onlyxtrabk!@#$' --parallel=4 /alidata1/backup/full_mysql --no-timestamp
在slave机器操作
1、停止mysql,删除或者重命名Mysql数据目录
- systemctl stop mysqld.service
- rm -rf /alidata1/mysql/data
- rm -rf /alidata1/mysql/redolog
- 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
- 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'
- mysql> show master status \G;
- *************************** 1. row ***************************
- File: bin.000001
- Position: 154
- Binlog_Do_DB:
- Binlog_Ignore_DB:
- Executed_Gtid_Set: c9c73c70-c089-11e7-8544-00163e0ad76e:1-106016597
- 1 row in set (0.00 sec)
8.执行GTID_PURGED
- SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
- SET @@SESSION.SQL_LOG_BIN= 0;
- SET @@GLOBAL.GTID_PURGED='c9c73c70-c089-11e7-8544-00163e0ad76e:1-107089934';
- SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
- change master to
- master_host='192.168.2.71',
- master_port=3306,
- master_user='repl',
- master_password='REPLsafe!@#$71',
- MASTER_AUTO_POSITION = 1;
11.show slave status\G;
相关推荐
wanjichun 2020-07-18
xiaoxiangyu 2020-06-16
Java学习 2020-06-12
wenjieyatou 2020-06-12
xjd0 2020-04-26
silencehgt 2020-03-28
景泽元的编程 2020-02-17
勇往直前 2020-02-13
tlsmile 2020-01-28
Java学习 2020-01-17
hanshangzhi 2020-01-11
azhou 2019-11-03
azhou 2019-10-21
LeeLuffy 2019-08-09
吴小伟 2019-08-03
鲁氏汤包王 2019-04-21
xjywp00 2019-06-25
Zn昕 2019-01-08
Zn昕 2018-12-14