Mysql集群安装部署，Slave-Mater-Master-Slave模式

1、同步模式

    一共4台主机，分别Slave-Mater-Master-Slave模式。双Master保障可靠性，多Slave进行同步，读写分离。

   Mysql版本5.7.16，主机操作系统版本Centos 6.8。

   主机A  10.3.1.101 Slave

 主机B  10.3.1.102 Master

 主机C  10.3.1.103 Master

 主机D  10.3.1.104 Slave

 

 同步模式 双Master B和C双向同步，B<->C。Master-Slave单向同步，B->A, C->D。

2、配置文件 /etc/my.cnf

-------------------------------------------------------

主机A 10.3.1.101

[mysqld]

server-id=101

relay_log=/usr/local/mysql/log/mysql-relay-bin

log_slave_updates=1

read_only=1

-------------------------------------------------------

主机B 10.3.1.102

[mysqld]

server-id=102

log-bin=/usr/local/mysql/log/mysql-bin

max_binlog_size=1024M

expire-logs-days=15

binlog_cache_size=4M

max_binlog_cache_size=512M

relay_log=/usr/local/mysql/log/mysql-relay-bin

log_slave_updates=1

read_only=0

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=1

-------------------------------------------------------

主机C 10.3.1.103

[mysqld]

server-id=103

log-bin=/usr/local/mysql/log/mysql-bin

max_binlog_size=1024M

expire-logs-days=15

binlog_cache_size=4M

max_binlog_cache_size=512M

relay_log=/usr/local/mysql/log/mysql-relay-bin

log_slave_updates=1

read_only=1

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=2

-------------------------------------------------------

[mysqld]

server-id=104

relay_log=/usr/local/mysql/log/mysql-relay-bin

log_slave_updates=1

read_only=0

3、在各个Mysql上创建同步用户

FLUSH PRIVILEGES;
CREATE USER repl IDENTIFIED BY 'repl123';
CREATE USER repl@localhost IDENTIFIED BY 'repl123';
CREATE USER repl@'HOSTNAME' IDENTIFIED BY 'repl123';
grant all privileges on *.* to repl with grant option;
grant all privileges on *.* to repl@localhost with grant option;
grant all privileges on *.* to repl@'HOSTNAME' with grant option;
FLUSH PRIVILEGES;

4、设置Master

分别在各个主机上设置对应的Master,在SQL命令窗口执行。

在101上执行：

change master to MASTER_HOST='10.3.1.102',
MASTER_USER='repl',
MASTER_PASSWORD='repl123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;

在102上执行：

change master to MASTER_HOST='10.3.1.103',
MASTER_USER='repl',
MASTER_PASSWORD='repl123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;

在103上执行：

change master to MASTER_HOST='10.3.1.102',
MASTER_USER='repl',
MASTER_PASSWORD='repl123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;

在104上执行：

change master to MASTER_HOST='10.3.1.103',
MASTER_USER='repl',
MASTER_PASSWORD='repl123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;

5、查看Salve同步信息

show slave status \G;

6、如果有错误信息，查看具体错误，可跳过错误，再同步

stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;
show slave status \G;