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;