架构
IP | role | 主 | 版本 |
192.168.96.201 | 主+从 | 192.168.96.202 | 5.7.44 |
192.168.96.202 | 主+从 | 192.168.96.201 | 5.7.44 |
192.168.96.203 | 从 | | 5.7.44 |
192.168.96.204 | 从 | | 5.7.44 |
环境准备
# 全部执行
mkdir /data
chown mysql.mysql /data
vim /etc/my.cnf # [mysqld]下添加
# 201
log-bin=/data/mysql-bin
server-id=1 # server id 各服务器唯一
auto_increment_increment=2 # 设置步长初始值
auto_increment_offset=1 # 设置偏移量
log-slave-updates # 主库复制的内容也会写入从库binlog
sync_binlog=1 # 事务提交既写入磁盘
# 202
log-bin=/data/mysql-bin
server-id=2 # server id 各服务器唯一
auto_increment_increment=2 # 设置步长初始值
auto_increment_offset=2 # 设置偏移量
log-slave-updates # 主库复制的内容也会写入从库binlog
sync_binlog=1 # 事务提交既写入磁盘
# 203
log-bin=/data/mysql-bin
server-id=3 # server id 各服务器唯一
# 204
log-bin=/data/mysql-bin
server-id=4 # server id 各服务器唯一
两个主库授权slave用户
CREATE USER 'slave'@'%' IDENTIFIED BY 'New@123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
FLUSH PRIVILEGES;
# 查看位置点 201
mysql> show master status\G
File: mysql-bin.000001
Position: 784
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
# 查看位置点 202
mysql> show master status\G
File: mysql-bin.000001
Position: 784
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
主从配置
# 201
CHANGE MASTER TO
MASTER_HOST='192.168.96.202',
MASTER_USER='slave',
MASTER_PASSWORD='New@123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=784,
MASTER_CONNECT_RETRY=10;
# 202
CHANGE MASTER TO
MASTER_HOST='192.168.96.201',
MASTER_USER='slave',
MASTER_PASSWORD='New@123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=784,
MASTER_CONNECT_RETRY=10;
# 203
CHANGE MASTER TO
MASTER_HOST='192.168.96.201',
MASTER_USER='slave',
MASTER_PASSWORD='New@123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=784,
MASTER_CONNECT_RETRY=10;
# 204
CHANGE MASTER TO
MASTER_HOST='192.168.96.202',
MASTER_USER='slave',
MASTER_PASSWORD='New@123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=784,
MASTER_CONNECT_RETRY=10;
开启slave(4台执行)
mysql> start slave;
mysql> show slave status\G
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.96.202
Master_User: slave
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 784
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试
201
create database db1 default charset 'utf8';
202 show databases; OK
203 show databases; OK
204 show databases; OK
Comments NOTHING