1.簡介

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來自于我們對這個行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長期合作伙伴，公司提供的服務(wù)項(xiàng)目有：域名注冊、虛擬空間、營銷軟件、網(wǎng)站建設(shè)、吳忠網(wǎng)站維護(hù)、網(wǎng)站推廣。

MySQL 是企業(yè)應(yīng)用程序中使用最多的SQL數(shù)據(jù)庫之一，其能夠管理事務(wù)和內(nèi)核中的ACID行為，且數(shù)據(jù)庫本身的使用及相關(guān)命令的使用都很便利。

在開源Web應(yīng)用程序框架LAMP(包括Linux，Apache，MySQL和PHP)中，MySQL服務(wù)器是一個核心和重要的組件。MySQL數(shù)據(jù)庫服務(wù)器使用C和C ++編寫的，內(nèi)部使用詞法分析器來解析和理解SQL查詢。

隨著系統(tǒng)變得分散、可擴(kuò)展且高度容錯時，我們越來越無法承受數(shù)據(jù)庫中的故障，例如數(shù)據(jù)庫服務(wù)器發(fā)生故障且無法自動管理。所以，本文就將和大家討論一下數(shù)據(jù)庫復(fù)制。

當(dāng)系統(tǒng)的MySQL數(shù)據(jù)庫發(fā)生故障，利用數(shù)據(jù)庫復(fù)制我們可以轉(zhuǎn)移到其副本并從中管理數(shù)據(jù)，甚至用戶都感知不到數(shù)據(jù)庫中發(fā)生了錯誤。不同的企業(yè)使用數(shù)據(jù)庫復(fù)制的初衷包括但不限于以下原因：

• 確保直接從數(shù)據(jù)庫備份數(shù)據(jù)

• 在不干擾主數(shù)據(jù)庫的情況下運(yùn)行分析或檢查數(shù)據(jù)

• 擴(kuò)展數(shù)據(jù)庫以獲得更好的性能

2. MySQL設(shè)置

我們創(chuàng)建了兩個具有不同IP的新服務(wù)器，在副本集中將其分別用作主服務(wù)器和從服務(wù)器。為了進(jìn)一步研究，我們在它們上面設(shè)置了MySQL服務(wù)器和客戶端工具。

安裝MySQL服務(wù)器和客戶端：

sudo apt-get install mysql-server mysql-client

運(yùn)行此命令后，服務(wù)器上即安裝了上述程序，然后在兩臺服務(wù)器上進(jìn)行相同的配置并設(shè)置MySQL root密碼：

設(shè)置Root密碼

安裝過程完成后，使用以下命令確認(rèn)MySQL服務(wù)器是否已啟動并運(yùn)行：

sudo service mysql status

輸出：

檢查MySQL服務(wù)器狀態(tài)

MySQL服務(wù)器已啟動并運(yùn)行，使用在安裝過程中的用戶名和密碼連接。

登錄MySQL

mysql -u root -p

此時，MySQL服務(wù)器會等待我們輸入密碼，出于安全考慮，密碼不會回顯給終端。登錄MySQL命令行后，會出現(xiàn)以下提示：

MySQL登錄

進(jìn)入MySQL命令提示符時，我們可以使用給定的命令來顯示系統(tǒng)中存在的數(shù)據(jù)庫并確保MySQL運(yùn)行正常：

顯示所有數(shù)據(jù)庫

show databases;

輸出：

檢查MySQL數(shù)據(jù)庫

在輸出中，MySQL只顯示用于管理目的的MySQL默認(rèn)數(shù)據(jù)庫列表。只要在兩臺服務(wù)器上看到Active狀態(tài)，我們就可以繼續(xù)進(jìn)行Master和Slave數(shù)據(jù)庫的配置。

3.掌握MySQL服務(wù)器配置

MySQL安裝完之后，我們就可以進(jìn)行master數(shù)據(jù)庫的配置，即在主MySQL配置文件中添加配置，在Ubuntu上使用nano編輯器打開并執(zhí)行以下命令：

編輯配置文件

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

該文件包含許多選項(xiàng)，利用它們可以修改和配置在系統(tǒng)上運(yùn)行的MySQL服務(wù)器的行為。首先，我們需要在文件中找到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修改為當(dāng)前服務(wù)器IP：

更新Bind Address屬性

bind-address =

查看server-id屬性：

服務(wù)器ID屬性

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

# other settings you may need to change.

#server-id = 1

更新服務(wù)器ID屬性

server-id = 1

log_bin屬性通知實(shí)際保存副本集詳細(xì)信息的文件。

Log Bin屬性

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

在這個文件中，從服務(wù)器記錄它從主數(shù)據(jù)庫中容納的變化?，F(xiàn)在我們將取消對屬性的注釋，編輯binlog_do_db屬性，該屬性通知從數(shù)據(jù)庫服務(wù)器在從數(shù)據(jù)庫中復(fù)制哪個數(shù)據(jù)庫。我們可以通過對我們需要的所有數(shù)據(jù)庫重復(fù)此行來包含多個數(shù)據(jù)庫：

DB備份：

binlog_do_db = jcg_database

配置文件中顯示的更新屬性：

更新了配置文件

完成所有屬性后，我們可以保存文件并重新啟動MySQL服務(wù)器，以便這些更新反映在服務(wù)器中。要重新啟動MySQL服務(wù)器，請運(yùn)行以下命令：

重啟MySQL：

sudo service mysql restart

一旦MySQL服務(wù)器重新啟動，我們需要做的下一個更改是在MySQL shell本身內(nèi)部。再次登錄MySQL命令行。

授權(quán)給Slave DB，以便它可以訪問和復(fù)制我們在配置文件中提到的數(shù)據(jù)庫中的數(shù)據(jù)jcg_database。

授予權(quán)限

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

刷新權(quán)限：

FLUSH PRIVILEGES;

切換到創(chuàng)建之后要復(fù)制的數(shù)據(jù)庫：

mysql> CREATE SCHEMA jcg_database;

Query OK, 1 row affected (0.00 sec)

mysql> USE jcg_database;

Database changed

鎖定數(shù)據(jù)庫，禁止更改：

Read Lock：

FLUSH TABLES WITH READ LOCK;

在應(yīng)用鎖之前，我們需要制定一些新表并插入數(shù)據(jù)。

檢查主狀態(tài)

SHOW MASTER STATUS;

輸出：

主數(shù)據(jù)庫狀態(tài)

需要注意的是，因?yàn)檫@是從屬DB開始復(fù)制數(shù)據(jù)庫的位置。如果我們對DB進(jìn)行任何更改，它將自動解鎖，所以不要在同一窗口中進(jìn)行任何新的更改。下一部分有點(diǎn)棘手，打開一個新的終端窗口或選項(xiàng)卡(不關(guān)閉當(dāng)前選項(xiàng)卡)并登錄MySQL服務(wù)器并執(zhí)行以下命令：

轉(zhuǎn)儲MySQL

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

輸出：

MySQL轉(zhuǎn)儲

退出單獨(dú)打開的新選項(xiàng)卡并返回到舊選項(xiàng)卡。在該選項(xiàng)卡上，解鎖數(shù)據(jù)庫并退出MySQL：

解鎖并退出

UNLOCK TABLES;

QUIT;

如此，我們就完成了在master數(shù)據(jù)庫上所需的所有配置。

4.從屬M(fèi)ySQL服務(wù)器配置

現(xiàn)在，我們準(zhǔn)備開始配置復(fù)制數(shù)據(jù)的從數(shù)據(jù)庫，登錄Slave服務(wù)器并在其中打開MySQL命令行。創(chuàng)建一個具有相同名稱的數(shù)據(jù)庫，復(fù)制并退出MySQL終端：

MySQL Slave DB

使用我們制作的SQL文件將原始數(shù)據(jù)庫導(dǎo)入Slave MySQL服務(wù)器，確保將該文件帶到此新服務(wù)器上并運(yùn)行以下命令將其導(dǎo)入到從屬M(fèi)ySQL數(shù)據(jù)庫中：

導(dǎo)入數(shù)據(jù)庫

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

點(diǎn)擊Enter后，數(shù)據(jù)庫內(nèi)容和元數(shù)據(jù)將導(dǎo)入從數(shù)據(jù)庫。完成之后，我們也可以配置Slave MySQL DB：

配置DB

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

我們需要確保此配置文件中的某些屬性已設(shè)置，server-id設(shè)置的默認(rèn)值為1，也可利用下面命令設(shè)置為其它值：

這個財(cái)產(chǎn)是server-id。它當(dāng)前設(shè)置為1，這是默認(rèn)值。將其設(shè)置為其他值：

Server ID for Slave

server-id = 2

Slace的其他屬性設(shè)置：

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

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

binlog_do_db = jcg_database

添加relay-log屬性，因?yàn)槟J(rèn)情況下它不在配置文件中。完成此操作后，還需要重新啟動Slave MySQL DB，配置更改才能生效。

重啟MySQL

sudo service mysql restart

一旦MySQL服務(wù)器重新啟動，我們需要做的下一個更改是在MySQL shell本身內(nèi)部。所以再次登錄MySQL命令行。

在MySQL shell中，執(zhí)行以下命令：

啟用復(fù)制

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

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

• 通知當(dāng)前MySQL服務(wù)器，它是給定的MySQL主服務(wù)器的Slave

• 為Slave提供了Master Server的登錄憑據(jù)

• 通知Slave需要啟動復(fù)制過程的位置以及日志文件詳細(xì)信息

使用以下命令最終激活從服務(wù)器：

激活MySQL Slave Server

START SLAVE;

使用以下命令查看一些主要細(xì)節(jié)：

MySQL主狀態(tài)

SHOW SLAVE STATUS\G;

輸出：

MySQL主狀態(tài)信息

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)

如果在連接時出現(xiàn)問題，可以嘗試使用命令啟動slave：

MySQL主狀態(tài)

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

這樣，我們就完成了MySQL復(fù)制的配置，數(shù)據(jù)正在MySQL服務(wù)器上復(fù)制，并嘗試將一些數(shù)據(jù)插入Master數(shù)據(jù)庫，并檢查數(shù)據(jù)是否也復(fù)制到從數(shù)據(jù)庫。

5.復(fù)制滯后

MySQL復(fù)制利用兩個線程來完成主數(shù)據(jù)庫和從屬數(shù)據(jù)庫之間的復(fù)制：

1. IO_THREAD

2. SQL_THREAD

IO_THREAD連接到主MySQL服務(wù)器，讀取二進(jìn)制日志以跟蹤和更改數(shù)據(jù)庫中的事件，將它們復(fù)制到本地中繼日志文件，Slave數(shù)據(jù)庫的SQL_THREAD讀取并跟蹤更改，將它們復(fù)制到Slave數(shù)據(jù)庫。

如果我們觀察到任何復(fù)制延遲，首先要確定此延遲是來自Slave的IO_THREAD還是Slave的SQL_THREAD。

通常，I / O線程不會導(dǎo)致任何重大的復(fù)制延遲，因?yàn)樗皇菑闹鲾?shù)據(jù)庫讀取二進(jìn)制日志，但有些因素會影響其性能，如網(wǎng)絡(luò)連接，網(wǎng)絡(luò)延遲以及通信網(wǎng)絡(luò)的速度等等。如果Master上存在大量寫入，由于帶寬問題，復(fù)制可能會很慢。

另一方面，如果SQL線程在Slave延遲了，那么最可能的原因是主數(shù)據(jù)庫的SQL查詢需要在Slave數(shù)據(jù)庫執(zhí)行執(zhí)行較長時間。另外， MySQL 5.6之前slave是單線程的，這也是導(dǎo)致從屬SQL_THREAD延遲的另一個原因。

6.復(fù)制的優(yōu)點(diǎn)

MySQL復(fù)制在生產(chǎn)環(huán)境中具備一些明顯優(yōu)勢：

• 性能：Slave服務(wù)器可以很容易地用于向任何請求數(shù)據(jù)的客戶端提供READ支持。這意味著Master數(shù)據(jù)庫上的負(fù)載會減少很多，因?yàn)闆]有對它進(jìn)行讀取。

• 備份性能：如果有任何運(yùn)行的備份任務(wù)，則可以在復(fù)制數(shù)據(jù)時通過Slave數(shù)據(jù)庫運(yùn)行它。這意味著備份作業(yè)根本不會影響Master數(shù)據(jù)庫。

• 災(zāi)難恢復(fù)：在Master數(shù)據(jù)庫完全脫機(jī)的事件中，如果以這種方式配置，Slave數(shù)據(jù)庫可以快速取代它并開始執(zhí)行寫操作。這將允許在重建和恢復(fù)主服務(wù)器時最小的站點(diǎn)停機(jī)時間。

7.復(fù)制的缺點(diǎn)

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

• 復(fù)雜性：如果管理不正確，具有大量Slave進(jìn)行復(fù)制的應(yīng)用程序可能會造成維護(hù)噩夢。

• 性能：要完成復(fù)制過程，需要將二進(jìn)制日志寫入磁盤，盡管它的影響可能很小，但是在查看整體服務(wù)器性能時仍需要考慮?？梢酝ㄟ^將二進(jìn)制日志寫入磁盤的單獨(dú)分區(qū)來解決，以限制IO性能問題。

8.復(fù)制的局限性

除了上述內(nèi)容，還有一些數(shù)據(jù)復(fù)制的限制點(diǎn)需要說明：

• 復(fù)制不是應(yīng)用程序邏輯的備份，并且在Master數(shù)據(jù)庫上執(zhí)行的任何更改將始終復(fù)制到Slave數(shù)據(jù)庫，并且不能限制它。如果用戶刪除master數(shù)據(jù)庫上的數(shù)據(jù)，它也將在Slave數(shù)據(jù)庫中刪除。

• 在多個Slaves的情況下，性能不會增加，反而會降低，因?yàn)閿?shù)據(jù)庫連接分布在多個服務(wù)器上，并且在任何服務(wù)器發(fā)生故障時出現(xiàn)問題的風(fēng)險(xiǎn)都會增加。

9. MySQL復(fù)制的類型

從本質(zhì)上講，MySQL支持三種不同的方法將數(shù)據(jù)從主服務(wù)器復(fù)制到從屬服務(wù)器。所有這些方法都使用二進(jìn)制日志，但它與日志的寫入方式不同。以下是復(fù)制的方法：

• 基于語句的復(fù)制：使用此方法，數(shù)據(jù)庫中每次更改的SQL語句都存儲在二進(jìn)制日志文件中。從屬設(shè)備將讀取這些SQL語句并在自己的MySQL數(shù)據(jù)庫上執(zhí)行它們，以便從主服務(wù)器生成完全相同的數(shù)據(jù)副本。這是MySQL 5.1.11和MySQL 5.1.29中的默認(rèn)復(fù)制方法。

• 基于行的復(fù)制：在此方法中，二進(jìn)制日志文件存儲主數(shù)據(jù)庫表中發(fā)生的所有記錄級更改。從服務(wù)器讀取此數(shù)據(jù)并根據(jù)主數(shù)據(jù)更新其自己的記錄，以生成主數(shù)據(jù)庫的精確副本。

• 混合格式復(fù)制：在此方法中，服務(wù)器將在基于語句的復(fù)制和基于行的復(fù)制之間動態(tài)選擇，具體取決于某些條件，如使用用戶定義的函數(shù)(UDF)，使用帶DELAYED子句的INSERT命令，臨時表，或使用使用系統(tǒng)變量的語句。這是MySQL 5.1.12到MySQL 5.1.28中的默認(rèn)復(fù)制方法。

在用例中，當(dāng)你不確定要使用哪種復(fù)制方法時，最好使用基于語句的復(fù)制，因?yàn)樗亲畛Ｓ煤妥詈唵蔚膱?zhí)行方式。如果你有一個寫入繁重的系統(tǒng)，則不建議使用基于語句的復(fù)制，因?yàn)樗矐?yīng)用表鎖。在這種情況下，可以使用基于行的復(fù)制方法。

10.對業(yè)績的影響

如前所述，復(fù)制可能會影響數(shù)據(jù)庫的性能，但與其他事情相比，復(fù)制對主服務(wù)器的影響通常非常小，因?yàn)閙aster只需要在復(fù)制環(huán)境中完成兩件重要事情：

• 制定事件并將事件寫入本地硬盤驅(qū)動器上的二進(jìn)制日志

• 將它寫入二進(jìn)制日志的每個事件副本發(fā)送給每個連接的從站

即使沒有復(fù)制，二進(jìn)制日志也是要始終打開的，所以在考慮復(fù)制成本時也不需要列入二進(jìn)制日志。

另外，復(fù)制事件發(fā)送到從設(shè)備的成本也可以忽略不計(jì)，因?yàn)閺脑O(shè)備負(fù)責(zé)維護(hù)與主設(shè)備的持久TCP連接，主設(shè)備只需在事件發(fā)生時將數(shù)據(jù)復(fù)制到套接字上。除此之外，主設(shè)備絲毫不關(guān)心從設(shè)備是否或合適執(zhí)行。

最后一條語句的部分異常是半同步復(fù)制，這不是默認(rèn)值。在這種模式下，主服務(wù)器等待至少一個從服務(wù)器確認(rèn)來自每個事務(wù)的二進(jìn)制日志事件的接收和持久存儲(盡管不是實(shí)際執(zhí)行)，然后主服務(wù)器在每次提交時將控制權(quán)返回給客戶端。

在任何情況下，主服務(wù)器都不負(fù)責(zé)實(shí)際執(zhí)行從服務(wù)器上的更新，它只向從服務(wù)器發(fā)送兩件事：運(yùn)行的實(shí)際輸入查詢的副本(基于語句的模式)或數(shù)據(jù)對于每個查詢實(shí)際插入/更新/刪除的行(在基于行的模式下)。在混合模式下，查詢優(yōu)化器將決定在每個事件的基礎(chǔ)上使用哪種格式。

11. 綜述MySQL復(fù)制

在確保生產(chǎn)系統(tǒng)運(yùn)行時具有故障轉(zhuǎn)移可靠性使其成為容錯系統(tǒng)時，MySQL Replication是一個很好的選擇，同時這也是當(dāng)今分布式和高可用系統(tǒng)必須具備的。

本文向大家介紹了在單個從屬服務(wù)器上復(fù)制數(shù)據(jù)需要進(jìn)行的重要的配置更改、系統(tǒng)更改。當(dāng)然，因?yàn)橹鞣?wù)器上沒有和從服務(wù)器相關(guān)或綁定的配置，所以我們可以在不影響主服務(wù)器的情況下設(shè)置任意數(shù)量的從服務(wù)器。