服务器信息
| IP |
Hostname |
module |
| 172.16.1.11 |
master-A |
mysql |
| 172.16.1.12 |
master-B |
mysql |
| 172.16.1.13 |
slave-A |
mysql |
| 172.16.1.14 |
slave-B |
mysql |
实现结果:往任意主库修改数据，四台节点均可以实现数据同步
配置Master-A
1
2
3
4
5
6
7
8
| vim /etc/my.cnf
[mysqld]
添加：
server_id=1
log_bin=mysql-bin
log-slave-updates=on
auto_increment_offset=1
auto_increment_increment=2
|
解释
server_id=1 # 设置集群中的节点唯一标识
log_bin=mysql-bin # 开启二进制日志文件，并指定二进制日志文件名字以及存放路径
log-slave-updates=on # 从节点同步的数据操作写入到二进制日志文件中
auto_increment_offset=1 # 从编号1开始自增
auto_increment_increment=2 # 每次自增2，也就是1、3、5…
重启服务
1
| systemctl restart mysqld
|
授权
1
2
3
4
5
| mysql -u root -p123.com
grant replication privileges on *.* to 'slave'@'172.16.1.%' identified by '123.com';
flush privileges;
|
replication : 复制权限
all : 所有权限
select,update : 查询和更新权限
收回权限：revoke 权限 on 数据库对象 from 用户
配置Master-B
1
2
3
4
5
6
7
8
| vi /etc/my.cnf
[mysqld]
添加：
server_id=2
log_bin=mysql-bin
log-slave-updates=on
auto_increment_offset=2
auto_increment_increment=2
|
重启服务
1
| systemctl restart mysqld
|
授权
1
2
3
4
5
| mysql -u root -p123.com
grant replication privileges on *.* to 'slave'@'172.16.1.%' identified by '123.com';
flush privileges;
|
配置Master-A和Master-B互同步
Master-A指向Master-B
Master-B操作查看master状态
1
2
3
4
5
6
| mysql> show master status;
mysql-bin.000002 414
row in set ( 0.o0 sec)
|
Master-A操作
1
| change master to master_host='172.16.1.12',master_user='slave',master_password='123.com',master_log_file='mysql-bin.000002',master_log_pos=414;
|
Master-A开启从状态
Master-A查看从状态信息，I/O线程和SQL线程是否为YES
1
2
3
| show slave status\G;
Slace_IO_Runing: Yes
Slave_SQL_Runing: Yes
|
Master-B指向Master-A
Master-A操作查看master状态
1
2
3
4
5
| mysql> show master status;
mysql-bin.000001 414
row in set ( 0.o0 sec)
|
Master-B操作
1
| change master to master_host='172.16.1.11',master_user='slave',master_password='123.com',master_log_file='mysql-bin.000002',master_log_pos=414;
|
Master-B开启从状态
Master-B查看从状态信息，I/O线程和SQL线程是否为YES
1
2
3
| show slave status\G;
Slace_IO_Runing: Yes
Slave_SQL_Runing: Yes
|
从节点指向主节点
Slave-A指向Master-A
Slave-A操作
1
2
3
4
| vi /etc/my.cnf
添加：
server_id=3
relay-log=relay-log-bin
|
重启服务
1
| systemctl restart mysqld
|
连接master-A
1
2
3
| mysql> change master to master_host='172.16.1.11',master_user='slave',master_password='123.com',master_log_file='mysql-bin.000001',master_log_pos=414;
mysql> start slave;
mysql> show slave status\G;
|
Slave-B指向Master-B
Slave-B操作
1
2
3
4
| vi /etc/my.cnf
添加：
server_id=4
relay-log=relay-log-bin
|
重启服务
1
| systemctl restart mysqld
|
连接master-B
1
2
3
| mysql> change master to master_host='172.16.1.12',master_user='slave',master_password='123.com',master_log_file='mysql-bin.000002',master_log_pos=414;
mysql> start slave;
mysql> show slave status\G;
|
