MySQL5.7主從複製詳細設定教學

2022-11-03 14:02:45

1 環境準備

192.168.1.34 伺服器A（主機）
192.168.1.35 伺服器B（從機）

2 安裝mysql

MySQL版本：

這裡採用Server version: 5.7.35 MySQL Community Server (GPL)

我們把安裝在“伺服器A”的資料庫稱作“主資料庫”、安裝在“伺服器B”的資料庫稱作“從資料庫”。

2.1 開放埠

確保伺服器A與伺服器B上的3306埠可以互訪。

3 設定主庫

進行下面的設定前，假設你已經在兩臺伺服器AB上安裝成功MySQL服務。

192.168.1.34 伺服器A（主機）

3.1 修改MySQL組態檔

編輯mysql組態檔/etc/my.cnf，新增如下的內容：

[mysqld]
log-bin=mysql-bin
server-id=1

儲存修改內容並重啟MySQL服務。

3.2 建立一個使用者用於複製

所謂複製使用者，即供從資料庫使用的，從主資料庫拷貝二進位制紀錄檔資訊的使用者。

用使用者端連線上MySQL服務，執行以下語句建立一個用於複製的使用者：

mysql> CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'Abc!@#123';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';

3.3 獲取二進位制檔案位置

這些資訊在後面設定從資料庫時用到。

1、阻塞資料庫寫語句

mysql> FLUSH TABLES WITH READ LOCK;

2、獲取當前二進位制檔案的名稱和位置

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1030 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.4 使用mysqldump建立資料快照

本指南預設使用InnoDB儲存引擎。

如果在啟用主從複製前，主資料庫已存在資料，那麼你就需要先把這些資料拷貝到從資料庫。下面給出一個例子，將所有資料庫備份到一個叫做dbdump.db的檔案：

shell> mysqldump --all-databases --master-data > dbdump.db

其中--master-data選項會自動追加 CHANGE MASTER TO語句，該語句在從資料庫啟動複製程序時需要到。

注意：

如果你沒有使用--master-data選項的話，那麼你需要在一個單獨的對談中鎖定所有資料表。詳情見 Section 16.1.2.3, “Obtaining the Replication Source's Binary Log Coordinates”.

4 設定從庫

192.168.1.35 伺服器B（從機）

4.1 修改MySQL組態檔

編輯mysql組態檔/etc/my.cnf，新增如下的內容：

[mysqld]
server-id=2
skip_slave_start=ON

這裡主要解釋一下skip_slave_start設定，ON表示資料庫啟動時不啟動從機的複製程序，需要通過手動的方式進行啟動。

儲存修改內容並重啟MySQL服務。

4.2 設定從庫對應的主庫

這裡主要用到 CHANGE MASTER TO語句，其基本語法如下：

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='source_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

我們的例子對應如下（當中的引數見上文[設定主資料庫](#3 設定主資料庫)一節）：

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='192.168.1.34',
    ->     MASTER_USER='repl',
    ->     MASTER_PASSWORD='Abc!@#123',
    ->     MASTER_LOG_FILE='mysql-bin.000001',
    ->     MASTER_LOG_POS=1030;

4.3 匯入全新的資料的情況

即主庫和備庫都是新建的，不存在舊庫舊錶舊資料，且主庫已經啟用二進位制紀錄檔，備庫已指定主庫及當前位置。這時你想要將其它的資料庫備份下來匯入到當前的主備資料庫中，你只需要在主庫中執行匯入命令即可（不能再備庫執行匯入語句）：

shell> mysql -h '192.168.1.34' < fulldb.dump

4.4 主庫已存在資料的情況

即主庫在啟用二進位制紀錄檔前已存在資料，你在啟用從庫複製程序前需要把主庫的備份快照匯入到從庫中，然後才能啟用從庫的複製程序。

1、新建主庫快照

shell> mysqldump --all-databases --master-data > dbdump.db

2、匯入到從庫中

shell&gt; mysql -h '192.168.1.35' &lt; fulldb.dump

4.5 啟用從庫複製程序

mysql> START SLAVE;

驗證主從複製是否啟動成功

mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.34
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2096
               Relay_Log_File: 192-relay-bin.000007
                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: 
......

如上，只有Slave_IO_Running和Slave_SQL_Running同時都是Yes的時候表示主從複製設定成功。