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 [email protected] IDENTIFIED BY 'repl123';

CREATE USER [email protected]'HOSTNAME' IDENTIFIED BY 'repl123';

grant all privileges on *.* to repl with grant option;

grant all privileges on *.* to [email protected] with grant option;

grant all privileges on *.* to [email protected]'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;