看完這篇,學會MySQL數據複製(含配置教程)

來源:陳亞墨 2018-10-11 22:35:58

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

點擊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_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是一個很好的選擇,同時這也是當今分布式和高可用係統必須具備的。

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

點擊查看原文

相關鏈接