MYSQL主從複製配置（整理）

徐~某~某發表於2018-09-29
MySql

MYSQL主從原理及過程

原理

Mysql的 Replication 是一個非同步的複製過程（mysql5.1.7以上版本分為非同步複製和半同步兩種模式），從一個 Mysql instace(我們稱之為 Master)複製到另一個 Mysql instance(我們稱之 Slave)。在 Master 與 Slave 之間的實現整個複製過程主要由三個執行緒來完成，其中兩個執行緒(Sql執行緒和IO執行緒)在 Slave 端，另外一個執行緒(IO執行緒)在 Master 端。
要實現 MySQL 的 Replication ，首先必須開啟 Master 端的Binary Log(mysql-bin.xxxxxx)功能，否則無法實現。因為整個複製過程實際上就是Slave從Master端獲取該日誌然後再在自己身上完全 順序的執行日誌中所記錄的各種操作。開啟 MySQL 的 Binary Log 可以通過在啟動 MySQL Server 的過程中使用 “—log-bin” 引數選項，或者在配置檔案中的 mysqld 引數組([mysqld]標識後的引數部分)增加 “log-bin” 引數項。

基本過程

  1. Slave 上面的IO執行緒連線上 Master，並請求從指定日誌檔案的指定位置(或者從最開始的日誌)之後的日誌內容；

  2. Master 接收到來自 Slave 的 IO 執行緒的請求後，通過負責複製的 IO 執行緒根據請求資訊讀取指定日誌指定位置之後的日誌資訊，返回給 Slave 端的 IO 執行緒。返回資訊中除了日誌所包含的資訊之外，還包括本次返回的資訊在 Master 端的 Binary Log 檔案的名稱以及在 Binary Log 中的位置；

  3. Slave 的 IO 執行緒接收到資訊後，將接收到的日誌內容依次寫入到 Slave 端的Relay Log檔案(mysql-relay-bin.xxxxxx)的最末端，並將讀取到的Master端的bin-log的檔名和位置記錄到master- info檔案中，以便在下一次讀取的時候能夠清楚的告訴Master“我需要從某個bin-log的哪個位置開始往後的日誌內容，請發給我”

  4. Slave 的 SQL 執行緒檢測到 Relay Log 中新增加了內容後，會馬上解析該 Log 檔案中的內容成為在 Master 端真實執行時候的那些可執行的 Query 語句，並在自身執行這些 Query。這樣，實際上就是在 Master 端和 Slave 端執行了同樣的 Query，所以兩端的資料是完全一樣的。

Mysql複製的幾種模式

.從 MySQL 5.1.12 開始，可以用以下三種模式來實現：

– 基於SQL語句的複製(statement-based replication, SBR)，

– 基於行的複製(row-based replication, RBR)，

– 混合模式複製(mixed-based replication, MBR)

相應地，binlog的格式也有三種：STATEMENT，ROW，MIXED。 MBR 模式中，SBR 模式是預設的。

設定主從複製模式：

log-bin=mysql-bin

#binlog_format="STATEMENT"

#binlog_format="ROW"

binlog_format="MIXED"

也可以在執行時動態修改binlog的格式。例如

mysql> SET SESSION binlog_format = `STATEMENT`;

mysql> SET SESSION binlog_format = `ROW`;

mysql> SET SESSION binlog_format = `MIXED`;

mysql> SET GLOBAL binlog_format = `STATEMENT`;

mysql主從複製配置

版本：mysql5.7 centos7.2

場景描述：
主資料庫伺服器：192.168.206.100，MySQL已經安裝，並且無應用資料。
從資料庫伺服器：192.168.206.200，MySQL已經安裝，並且無應用資料。

1 主伺服器上進行的操作

啟動mysql服務

service mysqld start

通過命令列登入管理MySQL伺服器

mysql -uroot -p`new-password`

授權複製許可權給從資料庫伺服器192.168.206.200

mysql> GRANT REPLICATION SLAVE ON *.* to `rep1`@`192.168.206.200` identified by ‘password’;

查詢主資料庫狀態

配置從伺服器時會用到
mysql> show master status;
+————————-+———-+————–+——————+——————-+
| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+————————-+———-+————–+——————+——————-+
| mysql-master-bin.000001 | 154 | | | |
+————————-+———-+————–+——————+——————-+

這裡需要注意一點，若查詢時返回的是

mysql> show slave status;
Empty set (0.01 sec)

這是因為沒有開啟bin-log造成的，需要去修改/etc/my.cnf檔案

server-id =1
log-bin=mysql-master-bin

修改檔案時還需要注意一點，mysql5.7之後，開啟binlog時還需要同時指定server-id，否則會報錯

2 配置從伺服器

修改從伺服器的配置檔案/opt/mysql/etc/my.cnf

將 server-id = 1修改為 server-id = 2，並確保這個ID沒有被別的MySQL服務所使用。

啟動mysql服務

service mysqld start

登入管理MySQL伺服器

mysql -uroot -p`new-password`

執行同步SQL語句

change master to
master_host=`192.168.206.100`,
master_user=`root`,
master_password=`Xu261220..`,
master_log_file=`mysql-master-bin.000001`,
master_log_pos=154;

正確執行後啟動Slave同步程式

mysql> start slave;

注意，這裡又有一個坑了
即使啟動start slave成功了，主從複製任然是失敗的

1、錯誤訊息
mysql> show slave staus;
 
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; 
these UUIDs must be different for replication to work.
 
2、檢視主從的server_id變數
master_mysql> show variables like `server_id`;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 33|
+---------------+-------+
 
slave_mysql> show variables like `server_id`;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 11|
+---------------+-------+
-- 從上面的情形可知，主從mysql已經使用了不同的server_id
 
3、解決故障
###檢視auto.cnf檔案
[root@dbsrv1 ~] cat /data/mysqldata/auto.cnf  ### 主上的uuid
[auto]
server-uuid=62ee10aa-b1f7-11e4-90ae-080027615026
 
[root@dbsrv2 ~]# more /data/mysqldata/auto.cnf ###從上的uuid，果然出現了重複，原因是克隆了虛擬機器，只改server_id不行
[auto]
server-uuid=62ee10aa-b1f7-11e4-90ae-080027615026
 
[root@dbsrv2 ~]# mv /data/mysqldata/auto.cnf  /data/mysqldata/auto.cnf.bk  ###重新命名該檔案
[root@dbsrv2 ~]# service mysql restart  ###重啟mysql
Shutting down MySQL.[  OK  ]
Starting MySQL.[  OK  ]
[root@dbsrv2 ~]# more /data/mysqldata/auto.cnf  ###重啟後自動生成新的auto.cnf檔案，即新的UUID
[auto]
server-uuid=6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9
 
 
###再次檢視slave的狀態已經正常
[root@dbsrv1 ~]# mysql -uroot -pxxx -e "show slave statusG"|grep Running
Warning: Using a password on the command line interface can be insecure.
 Slave_IO_Running: Yes
Slave_SQL_Running: Yes
  Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
###主庫端檢視自身的uuid
master_mysql> show variables like `server_uuid`;
+---------------+--------------------------------------+
| Variable_name | Value|
+---------------+--------------------------------------+
| server_uuid   | 62ee10aa-b1f7-11e4-90ae-080027615026 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
###主庫端檢視從庫的uuid
master_mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID   |
+-----------+------+------+-----------+--------------------------------------+
|33 |  | 3306 |11 | 62ee10aa-b1f7-11e4-90ae-080027615030 |
|22 |  | 3306 |11 | 6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9 |
+-----------+------+------+-----------+--------------------------------------+

參考：https://blog.csdn.net/leshami/article/details/43854505

其中Slave_IO_Running 與 Slave_SQL_Running 的值都必須為YES，才表明狀態正常。

如果主伺服器已經存在應用資料，則在進行主從複製時，需要做以下處理：
(1)主資料庫進行鎖表操作，不讓資料再進行寫入動作
mysql> FLUSH TABLES WITH READ LOCK;

(2)檢視主資料庫狀態
mysql> show master status;

(3)記錄下 FILE 及 Position 的值。
將主伺服器的資料檔案（整個/opt/mysql/data目錄）複製到從伺服器，建議通過tar歸檔壓縮後再傳到從伺服器解壓。

(4)取消主資料庫鎖定
mysql> UNLOCK TABLES;

3 驗證主從複製效果

在主伺服器上建立資料庫first_db

mysql> create database first_db;
Query Ok, 1 row affected (0.01 sec)

在主伺服器上建立表first_tb

mysql> create table first_tb(id int(3),name char(10));
Query Ok, 1 row affected (0.00 sec)

在主伺服器上的表first_tb中插入記錄

mysql> insert into first_tb values (001,“myself”);
Query Ok, 1 row affected (0.00 sec)

在從伺服器上檢視

mysql> show databases;

相關文章