架构
ip |
role |
主 |
版本 |
192.168.96.201 |
主+从 |
192.168.96.202 |
5.7.38 |
192.168.96.202 |
主+从 |
192.168.96.201 |
5.7.38 |
192.168.96.203 |
从 |
以上双主 |
5.7.38 |
192.168.96.204 |
从 |
以上双主 |
5.7.38 |
数据库部署
# mysql一键部署脚本
curl download.beyourself.org.cn/mysql57.sh | /bin/bash
环境准备
# 全部执行
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 各服务器唯一
master_info_repository=table
relay_log_info_repository=table
# 204
log-bin=/data/mysql-bin
server-id=4 # server id 各服务器唯一
master_info_repository=table
relay_log_info_repository=table
mkdir /data # 创建binlog日志目录
chown mysql.mysql /data
两个主库授权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
for channel "channel1";
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
for channel "channel2";
# 204
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
for channel "channel1";
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
for channel "channel2";
开启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