gtids mysql_mysql基于GTIDS复制

GTIDS的环境：

一台master 192.168.200.111

多个slave： 192.168.200.112 192.168.200.113

修改master服务器：

[root@localhost ~]# /etc/init.d/mysql stop

[root@localhost ~]# vim /etc/my.cnf

[mysqld]

datadir=/usr/local/mysql/data

socket=/tmp/mysql.sock

symbolic-links=0

#GTID:

gtid_mode=on

enforce_gtid_consistency=on

server_id=1

#binlog

log-bin=mysql-binlog

log-slave-updates=1

binlog_format=row

#relay log

skip_slave_start=1

[mysqld_safe]

log-error=/usr/local/mysql/data/mysql.log

pid-file=/usr/local/mysql/data/mysql.pid

!includedir /etc/my.cnf.d

[root@localhost ~]# /etc/init.d/mysql start

[root@localhost ~]# mysql -uroot -p111111

mysql> grant replication slave on *.* to 'myslave'@'192.168.200.%' identified by '123456';

可能会出错，原因是自定义密码比较简单，就出现了不符合密码策略的问题。

解决方法请查看https://www.cnblogs.com/lyqlyqlyq/p/11683294.html

mysql> flush privileges;

修改slave1服务器

[root@localhost ~]# /etc/init.d/mysql stop

[root@localhost ~]# vim /etc/my.cnf

[mysqld]

datadir=/usr/local/mysql/data

socket=/tmp/mysql.sock

symbolic-links=0

gtid_mode=on

enforce_gtid_consistency=on

server_id=2

read_only=on

log_bin=on

#保证从节点的安全性

master_info_repository=TABLE

relay_log_info_repository=TABLE

[mysqld_safe]

log-error=/usr/local/mysql/data/mysql.log

pid-file=/usr/local/mysql/data/mysql.pid

!includedir /etc/my.cnf.d

[root@localhost ~]# /etc/init.d/mysql start

[root@localhost ~]# mysql -uroot -p111111

mysql> stop slave;

mysql> change master to master_host='192.168.200.111',

-> master_user='myslave',

-> master_password='123456',

-> master_auto_position=1;

mysql> start slave;

修改slave2服务器

[root@localhost ~]# /etc/init.d/mysql stop

[root@localhost ~]# vim /etc/my.cnf

[mysqld]

datadir=/usr/local/mysql/data

socket=/tmp/mysql.sock

symbolic-links=0

gtid_mode=on

enforce_gtid_consistency=on

server_id=3

read_only=on

log_bin=on

#保证从节点的安全性

master_info_repository=TABLE

relay_log_info_repository=TABLE

[mysqld_safe]

log-error=/usr/local/mysql/data/mysql.log

pid-file=/usr/local/mysql/data/mysql.pid

!includedir /etc/my.cnf.d

[root@localhost ~]# /etc/init.d/mysql start

[root@localhost ~]# mysql -uroot -p111111

mysql> stop slave;

mysql> change master to master_host='192.168.200.111',

-> master_user='myslave',

-> master_password='123456',

-> master_auto_position=1;

mysql> start slave;

测试

在master服务器上创建数据

mysql> create database test;

Query OK, 1 row affected (0.01 sec)

然后在slave服务器上查看

mysql> show databases;