管理mysql主從有2年多了，管理過200多組mysql主從，幾乎涉及到各個版本的主從，本博文屬於總結性的，有一部分是摘自網路，大部分是根據自己管理的心得和經驗所寫，整理了一下，分享給各位同行，希望對大家有幫助，互相交流。

、Replication 執行緒

Mysql的 Replication 是一個非同步的複製過程（mysql5.1.7以上版本分為非同步複製和半同步兩種模式），從一個 Mysql instace(我們稱之為 Master)複製到另一個 Mysql instance(我們稱之 Slave)。在 Master 與 Slave 之間的實現整個複製過程主要由三個執行緒來完成，其中兩個執行緒(Sql執行緒和IO執行緒)在 Slave 端，另外一個執行緒(IO執行緒)在 Master 端。

要實現 MySQL 的 Replication ，首先必須開啟 Master 端的Binary Log(mysql-bin.xxxxxx)功能，否則無法實現。因為整個複製過程實際上就是Slave從Master端獲取該日誌然後再在自己身上完全順序的執行日誌中所記錄的各種操作。開啟 MySQL 的 Binary Log 可以透過在啟動 MySQL Server 的過程中使用 “—log-bin” 引數選項，或者在 my.cnf 配置檔案中的 mysqld 引數組([mysqld]標識後的引數部分)增加 “log-bin” 引數項。

、MySQL 複製的基本過程如下：

2.1．Slave 上面的IO執行緒連線上 Master，並請求從指定日誌檔案的指定位置(或者從最開始的日誌)之後的日誌內容；

2.2. Master 接收到來自 Slave 的 IO 執行緒的請求後，透過負責複製的 IO 執行緒根據請求資訊讀取指定日誌指定位置之後的日誌資訊，返回給 Slave 端的 IO 執行緒。返回資訊中除了日誌所包含的資訊之外，還包括本次返回的資訊在 Master 端的 Binary Log 檔案的名稱以及在 Binary Log 中的位置；

2.3. Slave 的 IO 執行緒接收到資訊後，將接收到的日誌內容依次寫入到 Slave 端的Relay Log檔案(mysql-relay-bin.xxxxxx)的最末端，並將讀取到的Master端的bin-log的檔名和位置記錄到master- info檔案中，以便在下一次讀取的時候能夠清楚的高速Master“我需要從某個bin-log的哪個位置開始往後的日誌內容，請發給我”

2.4. Slave 的 SQL 執行緒檢測到 Relay Log 中新增加了內容後，會馬上解析該 Log 檔案中的內容成為在 Master 端真實執行時候的那些可執行的 Query 語句，並在自身執行這些 Query。這樣，實際上就是在 Master 端和 Slave 端執行了同樣的 Query，所以兩端的資料是完全一樣的。

、Mysql複製的幾種模式

3.1.從 MySQL 5.1.12 開始，可以用以下三種模式來實現：

– 基於SQL語句的複製(statement-based replication, SBR)，

– 基於行的複製(row-based replication, RBR)，

– 混合模式複製(mixed-based replication, MBR)。

相應地，binlog的格式也有三種：STATEMENT，ROW，MIXED。 MBR 模式中，SBR 模式是預設的。

在執行時可以動態改動 binlog的格式，除了以下幾種情況：

1.儲存流程或者觸發器中間

2.啟用了NDB

3.當前會話試用 RBR 模式，並且已開啟了臨時表

如果binlog採用了 MIXED 模式，那麼在以下幾種情況下會自動將binlog的模式由 SBR 模式改成 RBR 模式：

1.當DML語句更新一個NDB表時

2.當函式中包含 UUID() 時

3.2個及以上包含 AUTO_INCREMENT 欄位的表被更新時

4.行任何 INSERT DELAYED 語句時

5.用 UDF 時

6.檢視中必須要求運用 RBR 時，例如建立檢視是運用了 UUID() 函式

3.2.設定主從複製模式：

log-bin=mysql-bin

#binlog_format="STATEMENT"

#binlog_format="ROW"

binlog_format="MIXED"

也可以在執行時動態修改binlog的格式。例如

mysql> SET SESSION binlog_format = 'STATEMENT';

mysql> SET SESSION binlog_format = 'ROW';

mysql> SET SESSION binlog_format = 'MIXED';

mysql> SET GLOBAL binlog_format = 'STATEMENT';

mysql> SET GLOBAL binlog_format = 'ROW';

mysql> SET GLOBAL binlog_format = 'MIXED';

3.3.兩種模式各自的優缺點：

SBR 的優點：

歷史悠久，技能成熟

binlog檔案較小

binlog中包含了所有資料庫修改資訊，可以據此來稽核資料庫的安全等情況

binlog可以用於實時的還原，而不僅僅用於複製

主從版本可以不一樣，從伺服器版本可以比主伺服器版本高

SBR 的缺點：

不是所有的UPDATE語句都能被複制，尤其是包含不確定操作的時候。

呼叫具有不確定因素的 UDF 時複製也可能出疑問

運用以下函式的語句也不能被複制：

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

* SYSDATE() (除非啟動時啟用了 –sysdate-is-now 選項)

INSERT … SELECT 會產生比 RBR 更多的行級鎖

複製須要執行全表掃描(WHERE 語句中沒有運用到索引)的 UPDATE 時，須要比 RBR 請求更多的行級鎖

對於有 AUTO_INCREMENT 欄位的 InnoDB表而言，INSERT 語句會阻塞其他 INSERT 語句

對於一些複雜的語句，在從伺服器上的耗資源情況會更嚴重，而 RBR 模式下，只會對那個發生變化的記錄產生影響

儲存函式(不是儲存流程 )在被呼叫的同時也會執行一次 NOW() 函式，這個可以說是壞事也可能是好事

確定了的 UDF 也須要在從伺服器上執行

資料表必須幾乎和主伺服器保持一致才行，否則可能會導致複製出錯

執行復雜語句如果出錯的話，會消耗更多資源

RBR 的優點：

任何情況都可以被複制，這對複製來說是最安全可靠的

和其他大多數資料庫系統的複製技能一樣

多數情況下，從伺服器上的表如果有主鍵的話，複製就會快了很多

複製以下幾種語句時的行鎖更少：

* INSERT … SELECT

* 包含 AUTO_INCREMENT 欄位的 INSERT

* 沒有附帶條件或者並沒有修改很多記錄的 UPDATE 或 DELETE 語句

執行 INSERT，UPDATE，DELETE 語句時鎖更少

從伺服器上採用多執行緒來執行復製成為可能

RBR 的缺點：

binlog 大了很多

複雜的回滾時 binlog 中會包含大量的資料

主伺服器上執行 UPDATE 語句時，所有發生變化的記錄都會寫到 binlog 中，而 SBR 只會寫一次，這會導致頻繁發生 binlog 的併發寫疑問

UDF 產生的大 BLOB 值會導致複製變慢

不能從 binlog 中看到都複製了寫什麼語句(加密過的)

當在非事務表上執行一段堆積的SQL語句時，最好採用 SBR 模式，否則很容易導致主從伺服器的資料不一致情況發生

另外，針對系統庫 mysql 裡面的表發生變化時的處理準則如下：

如果是採用 INSERT，UPDATE，DELETE 直接操作表的情況，則日誌格式根據 binlog_format 的設定而記錄

如果是採用 GRANT，REVOKE，SET PASSWORD 等管理語句來做的話，那麼無論如何都採用 SBR 模式記錄。

注：採用 RBR 模式後，能處理很多原先出現的主鍵重複問題。例項:

對於insert into db_allot_ids select * from db_allot_ids 這個語句:

在BINLOG_FORMAT=STATEMENT 模式下:

BINLOG日誌資訊為:

—————————————–

BEGIN

/*!*/;

# at 173

#090612 16:05:42 server id 1 end_log_pos 288 Query thread_id=4 exec_time=0 error_code=0

SET TIMESTAMP=1244793942/*!*/;

insert into db_allot_ids select * from db_allot_ids

/*!*/;

—————————————–

在BINLOG_FORMAT=ROW 模式下:

BINLOG日誌資訊為:

—————————————–

BINLOG '

hA0yShMBAAAAMwAAAOAAAAAAAA8AAAAAAAAAA1NOUwAMZGJfYWxsb3RfaWRzAAIBAwAA

hA0yShcBAAAANQAAABUBAAAQAA8AAAAAAAEAAv/8AQEAAAD8AQEAAAD8AQEAAAD8AQEAAAA=

'/*!*/;

—————————————–

、Mysql主從的優缺點

MySQL的主從同步是一個很成熟的架構，優點為：①在從伺服器可以執行查詢工作(即我們常說的讀功能)，降低主服務器壓力;②在從主伺服器進行備份，避免備份期間影響主伺服器服務;③當主伺服器出現問題時，可以切換到從伺服器。所以我在專案部署和實施中經常會採用這種方案;鑑於生產環境下的mysql的嚴謹性。

實際上，在老版本中，MySQL 的複製實現在 Slave 端並不是由 SQL 執行緒和 IO 執行緒這兩個執行緒共同協作而完成的，而是由單獨的一個執行緒來完成所有的工作。但是 MySQL 的工程師們很快發現，這樣做存在很大的風險和效能問題，主要如下：

首先，如果透過一個單一的執行緒來獨立實現這個工作的話，就使複製 Master 端的，Binary Log日誌，以及解析這些日誌，然後再在自身執行的這個過程成為一個序列的過程，效能自然會受到較大的限制，這種架構下的 Replication 的延遲自然就比較長了。

其次，Slave 端的這個複製執行緒從 Master 端獲取 Binary Log 過來之後，需要接著解析這些內容，還原成 Master 端所執行的原始 Query，然後在自身執行。在這個過程中，Master端很可能又已經產生了大量的變化並生成了大量的 Binary Log 資訊。如果在這個階段 Master 端的儲存系統出現了無法修復的故障，那麼在這個階段所產生的所有變更都將永遠的丟失，無法再找回來。這種潛在風險在Slave 端壓力比較大的時候尤其突出，因為如果 Slave 壓力比較大，解析日誌以及應用這些日誌所花費的時間自然就會更長一些，可能丟失的資料也就會更多。

所以，在後期的改造中，新版本的 MySQL 為了儘量減小這個風險，並提高複製的效能，將 Slave 端的複製改為兩個執行緒來完成，也就是前面所提到的 SQL 執行緒和 IO 執行緒。最早提出這個改進方案的是Yahoo!的一位工程師“Jeremy Zawodny”。透過這樣的改造，這樣既在很大程度上解決了效能問題，縮短了非同步的延時時間，同時也減少了潛在的資料丟失量。

當然，即使是換成了現在這樣兩個執行緒來協作處理之後，同樣也還是存在 Slave 資料延時以及資料丟失的可能性的，畢竟這個複製是非同步的。只要資料的更改不是在一個事務中，這些問題都是存在的。

如果要完全避免這些問題，就只能用 MySQL 的 Cluster 來解決了。不過 MySQL的 Cluster 知道筆者寫這部分內容的時候，仍然還是一個記憶體資料庫的解決方案，也就是需要將所有資料包括索引全部都 Load 到記憶體中，這樣就對記憶體的要求就非常大的大，對於一般的大眾化應用來說可實施性並不是太大。MySQL 現在正在不斷改進其 Cluster 的實現，其中非常大的一個改動就是允許資料不用全部 Load 到記憶體中，而僅僅只是索引全部 Load 到記憶體中，我想信在完成該項改造之後的 MySQL Cluster 將會更加受人歡迎，可實施性也會更大。

、Mysql的半同步模式（Semisynchronous Replication）

我們知道在5.5之前，MySQL的複製其實是非同步操作，而不是同步，也就意味著允許主從之間的資料存在一定的延遲，mysql當初這樣設計的目的可能也是基於可用性的考慮，為了保證master不受slave的影響，並且非同步複製使得master處於一種效能最優的狀態：寫完binlog後即可提交而不需要等待slave的操作完成。這樣存在一個隱患，當你使用slave作為備份時，如果master掛掉，那麼會存在部分已提交的事務未能成功傳輸到slave的可能，這就意味著資料丟失！

在MySQL5.5版本中，引入了半同步複製模式（Semi-synchronous Replication）能夠成功（只是相對的）避免上述資料丟失的隱患。在這種模式下：master會等到binlog成功傳送並寫入至少一個slave的relay log之後才會提交，否則一直等待，直到timeout（預設10s）。當出現timeout的時候，master會自動切換半同步為非同步，直到至少有一個slave成功收到併傳送Acknowledge，master會再切換回半同步模式。結合這個新功能，我們可以做到，在允許損失一定的事務吞吐量的前提下來保證同步資料的絕對安全，因為當你設定timeout為一個足夠大的值的情況下，任何提交的資料都會安全抵達slave。

mysql5.5 版本支援半同步複製功能（Semisynchronous Replication），但還不是原生的支援，是透過plugin來支援的，並且預設是沒有安裝這個外掛的。不論是二進位制釋出的，還是自己原始碼編譯的，都會預設生成這個外掛，一個是針對master 的一個是針對slave的，在使用之前需要先安裝這倆plugins。

複製的過濾主要有２種方式：

1、在主伺服器在把事件從進二制日誌中過濾掉，相關的引數是:binlog_do_db和binlog_ignore_db。

2、在從伺服器上把事件從中繼日誌中過濾掉，相關的引數是replicate_*。

複製只能擴充套件讀取，不能擴充套件寫入，對資料進行分割槽可以進行擴充套件寫入。

複製的最佳化：

在mysql複製環境中,有8個引數可以讓我們控制,需要複製或需要忽略不進行復制的DB或table分別為:

下面二項需要在Master上設定：

Binlog_Do_DB:設定哪些資料庫需要記錄Binlog

Binlog_Ignore_DB:設定哪裡資料庫不需要記錄Binlog

優點是Master端的Binlog記錄所帶來的Io量減少，網路IO減少，還會讓slave端的IO執行緒,SQL執行緒減少，從而大幅提高複製效能,

缺點是mysql判斷是否需要複製某個事件不是根據產生該事件的查詢所在的DB,而是根據執行查詢時刻所在的預設資料庫（也就是登入時指定的庫名或執行"use database"中指定的DB）,只有當前預設DB和配置中所設定的DB完全吻合時IO執行緒才會將該事件讀取給slave的IO執行緒.所以,如果在預設DB和設定須要複製的DB不一樣的情況下改變了須要複製的DB中某個Table中的資料,該事件是不會被複制到Slave中去的,這樣就會造成Slave端的資料和Master的資料不一致.同樣,在預設的資料庫下更改了不須要複製的資料庫中的資料,則會被複制到slave端,當slave端並沒有該資料庫時,則會造成複製出錯而停止。

下面六項需要在slave上設定：

Replicate_Do_DB:設定需要複製的資料庫,多個DB用逗號分隔

Replicate_Ignore_DB:設定可以忽略的資料庫.

Replicate_Do_Table:設定需要複製的Table

Replicate_Ignore_Table:設定可以忽略的Table

Replicate_Wild_Do_Table:功能同Replicate_Do_Table,但可以帶萬用字元來進行設定。

Replicate_Wild_Ignore_Table:功能同Replicate_Do_Table,功能同Replicate_Ignore_Table,可以帶萬用字元。

優點是在slave端設定複製過濾機制,可以保證不會出現因為預設的資料庫問題而造成Slave和Master資料不一致或複製出錯的問題.

缺點是效能方面比在Master端差一些.原因在於:不管是否須要複製,事件都會被IO執行緒讀取到Slave端,這樣不僅增加了網路IO量,也給Slave端的IO執行緒增加了Relay Log的寫入量。

注：在實際的生產應用中發現，在mysql5.0以前的版本，mysql的這個過濾設定幾乎是形同虛設，不起作用：不管你在主庫或是從庫上設定了忽略某個資料庫或是表，他依然會進行同步，所以在做5.0以前版本的主從同步時，一定保持主從資料庫的一致性，主上有的庫或是表從上一定要有，否則在同步的過程會出錯。

主庫IP：192.168.1.2

從庫IP：192.168.1.3

新增一個用於主從同步的使用者：

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY ‘1q2w3e4r’;

如果監控mysql主從的話，請加上一個super許可權：

GRANT SUPER, REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '1q2w3e4r';

、主庫的配置

．mysql5.0以下版本的配置

修改主庫mysql配置配置檔案，在[mysqld]段新增以下內容：

server-id = 1

log-bin=/home/mysql/logs/binlog/bin-log

max_binlog_size = 500M

binlog_cache_size = 128K

binlog-do-db = adb

binlog-ignore-db = mysql

log-slave-updates

以上版本的配置

修改主庫mysql配置配置檔案，在[mysqld]段新增以下內容：

server-id = 1

log-bin=/home/mysql/logs/binlog/bin-log

max_binlog_size = 500M

binlog_cache_size = 128K

binlog-do-db = adb

binlog-ignore-db = mysql

log-slave-updates

expire_logs_day=2

binlog_format="MIXED"

各個引數的含義和相關注意項：

server-id = 1 #伺服器標誌號，注意在配置檔案中不能出現多個這樣的標識，如果出現多個的話mysql以第一個為準，一組主從中此標識號不能重複。

log-bin=/home/mysql/logs/binlog/bin-log #開啟bin-log，並指定檔案目錄和檔名字首。

max_binlog_size = 500M #每個bin-log最大大小，當此大小等於500M時會自動生成一個新的日誌檔案。一條記錄不會寫在2個日誌檔案中，所以有時日誌檔案會超過此大小。

binlog_cache_size = 128K #日誌快取大小

binlog-do-db = adb #需要同步的資料庫名字，如果是多個，就以此格式在寫一行即可。

binlog-ignore-db = mysql #不需要同步的資料庫名字，如果是多個，就以此格式在寫一行即可。

log-slave-updates #當Slave從Master資料庫讀取日誌時更新新寫入日誌中，如果只啟動log-bin 而沒有啟動log-slave-updates則Slave只記錄針對自己資料庫操作的更新。

expire_logs_day=2 #設定bin-log日誌檔案儲存的天數，此引數mysql5.0以下版本不支援。

binlog_format="MIXED" #設定bin-log日誌檔案格式為：MIXED，可以防止主鍵重複。

、從庫的配置

以前版本

修改從庫mysql配置配置檔案，在[mysqld]段新增以下內容：

server-id=2

master-host=192.168.1.2

master-user=repl

master-password=1q2w3e4r

master-port=3306

master-connect-retry=30

slave-skip-errors=1062

replicate-do-db = adb

replicate-ignore-db = mysql

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/90618/viewspace-767569/，如需轉載，請註明出處，否則將追究法律責任。

Mysql資料庫主從心得整理

相關文章