MySQL 雙主雙從實作讀寫分離

本節是基于上一節繼續擴充，基于Docker 實作MySQL 2主2從（讀寫分離）

一、2 主 2從架構解析

1.兩台mysql都可讀寫，互為主備，預設隻使用一台（masterA）負責資料的寫入，另一台（masterB）備用；

2.masterA是masterB的主庫，masterB又是masterA的主庫，它們互為主從；

3.兩台主庫之間做高可用,可以采用keepalived等方案（使用VIP對外提供服務）；

4.所有提供服務的從伺服器與masterB進行主從同步（雙主多從）;

架構簡易圖如下：

MySQL讀寫分離之MySQL 雙主雙從MySQL 雙主雙從實作讀寫分離

服務節點	節點狀态
192.168.190.165	既是主節點也是從節點，與183互為主從
192.168.190.183	既是主節點也是從節點，與165互為主從
192.168.190.184	是183的從節點
192.168.190.185	是165的從節點

MySQL讀寫分離之MySQL 雙主雙從MySQL 雙主雙從實作讀寫分離

二、主節點配置

這裡是對 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: 
          
。。。。。省略

MySQL讀寫分離之MySQL 雙主雙從MySQL 雙主雙從實作讀寫分離

MySQL 雙主雙從實作讀寫分離

一、2 主 2從架構解析

二、主節點配置

1.1. 編寫docker-compose.yml 檔案

1.2. 編寫MySQL配置檔案mysqld.cnf

第1個主節點配置

第2個主節點配置

1.3 檢視2個主節的狀态

1.4. 設定2個主節點的主從關系

1.5 開啟主從複制

三、從節點配置

2.1 編寫docker-compose.yml 檔案

2.2 編寫MySQL配置檔案mysqld.cnf

2.3 啟動MySQL

2.4 開啟主從配置

從節點1

從節點2

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案

MySQL讀寫分離之MySQL 雙主雙從MySQL 雙主雙從實作讀寫分離

MySQL 雙主雙從實作讀寫分離

一、2 主 2從架構解析

二、主節點配置

1.1. 編寫docker-compose.yml 檔案

1.2. 編寫MySQL配置檔案mysqld.cnf

第1個主節點配置

第2個主節點配置

1.3 檢視2個主節的 狀态

1.4. 設定2個主節點的主從關系

1.5 開啟主從複制

三、從節點配置

2.1 編寫docker-compose.yml 檔案

2.2 編寫MySQL配置檔案mysqld.cnf

2.3 啟動MySQL

2.4 開啟主從配置

從節點1

從節點2

繼續閱讀

1.3 檢視2個主節的狀态