技術文章 » 搭建Mysql主從複製

搭建Mysql主從複製

2023-06-20 18:00:33

MySQL主從複製是一種常用的資料庫高可用性解決方案，可以提高資料庫的可用性和效能。本教學將介紹如何搭建MySQL主從複製。

1、MySQL 支援的複製型別

基於語句的複製（STATEMENT）：在主伺服器上執行的 SQL 語句，在從伺服器上執行同樣的語句。MySQL 預設採用基於語句的複製，效率比較高。

  • 傳輸效率高，減少延遲。
  • 在從庫更新不存在的記錄時，語句賦值不會失敗。而行復制會導致失敗，從而更早發現主從之間的不一致。
  • 可能出現資料一致性問題

基於行的複製（ROW）：不記錄 SQL 語句上下文資訊，僅儲存哪條記錄被修改。

  • 大量的binlog紀錄檔佔用的空間大，傳輸頻寬佔用大。但是這種方式比基於語句的複製要更加精確。

混合型別的複製（MIXED）：預設採用基於語句的複製，一旦發現基於語句無法精確複製時，就會採用基於行的複製。

2、MySQL主從複製的工作過程

主從複製原理：

1、change master to 時，ip pot user password binlog position寫入到master.info進行記錄

2、start slave 時，從庫會啟動IO執行緒和SQL執行緒

3、IO_T，讀取master.info資訊，獲取主庫資訊連線主庫

4、主庫會生成一個準備binlog DUMP執行緒，來響應從庫

5、IO_T根據master.info記錄的binlog檔名和position號，請求主庫DUMP最新紀錄檔

6、DUMP執行緒檢查主庫的binlog紀錄檔，如果有新的，TP(傳送)給從從庫的IO_T

7、IO_T將收到的紀錄檔儲存到了TCP/IP 快取，立即返回ACK給主庫 ，主庫工作完成

8、IO_T將快取中的資料，儲存到relay-log紀錄檔檔案,更新master.info檔案binlog 檔名和postion，IO_T工作完成

9、SQL_T讀取relay-log.info檔案，獲取到上次執行到的relay-log的位置，作為起點，回放relay-log

10、SQL_T回放完成之後，會更新relay-log.info檔案

11、relay-log會有自動清理的功能

12、主庫一旦有新的紀錄檔生成，會傳送「訊號」給binlog dump，通知IO執行緒再請求

3、搭建步驟

步驟1：環境準備

在開始搭建之前，您需要準備以下環境：

  • 兩臺伺服器（一臺作為主伺服器，一臺作為從伺服器）
  • 安裝了MySQL的伺服器
  • 確保主伺服器可以通過網路存取從伺服器

步驟2：主從伺服器時間同步

yum -y install ntp

vim /etc/ntp.conf

25行左右新增
server 127.127.172.0							#設定本地是時鐘源，注意修改網段
fudge 127.127.172.0 stratum 8					#設定時間層級為8（限制在15內）

service ntpd start

yum -y install ntp ntpdate

service ntpd start
/usr/sbin/ntpdate masterIP			#進行時間同步，指向Master伺服器IP

crontab -e
*/30 * * * * /usr/sbin/ntpdate masterIP

步驟3：設定主伺服器

1、在主伺服器上開啟MySQL組態檔my.cnf，並新增以下設定：

[mysqld]
log-bin=mysql-bin
server-id=1
  • log-bin：啟用二進位制紀錄檔記錄，用於記錄主伺服器上的所有更改。
  • server-id：為主伺服器設定唯一識別符號，用於標識主伺服器和從伺服器。

2、systemctl restart mysqld 重啟MySQL服務以使更改生效。

步驟4：建立從伺服器賬戶

1、在主伺服器上建立一個用於從伺服器的賬戶，並授予複製許可權：

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
  • repl：從伺服器賬戶的使用者名稱。
  • password：從伺服器賬戶的密碼。

2、使用以下命令檢視主伺服器的二進位制紀錄檔檔名和位置：

SHOW MASTER STATUS;
  • 記下File和Position的值，稍後將在從伺服器上使用。

步驟5：設定從伺服器

  1. 在從伺服器上開啟MySQL組態檔my.cnf，並新增以下設定：

    [mysqld] server-id=2

  • server-id：為從伺服器設定唯一識別符號，用於標識主伺服器和從伺服器。
  1. 重啟MySQL服務以使更改生效。

步驟4：啟動從伺服器複製

1、在從伺服器上執行以下命令，連線到主伺服器：

CHANGE MASTER TO MASTER_HOST='master_host_name', MASTER_USER='repl',MASTER_PASSWORD='password',MASTER_LOG_FILE='recorded_log_file_name',MASTER_LOG_POS=recorded_log_position;
  • master_host_name：主伺服器的IP地址或主機名。
  • password：從伺服器賬戶的密碼。
  • recorded_log_file_name：在步驟2中記錄的主伺服器的二進位制紀錄檔檔名。
  • recorded_log_position：在步驟2中記錄的主伺服器的二進位制紀錄檔位置。

2、啟動從伺服器複製：

START SLAVE;

3、使用以下命令檢查從伺服器複製程序是否已啟動：

SHOW SLAVE STATUS\G

如果Slave_IO_Running和Slave_SQL_Running的值都為「YES」，則表示從伺服器已經成功連線到主伺服器並開始複製。

//如果需要停止主從服務複製的功能，使用以下命令：
stop slave;
//若搭建主從複製的過程出錯，則需要清理掉之前的設定，還需要執行以下命令：
reset slave all;

4、主從延遲問題

當主庫的 TPS 並行較高的時候，由於主庫上面是多執行緒寫入的，而從庫的SQL執行緒是單執行緒的，導致從庫SQL可能會跟不上主庫的處理速度。

解決方法：

  • 網路方面：儘量保證主庫和從庫之間的網路穩定，延遲較小；
  • 硬體方面：從庫設定更好的硬體，提升隨機寫的效能；
  • 設定方面：儘量使 MySQL 的操作在記憶體中完成，減少磁碟操作。或升級 MySQL5.7 版本使用並行複製；
  • 建構方面：在事務中儘量對主庫讀寫，其它非事務的讀在從庫。消除一部分延遲帶來的資料庫不一致。還可以增加快取降低一些從庫的負載。

5、結論

MySQL主從複製是一種提高資料庫可用性和效能的常用解決方案。本教學介紹瞭如何搭建MySQL主從複製，您可以根據實際情況進行調整和優化。