mysql主從同步原理及應用場景範例詳解

2022-08-05 18:03:08

基礎知識

隨著業務複雜度的增加，單臺 MySQL 資料庫伺服器已不能滿足實際的需求，取而代之的是資料庫伺服器叢集。MySQL 具有支援分散式的特性，能輕鬆搭建一個支援高並行的 MySQL 資料庫伺服器叢集。在叢集中我們必須保證各個 MySQL 節點的資料是同步的。主從同步就是一種最為常見的同步方式。

主從同步是指，在資料同步過程中，一臺伺服器充當主伺服器（Master），接收來自使用者的內容更新，另一個或多個其它的伺服器充當從伺服器（Slave），接收來自主伺服器的 binlog 內容，解析出 SQL 語句，更新到從資料庫，使得主從伺服器的資料達到一致。

MySQL 主從同步的主要應用場景有：

從伺服器作為主伺服器的備份節點，防止單點災難；
後續，可以在主從同步的基礎上，通過一些資料庫中介軟體實現讀寫分離，從而大幅提高資料庫的並行效能；
根據業務將多個從伺服器進行拆分，實現專庫專用。

從 MySQL5.6 版本開始，實現主從資料同步有兩種方式：基於紀錄檔（binlog）和基於 GTID（全域性事務標示符）。

原理

要實現 MySQL 主從同步，首先必須開啟 Master 端的 binlog 記錄功能，否則就無法實現。因為整個同步過程實際上就是 Slave 端從 Master 端獲取 binlog 紀錄檔，然後再在 Slave 上以相同的順序執行從 binlog 紀錄檔中所記錄的各種 SQL，如下圖所示。

主從同步原理：

主資料庫中對資料的各種操作，都會自動寫入 Binary Log 中；
從資料庫會在一定時間間隔內探測主資料庫的 Binary Log 是否發生變化，如有變化，則開始一個 IO 執行緒，請求存取主資料庫的二進位制紀錄檔檔案並儲存到從資料庫的中繼紀錄檔（Relay Log）中；
從資料庫啟動 SQL 執行緒從中繼紀錄檔中讀取二進位制紀錄檔，在本地重放，使其資料與主資料庫保持一致，完成後相關執行緒會陷入休眠，等待下一次喚醒。

設定主從同步，還有以下幾個前提：

主庫和從庫的版本保持一致；
主從同步叢集中每個資料庫範例的 server-id 值不能重複；

實驗環境模擬實現主從同步

我們在此使用 docker 這樣的容器技術在一臺主機上實現 mysql 的主從同步。這裡需要做一點解釋，docker 中存在容器的概念，當啟動一個 mysql 容器時，其實可以理解為啟動了一臺僅僅只安裝了 mysql 的伺服器。

首先在 docker 中拉取 mysql 5.7 版本的映象：

docker pull mysql:5.7

使用以下命令，啟動一個 Master 容器，將其命名為 masterMysql，並設定 mysql 的 root 使用者密碼為 123456：

docker run -p 3339:3306 --name masterMysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

使用以下命令可以檢視正在執行的容器：

dokcer ps

這裡可以看到，主容器已經啟動起來了。

同樣的方式，啟動一個 Slave 容器，將其命名為 slaveMysql, 並設定 mysql 的 root 使用者密碼為 123456：

docker run -p 3340:3306 --name slaveMysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

通過以下命令進入到 Master 容器內部：

docker exec -it masterMysql /bin/bash

也可以使用檢視正在執行容器時檢視到的 CONTAINER ID 來啟動，例如筆者使用這種方式的啟動命令如下：

docker exec -it c30b3528b8c8 /bin/bash

兩種啟動方式都可以。

由於容器環境下沒有安裝 vim，所以使用以下命令安裝 vim：

sed -i 's/deb.debian.org/mirrors.ustc.edu.cn/g' /etc/apt/sources.list
apt update
apt install vim

使用以下命令切換到 /etc/mysql 目錄下，修改組態檔 my.cnf：

cd /etc/mysql
vim my.cnf

在 my.cnf 中新增如下設定：

[mysqld]
server-id=100
sync-binlog=1
binlog-do-db=world
log-bin=mysql-bin

引數說明：

server-id：即主從叢集中每個資料庫範例 id，在多個伺服器間該值不能重複，可以設定 1 - 255 之間的任意值。
sync-binlog：該引數控制資料操作與磁碟紀錄檔同步頻率。該引數的值 n 表示，執行 n 次寫入後，與磁碟同步一次，範例中設定為 1，是最安全的，但也是最慢的。
binlog-do-db：表示準備進行同步的資料庫。
log-bin：開啟二進位制紀錄檔功能，可以隨便取

之後使用以下命令重啟 mysql 使組態檔生效。此時，docker 容器也會停止，還需要啟動一次 masterMysql 容器：

service mysql restart

docker start masterMysql
docker exec -it masterMysql /bin/bash

接下來，進入資料庫：

mysql -uroot -p123456

在 Master 上設定複製所需要的賬戶，這裡建立一個 slave 使用者， % 表示任何遠端地址的 slave 使用者都可以連線 Master 容器：

CREATE USER 'slave'@'%' IDENTIFIED BY '123456';

授予使用者 slave replication slave 許可權和 replication client 許可權，用於在主從庫之間同步資料：

GRANT replication slave, replication client ON *.* TO 'slave'@'%';

檢視二進位制紀錄檔狀態資訊，獲取 position 的值，為從伺服器設定做準備：

SHOW MASTER STATUS;

重新開啟一個 Xfce Terminal，通過以下命令進入到 Slave 容器內部：

docker exec -it slaveMysql /bin/bash

由於容器環境下沒有安裝 vim，所以使用以下命令安裝 vim：

sed -i 's/deb.debian.org/mirrors.ustc.edu.cn/g' /etc/apt/sources.list
apt update
apt install vim

使用以下命令切換到 /etc/mysql 目錄下，修改組態檔 my.cnf:

cd /etc/mysql
vim my.cnf

在 my.cnf 中新增如下設定：

[mysqld]
server-id=101
log-bin=mysql-slave-bin
relay_log=edu-mysql-relay-bin

引數說明：

log-bin：開啟二進位制紀錄檔功能，以備 Slave 作為其它 Slave 的 Master 時使用
relay_log：設定中繼紀錄檔

之後使用以下命令重啟 mysql 使組態檔生效。

service mysql restart

docker start slaveMysql
docker exec -it slaveMysql /bin/bash

進入 MySQL：

mysql -uroot -p123456

接下來執行以下命令：

change master to
master_host='172.17.0.2',
master_user='slave',
master_password='123456',
master_port=3306,
master_log_file='mysql-bin.000001',
master_log_pos= 617,
master_connect_retry=30;

引數說明：

master_host：Master 的地址，指的是容器的獨立 IP,可以通過docker inspect --format='{{.NetworkSettings.IPAddress}}' masterMysql 查詢 Master 的 IP

master_user：Master 中設定的使用者名稱

master_password：Master 中設定的使用者名稱對應密碼

master_port: Master 的埠號，指的是容器的埠號

master_log_file：二進位制紀錄檔檔名(這裡注意填寫為實驗者在之前使用 show master status 命令查詢出來的值)

master_log_pos：二進位制紀錄檔的 position 值(這裡注意填寫為實驗者在之前使用 show master status 命令查詢出來的值)

master_connect_retry：如果連線失敗，重試的時間間隔，單位是秒，預設是 60 秒

執行以下命令，啟動主從操作：

start slave;

執行以下命令，查詢 Slave 狀態：

show slave statusG

結果如下：

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.2
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 617
Relay_Log_File: edu-mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...

我們主要檢視的是 Slave_IO_Running 和 Slave_SQL_Running ，如果它們的值都是 Yes，則表示主從環境設定成功。

測試主從複製就比較簡單了，我們仿照之前的實驗，在 Master 中新建一個 world 資料庫，然後插入一些資料：

CREATE DATABASE world;
USE world;
CREATE TABLE student(
    stuId INT(10) NOT NULL,
    stuName VARCHAR(10) NOT NULL,
    stuAge INT(10) NOT NULL,
    PRIMARY KEY(stuId)
    );
INSERT INTO student(stuId, stuName, stuAge) VALUES(1, 'zhangsan', 18),(2, 'lisi', 19), (3, 'wangwu', 18);

在 Slave 中執行：