MySQL叢集搭建(1)-主備搭建

程淇銘發表於2018-11-17
MySql

資料庫在任何業務中都是最重要的環節之一，這就對資料庫架構提出的較高的要求。單點資料庫永遠不應該出現在生產環境，我們已經目睹過太多由於單點、備份缺失造成的損失，所以，搭建高可用 MySQL 叢集是非常有必要的。

搭建叢集有以下幾點好處:

  1. 高可用性，在主節失效時自動切換，不需要技術人員緊急處理
  2. 高吞吐，可以多個節點同時提供讀取資料服務，降低主節點負載，實現高吞吐
  3. 可擴充套件性強，支援線上擴容
  4. 無影響備份，在備節點進行備份操作不會對業務產生影響

要說缺點，有以下幾點:

  1. 架構複雜，在部署、管理方面對技術人員有要求
  2. 備節點拉取主節點日誌時會對主節點伺服器效能有一定影響
  3. 如果配置了半同步複製，會對事務提交有一點影響

總的說，叢集是一定要搭建的，誰敢把自己的資料跑在一個隨時會有風險的資料庫上呢。接下來我會以幾篇文章介紹怎麼從簡單地主備模式到高可用架構。本節主要介紹如何搭建 MySQL 主備，注重操作，不會有太多理論講解。

1 環境準備

1.1 啟動資料庫

在兩臺機器分別啟動 MySQL 例項, MySQL 搭建方式可以參考 MySQL 安裝(二進位制版)

IP 系統 埠 MySQL版本 節點
192.168.41.83 Centos6.8 3306 5.7.20 Master
192.168.41.72 Centos6.8 3306 5.7.20 Salve

關鍵配置:

Master:

[client]
port = 3306
default-character-set=utf8mb4
socket = /data/mysql_db/mysql_seg_3306/mysql.sock

[mysqld]
datadir = /data/mysql_db/mysql_seg_3306
basedir = /usr/local/mysql57
tmpdir = /tmp
socket = /data/mysql_db/mysql_seg_3306/mysql.sock
pid-file = /data/mysql_db/mysql_seg_3306/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 833306

default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0

#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql_seg_3306/mysql-bin
log_bin_index = /data/mysql_log/mysql_seg_3306/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.index
log_error = /data/mysql_log/mysql_seg_3306/mysql-error.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql57/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

Salve

[client]
port = 3306
default-character-set=utf8mb4
socket = /data/mysql_db/mysql_seg_3306/mysql.sock

[mysqld]
datadir = /data/mysql_db/mysql_seg_3306
basedir = /usr/local/mysql57
tmpdir = /tmp
socket = /data/mysql_db/mysql_seg_3306/mysql.sock
pid-file = /data/mysql_db/mysql_seg_3306/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 723306
read_only=1

default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0

#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql_seg_3306/mysql-bin
log_bin_index = /data/mysql_log/mysql_seg_3306/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.index
log_error = /data/mysql_log/mysql_seg_3306/mysql-error.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql57/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

配置解析

  • datadir, basedir, tmpdir 分別為資料檔案位置、資料庫程式安裝位置、臨時檔案位置
  • server_id 例項id，注意，同一叢集機器的 server_id 不能相同
  • read_only 是否只讀, 一般在備庫設定
  • log_bin, log_bin_index 二進位制日誌位置、二進位制日誌索引檔案位置
  • binlog_format 二進位制日誌格式，row 表示記錄每條資料變化情況、statement 表示記錄相關 sql 語句、mixed 表示兩種混用，在搭建叢集的時候建議使用 row 格式，如果是用 sql 語句來同步資料很容易出現資料不一致的情況
  • relay_log_recovery slave 當機後，假如中繼日誌損壞，則重新拉取日誌，為了保證中繼日誌完整性，建議開啟
  • relay_log, relay_log_index 中繼日誌以及中繼日誌索引檔案位置
  • log_error 錯誤日誌位置
  • replicate_wild_ignore_table 同步時需要忽略的表，這裡我們忽略了系統統計表，如果出現奇怪的同步失敗情況，可以嘗試開啟
  • plugin_dir 外掛位置
  • plugin_load 啟動時需要載入的外掛
  • loose_rpl_semi_sync_master_enabled 是否開啟無損半同步複製-主庫（建議主備都開啟,方便主備切換）
  • loose_rpl_semi_sync_slave_enabled 是否開啟無損半同步複製-備庫（建議主備都開啟,方便主備切換）

1.2 插入資料

我們假設 Master 是正在使用的資料庫，現在要線上搭建備庫，我們往 Master 節點插入一些測試資料

[mysql@mysql-test-83 ~]$ mydb-test_seg
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 29
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type `help;` or `h` for help. Type `c` to clear the current input statement.

db83-3306>>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

db83-3306>>create database mytest;
Query OK, 1 row affected (0.00 sec)

db83-3306>>use mytest;
Database changed
db83-3306>>create table test1(
    -> id int not null primary key auto_increment,
    -> name varchar(16) not null default ``,
    -> age int not null default 0
    -> ) engine = InnoDb charset = utf8;
Query OK, 0 rows affected (0.01 sec)
db83-3306>>insert into test1 values (0, `a`, 16), (0, `b`, 17), (0, `c`, 18), (0, `d`, 19);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

db83-3306>>select * from test1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  16 |
|  2 | b    |  17 |
|  3 | c    |  18 |
|  4 | d    |  19 |
+----+------+-----+
4 rows in set (0.00 sec)

2 線上搭建主從

現在我們的環境如下

  • 192.168.41.83:3306 Master 節點，正在使用
  • 192.168.41.72:3306 新搭建資料庫，要在上面做 192.168.41.83 的備庫

2.1 建立同步使用者

我們建立一個使用者名稱為 repl 的使用者，授予 REPLICATION SLAVE 許可權專門用來同步

db83-3306>>CREATE USER `repl`@`%` IDENTIFIED BY `repl`;
Query OK, 0 rows affected (5.01 sec)

db83-3306>>GRANT REPLICATION SLAVE ON *.* TO `repl`@`%`;
Query OK, 0 rows affected (0.00 sec)

db83-3306>>flush privileges;
Query OK, 0 rows affected (0.00 sec)

2.2 備份資料

常用的備份資料的方式有 innobackupex 和 mysqldump，這裡資料量少，我們用 mysqldump 進行全備

[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysqldump -S /data/mysql_db/mysql_seg_3306/mysql.sock -F --opt -R --single-transaction --master-data=2 --default-character-set=utf8 -A > mysql_backup_full.sql

引數解析:

  • -S 選擇 socket 檔案，本機連線資料庫可以用這種方法，也可以指定 ip、埠進行連線
  • -F 開始匯出之前重新整理日誌

  • --opt 如果有這個參數列示同時啟用了 mysqldump 命令的 quick，add-drop-table，add-locks，extended-insert，lock-tables 引數

    • --quick 代表忽略緩衝輸出，mysqldump 命令直接將資料匯出到指定的SQL檔案
    • --add-drop-table 就是在每個 CREATE TABEL 命令之前增加 DROP-TABLE IF EXISTS 語句，防止資料表重名
    • --add-locks 在INSERT資料之前和之後鎖定和解鎖對應的資料表
    • --extended-insert 表示可以多行插入
  • -R 匯出儲存過程以及自定義函式, 如果有用到儲存過程, 需要加這個引數
  • --single-transaction (innodb)設定事務的隔離級別為可重複讀，即 REPEATABLE READ，這樣能保證在一個事務中所有相同的查詢讀取到同樣的資料, 如果全部表都為 InnoDB 就帶上這個引數，保證資料一致性，備份時不會鎖表。如果有 MyISAM 的表，需要鎖表備份才能保證資料的一致性
  • --lock-all-tables 備份過程加讀鎖, single-transaction 選項和 lock-all-tables 選項是二選一的
  • --master-data=2 記錄當前二進位制日誌位置, master_data取1和取2的區別，只是後者把 change master ... 命令註釋起來了
  • --default-character-set 選擇編碼, 這個選項非常重要， 編碼選不對或者沒有設定很容易造成亂碼
  • -A 代表備份所有的庫

資料備份完畢後，把資料檔案直接傳輸到 Slave 機器上

[mysql@mysql-test-83 ~]$ ll
total 772
-rw-rw-r-- 1 mysql mysql 786921 Nov 17 10:38 mysql_backup_full.sql
[mysql@mysql-test-83 ~]$ rsync -avzP mysql_backup_full.sql 192.168.41.72:/home/mysql/

2.3 資料恢復

在 Slave 機器上直接執行 sql 檔案匯入資料

[mysql@mysql-test-72 ~]$ ll mysql_backup_full.sql 
-rw-rw-r-- 1 mysql mysql 786921 Nov 17 10:38 mysql_backup_full.sql
[mysql@mysql-test-72 ~]$ /usr/local/mysql57/bin/mysql -S /data/mysql_db/mysql_seg_3306/mysql.sock < mysql_backup_full.sql

匯入完畢，我們可以看到資料和 Master 的備份資料一致

db72-3306>>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mytest             |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

db72-3306>>use mytest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
db72-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| test1            |
+------------------+
1 row in set (0.00 sec)

db72-3306>>select * from test1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  16 |
|  2 | b    |  17 |
|  3 | c    |  18 |
|  4 | d    |  19 |
+----+------+-----+
4 rows in set (0.00 sec)

2.4 開啟同步

回到備份檔案，我們從頭部找到 Master 備份時間點的二進位制日誌位置

[mysql@mysql-test-72 ~]$ head -30 mysql_backup_full.sql | grep `CHANGE MASTER TO MASTER_LOG_FILE`
-- CHANGE MASTER TO MASTER_LOG_FILE=`mysql-bin.000004`, MASTER_LOG_POS=154;

MASTER_LOG_FILE 和 MASTER_LOG_POS 就是在 Master 執行 show master status 得到的二進位制位置資訊。現在，我們執行同步命令

-- 重置複製
-- reset slave;

-- 同步配置
CHANGE MASTER TO
MASTER_HOST=`192.168.41.83`,
MASTER_PORT=3306,
MASTER_USER=`repl`,
MASTER_PASSWORD=`repl`,
MASTER_LOG_FILE=`mysql-bin.000004`,
MASTER_LOG_POS=154;

-- 開啟同步
start slave

實際執行結果如下

db72-3306>>CHANGE MASTER TO
    -> MASTER_HOST=`192.168.41.83`,
    -> MASTER_PORT=3306,
    -> MASTER_USER=`repl`,
    -> MASTER_PASSWORD=`repl`,
    -> MASTER_LOG_FILE=`mysql-bin.000004`,
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.20 sec)

db72-3306>>start slave;
Query OK, 0 rows affected (0.01 sec)

檢視同步狀態

db72-3306>>show slave status G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.41.83
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             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: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 527
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 833306
                  Master_UUID: 15958368-e9a0-11e8-a98c-ecb1d77febe4
             Master_Info_File: /data/mysql_db/mysql_seg_3306/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

我們可以從 Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master 這三個引數可以判斷出同步狀態是否正常

  • Slave_IO_Running 取 Master 日誌的執行緒, Yes 為正在執行
  • Slave_SQL_Running 從日誌恢復資料的執行緒, Yes 為正在執行
  • Seconds_Behind_Master 當前資料庫相對於主庫的資料延遲， 這個值是根據二進位制日誌的時間戳計算得到的(秒)

從輸出結果可以看到我們的同步是正常的，下面我們來測試一下

2.5 同步測試

在 Master 節點插入新資料

db83-3306>>insert into test1 values(0, `chengqm`, 24);
Query OK, 1 row affected (0.00 sec)

db83-3306>>select * from test1;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | a       |  16 |
|  2 | b       |  17 |
|  3 | c       |  18 |
|  4 | d       |  19 |
|  5 | chengqm |  24 |
+----+---------+-----+
5 rows in set (0.00 sec)

備節點檢查資料同步狀態

db72-3306>>select * from test1;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | a       |  16 |
|  2 | b       |  17 |
|  3 | c       |  18 |
|  4 | d       |  19 |
|  5 | chengqm |  24 |
+----+---------+-----+
5 rows in set (0.00 sec)

可以看到資料已經同步到備節點，本次主備搭建完成

相關文章