云主机云服务器

美国云服务器MySQL版本是5.7.17操作系统是CentOS 7MySQL数据目录:/alidata1/mysqlMySQL备份目录:/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
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数据目录
systemctl stop mysqld.service
rm -rf /alidata1/mysql/data
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
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'
6.查看slave已执行的gtid是否为空，如果不为空，需要执行reset MASTER进行清理，否则无法设置gtid。
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)
7.执行reset master8.执行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;
9.change master
change master to
master_host='192.168.2.71',
master_port=3306,
master_user='repl',
master_password='REPLsafe!@#$71',
MASTER_AUTO_POSITION = 1;
10. start slave ;11.show slave status\G;

购买使用一诺网络美国云服务器，可以极大降低初创企业、中小企业以及个人开发者等用户群体的整体IT使用成本，无需亲自搭建基础设施、简化了运维和管理的日常工作量，使用户能够更专注于自身的业务发展和创新。美国云服务器低至49元/月，购买链接：https://www.enuoidc.com/vpszq.html?typeid=3