看完這篇，學會MySQL資料複製（含配置教程）

1.簡介

MySQL 是企業應用程式中使用最多的SQL資料庫之一，其能夠管理事務和核心中的ACID行為，且資料庫本身的使用及相關命令的使用都很便利。

在開源Web應用程式框架LAMP(包括Linux，Apache，MySQL和PHP)中，MySQL伺服器是一個核心和重要的元件。MySQL資料庫伺服器使用C和C ++編寫的，內部使用詞法分析器來解析和理解SQL查詢。

隨著系統變得分散、可擴充套件且高度容錯時，我們越來越無法承受資料庫中的故障，例如資料庫伺服器發生故障且無法自動管理。所以，本文就將和大家討論一下資料庫複製。

當系統的MySQL資料庫發生故障，利用資料庫複製我們可以轉移到其副本並從中管理資料，甚至使用者都感知不到資料庫中發生了錯誤。不同的企業使用資料庫複製的初衷包括但不限於以下原因：

• 確保直接從資料庫備份資料

• 在不干擾主資料庫的情況下執行分析或檢查資料

• 擴充套件資料庫以獲得更好的效能

2. MySQL設定

我們建立了兩個具有不同IP的新伺服器，在副本集中將其分別用作主伺服器和從伺服器。為了進一步研究，我們在它們上面設定了MySQL伺服器和客戶端工具。

安裝MySQL伺服器和客戶端：

sudo apt-get install mysql-server mysql-client

執行此命令後，伺服器上即安裝了上述程式，然後在兩臺伺服器上進行相同的配置並設定MySQL root密碼：

設定Root密碼

安裝過程完成後，使用以下命令確認MySQL伺服器是否已啟動並執行：

sudo service mysql status

輸出：

檢查MySQL伺服器狀態

MySQL伺服器已啟動並執行，使用在安裝過程中的使用者名稱和密碼連線。

登入MySQL

mysql -u root -p

此時，MySQL伺服器會等待我們輸入密碼，出於安全考慮，密碼不會回顯給終端。登入MySQL命令列後，會出現以下提示：

MySQL登入

進入MySQL命令提示符時，我們可以使用給定的命令來顯示系統中存在的資料庫並確保MySQL執行正常：

顯示所有資料庫

show databases;

輸出：

檢查MySQL資料庫

在輸出中，MySQL只顯示用於管理目的的MySQL預設資料庫列表。只要在兩臺伺服器上看到Active狀態，我們就可以繼續進行Master和Slave資料庫的配置。

3.掌握MySQL伺服器配置

MySQL安裝完之後，我們就可以進行master資料庫的配置，即在主MySQL配置檔案中新增配置，在Ubuntu上使用nano編輯器開啟並執行以下命令：

編輯配置檔案

sudo nano /etc/mysql/mysql.conf.d/my.cnf

該檔案包含許多選項，利用它們可以修改和配置在系統上執行的MySQL伺服器的行為。首先，我們需要在檔案中找到bind-address屬性：

繫結地址屬性

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

bind-address = 127.0.0.1

將此IP修改為當前伺服器IP：

更新Bind Address屬性

bind-address = <server_ip_here>

檢視server-id屬性：

伺服器ID屬性

# note: if you are setting up a replication slave, see README.Debian about

# other settings you may need to change.

#server-id = 1

更新伺服器ID屬性

server-id = 1

log_bin屬性通知實際儲存副本集詳細資訊的檔案。

Log Bin屬性

log_bin = /var/log/mysql/mysql-bin.log

在這個檔案中，從伺服器記錄它從主資料庫中容納的變化。現在我們將取消對屬性的註釋，編輯binlog_do_db屬性，該屬性通知從資料庫伺服器在從資料庫中複製哪個資料庫。我們可以透過對我們需要的所有資料庫重複此行來包含多個資料庫：

DB備份：

binlog_do_db = jcg_database

配置檔案中顯示的更新屬性：

更新了配置檔案

完成所有屬性後，我們可以儲存檔案並重新啟動MySQL伺服器，以便這些更新反映在伺服器中。要重新啟動MySQL伺服器，請執行以下命令：

重啟MySQL：

sudo service mysql restart

一旦MySQL伺服器重新啟動，我們需要做的下一個更改是在MySQL shell本身內部。再次登入MySQL命令列。

授權給Slave DB，以便它可以訪問和複製我們在配置檔案中提到的資料庫中的資料jcg_database。

授予許可權

GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

重新整理許可權：

FLUSH PRIVILEGES;

切換到建立之後要複製的資料庫：

mysql> CREATE SCHEMA jcg_database;

Query OK, 1 row affected (0.00 sec)

mysql> USE jcg_database;

Database changed

鎖定資料庫，禁止更改：

Read Lock：

FLUSH TABLES WITH READ LOCK;

在應用鎖之前，我們需要制定一些新表並插入資料。

檢查主狀態

SHOW MASTER STATUS;

輸出：

主資料庫狀態

需要注意的是，因為這是從屬DB開始複製資料庫的位置。如果我們對DB進行任何更改，它將自動解鎖，所以不要在同一視窗中進行任何新的更改。下一部分有點棘手，開啟一個新的終端視窗或選項卡(不關閉當前選項卡)並登入MySQL伺服器並執行以下命令：

轉儲MySQL

mysqldump -u root -p --opt jcg_database > jcg_database.sql

輸出：

MySQL轉儲

退出單獨開啟的新選項卡並返回到舊選項卡。在該選項卡上，解鎖資料庫並退出MySQL：

解鎖並退出

UNLOCK TABLES;

QUIT;

如此，我們就完成了在master資料庫上所需的所有配置。

4.從屬MySQL伺服器配置

現在，我們準備開始配置複製資料的從資料庫，登入Slave伺服器並在其中開啟MySQL命令列。建立一個具有相同名稱的資料庫，複製並退出MySQL終端：

MySQL Slave DB

使用我們製作的SQL檔案將原始資料庫匯入Slave MySQL伺服器，確保將該檔案帶到此新伺服器上並執行以下命令將其匯入到從屬MySQL資料庫中：

匯入資料庫

mysql -u root -p jcg_database < /root/jcg_database.sql

點選Enter後，資料庫內容和後設資料將匯入從資料庫。完成之後，我們也可以配置Slave MySQL DB：

配置DB

nano /etc/mysql/mysql.conf.d/mysqld.cnf

我們需要確保此配置檔案中的某些屬性已設定，server-id設定的預設值為1，也可利用下面命令設定為其它值：

這個財產是server-id。它當前設定為1，這是預設值。將其設定為其他值：

Server ID for Slave

server-id = 2

Slace的其他屬性設定：

relay-log = /var/log/mysql/mysql-relay-bin.log

log_bin = /var/log/mysql/mysql-bin.log

binlog_do_db = jcg_database

新增relay-log屬性，因為預設情況下它不在配置檔案中。完成此操作後，還需要重新啟動Slave MySQL DB，配置更改才能生效。

重啟MySQL

sudo service mysql restart

一旦MySQL伺服器重新啟動，我們需要做的下一個更改是在MySQL shell本身內部。所以再次登入MySQL命令列。

在MySQL shell中，執行以下命令：

啟用複製

CHANGE MASTER TO MASTER_HOST='<master-server-ip>',MASTER_USER='root', MASTER_PASSWORD='hello123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 1306;

此命令一次完成各個步驟，包括：

• 通知當前MySQL伺服器，它是給定的MySQL主伺服器的Slave

• 為Slave提供了Master Server的登入憑據

• 通知Slave需要啟動複製過程的位置以及日誌檔案詳細資訊

使用以下命令最終啟用從伺服器：

啟用MySQL Slave Server

START SLAVE;

使用以下命令檢視一些主要細節：

MySQL主狀態

SHOW SLAVE STATUS\G;

輸出：

MySQL主狀態資訊

mysql> SHOW SLAVE STATUS\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 206.189.133.122

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 1306

Relay_Log_File: mysql-relay-bin.000002

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:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 1306

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: 1

Master_UUID: 3b890258-be5d-11e8-88c2-422b77d8526c

Master_Info_File: /var/lib/mysql/master.info

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)

如果在連線時出現問題，可以嘗試使用命令啟動slave：

MySQL主狀態

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

這樣，我們就完成了MySQL複製的配置，資料正在MySQL伺服器上覆制，並嘗試將一些資料插入Master資料庫，並檢查資料是否也複製到從資料庫。

5.複製滯後

MySQL複製利用兩個執行緒來完成主資料庫和從屬資料庫之間的複製：

1. IO_THREAD

2. SQL_THREAD

IO_THREAD連線到主MySQL伺服器，讀取二進位制日誌以跟蹤和更改資料庫中的事件，將它們複製到本地中繼日誌檔案，Slave資料庫的SQL_THREAD讀取並跟蹤更改，將它們複製到Slave資料庫。

如果我們觀察到任何複製延遲，首先要確定此延遲是來自Slave的IO_THREAD還是Slave的SQL_THREAD。

通常，I / O執行緒不會導致任何重大的複製延遲，因為它只是從主資料庫讀取二進位制日誌，但有些因素會影響其效能，如網路連線，網路延遲以及通訊網路的速度等等。如果Master上存在大量寫入，由於頻寬問題，複製可能會很慢。

另一方面，如果SQL執行緒在Slave延遲了，那麼最可能的原因是主資料庫的SQL查詢需要在Slave資料庫執行執行較長時間。另外， MySQL 5.6之前slave是單執行緒的，這也是導致從屬SQL_THREAD延遲的另一個原因。

6.複製的優點

MySQL複製在生產環境中具備一些明顯優勢：

• 效能：Slave伺服器可以很容易地用於向任何請求資料的客戶端提供READ支援。這意味著Master資料庫上的負載會減少很多，因為沒有對它進行讀取。

• 備份效能：如果有任何執行的備份任務，則可以在複製資料時透過Slave資料庫執行它。這意味著備份作業根本不會影響Master資料庫。

• 災難恢復：在Master資料庫完全離線的事件中，如果以這種方式配置，Slave資料庫可以快速取代它並開始執行寫操作。這將允許在重建和恢復主伺服器時最小的站點停機時間。

7.複製的缺點

從上文看下來，MySQL Replication是很不錯的，但是它也有很多缺點：

• 複雜性：如果管理不正確，具有大量Slave進行復制的應用程式可能會造成維護噩夢。

• 效能：要完成複製過程，需要將二進位制日誌寫入磁碟，儘管它的影響可能很小，但是在檢視整體伺服器效能時仍需要考慮。可以透過將二進位制日誌寫入磁碟的單獨分割槽來解決，以限制IO效能問題。

8.複製的侷限性

除了上述內容，還有一些資料複製的限制點需要說明：

• 複製不是應用程式邏輯的備份，並且在Master資料庫上執行的任何更改將始終複製到Slave資料庫，並且不能限制它。如果使用者刪除master資料庫上的資料，它也將在Slave資料庫中刪除。

• 在多個Slaves的情況下，效能不會增加，反而會降低，因為資料庫連線分佈在多個伺服器上，並且在任何伺服器發生故障時出現問題的風險都會增加。

9. MySQL複製的型別

從本質上講，MySQL支援三種不同的方法將資料從主伺服器複製到從屬伺服器。所有這些方法都使用二進位制日誌，但它與日誌的寫入方式不同。以下是複製的方法：

• 基於語句的複製：使用此方法，資料庫中每次更改的SQL語句都儲存在二進位制日誌檔案中。從屬裝置將讀取這些SQL語句並在自己的MySQL資料庫上執行它們，以便從主伺服器生成完全相同的資料副本。這是MySQL 5.1.11和MySQL 5.1.29中的預設複製方法。

• 基於行的複製：在此方法中，二進位制日誌檔案儲存主資料庫表中發生的所有記錄級更改。從伺服器讀取此資料並根據主資料更新其自己的記錄，以生成主資料庫的精確副本。

• 混合格式複製：在此方法中，伺服器將在基於語句的複製和基於行的複製之間動態選擇，具體取決於某些條件，如使用使用者定義的函式(UDF)，使用帶DELAYED子句的INSERT命令，臨時表，或使用使用系統變數的語句。這是MySQL 5.1.12到MySQL 5.1.28中的預設複製方法。

在用例中，當你不確定要使用哪種複製方法時，最好使用基於語句的複製，因為它是最常用和最簡單的執行方式。如果你有一個寫入繁重的系統，則不建議使用基於語句的複製，因為它也應用表鎖。在這種情況下，可以使用基於行的複製方法。

10.對業績的影響

如前所述，複製可能會影響資料庫的效能，但與其他事情相比，複製對主伺服器的影響通常非常小，因為master只需要在複製環境中完成兩件重要事情：

• 制定事件並將事件寫入本地硬碟驅動器上的二進位制日誌

• 將它寫入二進位制日誌的每個事件副本傳送給每個連線的從站

即使沒有複製，二進位制日誌也是要始終開啟的，所以在考慮複製成本時也不需要列入二進位制日誌。

另外，複製事件傳送到從裝置的成本也可以忽略不計，因為從裝置負責維護與主裝置的持久TCP連線，主裝置只需在事件發生時將資料複製到套接字上。除此之外，主裝置絲毫不關心從裝置是否或合適執行。

最後一條語句的部分異常是半同步複製，這不是預設值。在這種模式下，主伺服器等待至少一個從伺服器確認來自每個事務的二進位制日誌事件的接收和持久儲存(儘管不是實際執行)，然後主伺服器在每次提交時將控制權返回給客戶端。

在任何情況下，主伺服器都不負責實際執行從伺服器上的更新，它只向從伺服器傳送兩件事：執行的實際輸入查詢的副本(基於語句的模式)或資料對於每個查詢實際插入/更新/刪除的行(在基於行的模式下)。在混合模式下，查詢最佳化器將決定在每個事件的基礎上使用哪種格式。

11. 綜述MySQL複製

在確保生產系統執行時具有故障轉移可靠性使其成為容錯系統時，MySQL Replication是一個很好的選擇，同時這也是當今分散式和高可用系統必須具備的。

本文向大家介紹了在單個從屬伺服器上覆制資料需要進行的重要的配置更改、系統更改。當然，因為主伺服器上沒有和從伺服器相關或繫結的配置，所以我們可以在不影響主伺服器的情況下設定任意數量的從伺服器。