看完這篇，學會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是一個很好的選擇，同時這也是當今分布式和高可用系統必須具備的。

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