MySQL读写分离之MySQL 双主双从

MySQL 双主双从实现读写分离

本节是基于上一节继续扩展，基于Docker 实现MySQL 2主2从（读写分离）

一、2 主 2从架构解析

1.两台mysql都可读写，互为主备，默认只使用一台（masterA）负责数据的写入，另一台（masterB）备用；

2.masterA是masterB的主库，masterB又是masterA的主库，它们互为主从；

3.两台主库之间做高可用,可以采用keepalived等方案（使用VIP对外提供服务）；

4.所有提供服务的从服务器与masterB进行主从同步（双主多从）;

架构简易图如下：

服务节点	节点状态
192.168.190.165	既是主节点也是从节点，与183互为主从
192.168.190.183	既是主节点也是从节点，与165互为主从
192.168.190.184	是183的从节点
192.168.190.185	是165的从节点

二、主节点配置

这里是对 192.168.190.165 和 192.168.190.183 2个互为主从的主节点 做统一的配置

1.1. 编写docker-compose.yml 文件

• 创建目录 mkdir /usr/local/docker/mysql/
• cd /usr/local/docker/mysql/ && mkdir data conf
• vim docker-compose.yml

version: '3.1'
services:
  mysql:
    restart: always
    image: mysql:5.7
    container_name: mysql
    ports:
      - 3306:3306
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: 123456
    command:
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
      --max_allowed_packet=128M
      --sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"
    volumes:
      - ./data:/var/lib/mysql
      - ./conf:/etc/mysql/mysql.conf.d

1.2. 编写MySQL配置文件mysqld.cnf

第1个主节点配置

192.168.190.165 在 /usr/local/docker/mysql/conf/ 目录下创建，mysqld.cnf文件，并加入以下配置

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir    = /var/lib/mysql

symbolic-links=0

server-id = 165
log-bin = mysql-bin
enforce_gtid_consistency = ON
gtid_mode = ON

auto_increment_increment=2
auto_increment_offset=2
log-slave-updates
sync_binlog=1

第2个主节点配置

192.168.190.183 在 /usr/local/docker/mysql/conf/ 目录下创建，mysqld.cnf文件，并加入以下配置

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir    = /var/lib/mysql
symbolic-links=0

server-id = 183
log-bin = mysql-bin
enforce_gtid_consistency = ON
gtid_mode = ON

auto_increment_increment=2
auto_increment_offset=1
log-slave-updates
sync_binlog=1

1.3 查看2个主节的 状态

1、启动mysql: cd /usr/local/docker/mysql/ && docker-compose up -d

2、进入容器：docker exec -it mysql /bin/bash

3、登录容器中mysql server : mysql -uroot -p123456

• 192.168.190.195 主节点1状态

mysql> show master status \G;
*************************** 1. row ***************************
             File: mysql-bin.000005
         Position: 495
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 83039ec9-d923-11e9-b99b-0242c0a82002:1-11,
fe718590-d923-11e9-8508-0242c0a8c002:1-7
1 row in set (0.00 sec)

• 192.168.190.183 主节点2状态

mysql> show master status \G;
*************************** 1. row ***************************
             File: mysql-bin.000005
         Position: 495
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 83039ec9-d923-11e9-b99b-0242c0a82002:1-11,
fe718590-d923-11e9-8508-0242c0a8c002:1-7
1 row in set (0.00 sec)

1.4. 设置2个主节点的主从关系

## 1、在主节点配置主从复制账号（2个主节点都执行一遍）
create user 'bpm'@'%' identified by '123456';
grant replication slave on *.* to bpm;

## 2、设置主从关系
### 2.1 192.168.190.195 节点设置 需要同步的节点（主库节点）
change master to 
master_host='192.168.190.183',
master_user='bpm',
master_password='123456';

### 2.2 192.168.190.183 节点设置 需要同步的节点（主库节点） 
change master to 
master_host='192.168.190.195',
master_user='bpm',
master_password='123456';

1.5 开启主从复制

• 192.168.190.195 节点（Slave_IO_Running 、Slave_SQL_Running 都必须为Yes）

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.190.183
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: 7b7d4c2fe2d3-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes 
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table:

• 192.168.190.183 节点（Slave_IO_Running 、Slave_SQL_Running 都必须为Yes）

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.190.165
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: 117fd52ec5fe-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 

三、从节点配置

这里主要是对 192.168.141.57 (是192.168.141.54的从节点) 、192.168.141.58 (是192.168.141.55的从节点)

做统一的配置

2.1 编写docker-compose.yml 文件

version: '3.1'
services:
  mysql:
    restart: always
    image: mysql:5.7
    container_name: mysql
    ports:
      - 3306:3306
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: 123456
    command:
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
      --max_allowed_packet=128M
      --sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"
    volumes:
      - ./data:/var/lib/mysql
      - ./conf:/etc/mysql/mysql.conf.d

2.2 编写MySQL配置文件mysqld.cnf

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir    = /var/lib/mysql

symbolic-links=0

server-id=184
enforce_gtid_consistency = ON
gtid_mode = ON

2.3 启动MySQL

• 启动mysql: cd /usr/local/docker/mysql/ && docker-compose up -d
• 进入容器：docker exec -it mysql /bin/bash
• 登录容器中: mysql server : mysql -uroot -p123456

2.4 开启主从配置

从节点1

开启从节点 192.168.190.184 复制 主节点192.168.190.183

change master to 
master_host='192.168.190.183',
master_user='bpm',
master_password='123456';

start slave

查看从节点信息

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.190.183
                  Master_User: bpm
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 3060753
               Relay_Log_File: 53afe365d437-relay-bin.000004
                Relay_Log_Pos: 3060966
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
           
           
           ..... 省略

从节点2

开启从节点 192.168.190.185 复制 主节点192.168.190.165

change master to 
master_host='192.168.190.165',
master_user='bpm',
master_password='123456';

start slave

查看从节点信息

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.190.165
                  Master_User: bpm
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 495
               Relay_Log_File: e7bbc17474b2-relay-bin.000008
                Relay_Log_Pos: 708
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
          
。。。。。省略

ster_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 495
               Relay_Log_File: e7bbc17474b2-relay-bin.000008
                Relay_Log_Pos: 708
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
          
。。。。。省略