数据库系列-mysql-双主双从

架构

数据库部署

# 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