看完這篇，學會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 STATUSG;

輸出：

MySQL主狀態信息

mysql> SHOW SLAVE STATUSG;

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

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

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！