Mysql資料庫主從心得整理

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

  一、mysql主從的原理

  1、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」 參數選項,或者在 f 配置文件中的 mysqld 參數組([mysqld]標識後的參數部分)增加 「log-bin」 參數項。

  2、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,所以兩端的數據是完全一樣的。

  3、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=

  /*!*/;

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

  4、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 將會更加受人歡迎,可實施性也會更大。

  5、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。

  二、Mysql主從複製的過濾

  複製的過濾主要有2種方式:

  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以前版本的主從同步時,一定保持主從資料庫的一致性,主上有的庫或是表從上一定要有,否則在同步的過程會出錯。

  三、Mysql主從同步的配置

  主庫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;

  1、主庫的配置

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

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

  expire_logs_day=2

  binlog_format="MIXED"

  1.3.各個參數的含義和相關注意項:

  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,可以防止主鍵重複。

  2、從庫的配置

  2.1.mysql5.1.7以前版本

  修改從庫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

  slave-skip-errors=1007,1008,1053,1062,1213,1158,1159

  master-info-file = /home/mysql/logs/

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

  relay-log-index = /home/mysql/logs/relay-bin.index

  relay-log-info-file = /home/mysql/logs/

  如果修改了連接主庫相關信息,重啟之前一定要刪除文件,否則重啟之後由於連接信息改變從庫而不會自動連接主庫,造成同步失敗。此文件是保存連接主庫信息的。

  2.2.mysql5.1.7以後版本

  Mysql5.1.7版本在叢庫上面的配置很少,主要是採用了新的同步信息記錄方式,他不在支持在配置文件中配置連接主庫的相關信息,而是把連接等相關信息記錄在master-info-file = /home/mysql/logs/文件中,如果入庫變了,直接在mysql命令行執行連接信息的改變即可生效,比較靈活了,而不用去重啟mysql。修改從庫mysql配置配置文件,在[mysqld]段添加以下內容:

  slave-skip-errors=1007,1008,1053,1062,1213,1158,1159

  2.3. 各個參數的含義和相關注意項

  這裡只講一下2個參數,其他全部是從庫連接主庫的信息和中間日誌relay-log的設置。

  master-connect-retry=30 #這個選項控制重試間隔,默認為60秒。

  slave-skip-errors=1007,1008,1053,1062,1213,1158,1159 #這個是在同步過程中忽略掉的錯誤,這些錯誤不會影響數據的完整性,有事經常出現的錯誤,一般設置忽略。其中1062為主鍵重複錯誤。

  3、實現主從同步

  3.1.實現資料庫的統一

  檢查主從資料庫的配置文件,查看是否已正確配置。首次實現 同步要備份主庫上需要同步的資料庫,然後完整的導入到從庫中。註:mysql5.0之前的版本涉及到mysql本身複製過濾存在問題,需要把所有的資料庫都備份導入到叢庫,保持。

  3.2.查看並記錄主庫bin-log信息

  進入主庫mysql中,執行:show master status;顯示信息如下:

  mysql> show master status;

  +-------------+----------+--------------+------------------+

  | File | Position | Binlog_do_db | Binlog_ignore_db |

  +-------------+----------+--------------+------------------+

  | bin-log.003 | 4 | adb | mysql |

  +-------------+----------+--------------+------------------+

  1 row in set (0.00 sec)

  記錄File 和Position信息;

  3.3.在從庫上執行同步語句

  進入mysql,執行以下語句:

  slave stop;

  change master to

  master_host=192.168.1.2,

  master_user=repl,

  master_password=1q2w3e4r,

  master_port=3306,

  master_log_file=bin-log.003,

  master_log_pos=4;

  slave start;

  3.4.查看主從同步狀態

  進入mysql,執行show slave statusG;顯示如下(mysql版本不同查詢的結果不同,但是重要的指標還是一樣的):

  重要的指標為:

  Slave_IO_Running: Yes

  Slave_SQL_Running: Yes

  Master_Log_File: bin-log.003

  Relay_Master_Log_File: bin-log.003

  Read_Master_Log_Pos: 4

  Exec_master_log_pos: 4

  Seconds_Behind_Master: 0(5.0之前版本沒有這個選項)

  以上選項是兩兩對應的,只要結果是一致的,就說明主從同步成功。

  3.5.同步中的常見的錯誤和處理

  1、現象:在從庫上面show slave statusG;出現下列情況,

  Slave_IO_Running: Yes

  Slave_SQL_Running: No

  Seconds_Behind_Master: NULL

  原因:

  a.程序可能在slave上進行了寫操作;

  b.也可能是slave機器重起後,事務回滾造成的;

  c.有可能是在同步過程中遇到某種錯誤,這個會在查看從庫中狀態時看到錯誤提示,最少見的就是主鍵重複1062的錯誤。

  解決方法:

  進入master

  mysql> show master status;

  +----------------------+----------+--------------+------------------+

  | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

  +----------------------+----------+--------------+------------------+

  | mysql-bin.000040 | 324 |adb | mysql|

  +----------------------+----------+--------------+------------------+

  然後到slave伺服器上執行手動同步

  slave stop;

  change master to

  master_host=10.14.0.140,

  master_user=repl,

  master_password=1q2w3e4r,

  master_port=3306,

  master_log_file=mysql-bin.000040,

  master_log_pos=324;

  slave start;

  show slave statusG;

  2、現象:從資料庫無法同步,show slave status顯示:

  Slave_IO_Running: No

  Slave_SQL_Running: Yes

  Seconds_Behind_Master: NULL

  解決:首先查看資料庫的err日誌,查看是什麼錯誤提示,看從庫連接主庫的IP、用戶、密碼等相關信息是否有誤,如果有誤,重新執行同步;如果確認無誤,重啟主資料庫。

  mysql> show master status;

  +------------------+----------+--------------+------------------+

  | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

  +------------------+----------+--------------+------------------+

  | mysql-bin.000001 | 98 | adb| mysql|

  +------------------+----------+--------------+------------------+

  進入從庫mysql,執行:

  slave stop;

  change master to Master_Log_File=mysql-bin.000001,Master_Log_Pos=98;

  slave start;

  或是這樣:

  stop slave;

  set global sql_slave_skip_counter =1;

  start slave;

  這個現象主要是master資料庫存在問題,由於連接主庫信息錯誤、主庫資料庫掛掉如果說常見錯等原因引起的,我在實際的操作中先重啟master後重啟slave即可解決這問題,出現此問題,必須要要重啟master資料庫。

  四、mysql主主和主主集群

  1、mysql主主的實現

  在實際的生產應用中,為了在主庫出現崩潰或是主伺服器出現嚴重故障時快速的恢復業務,會直接切換到從庫上,當主庫故障處理完成後讓他直接作為叢庫來運行,此時主主就是一個不錯的選擇。

  五、mysql主從的監控

  在mysql主從的應用中,只要進行了合理設置,基本上不會出現問題,但是對他的監控是必不可少的,以免由於真的出現問題又不知道而造成不必要的數據損失。

  1、mysql主從監控的主要思路

  Mysql主從的監控,其主要是監控從庫上的一些重要參數:

  Slave_IO_Running: Yes

  Slave_SQL_Running: Yes

  Master_Log_File: bin-log.003

  Relay_Master_Log_File: bin-log.003

  Read_Master_Log_Pos: 4

  Exec_master_log_pos: 4

  Seconds_Behind_Master: 0(5.0之前版本沒有這個選項)

  通過以上的參數可以反映出主庫和從庫狀態是否正常,從庫是否落後於主庫等。值得一提的是在mysql5.0以前的版本,Slave_IO_Running這個狀態指標不可靠,會在主庫直接掛掉的情況下不會變成NO,Seconds_Behind_Master參數也不存在。監控以上參數即可監控mysql主從。

  2、mysql主從監控的實現

  不管mysql是那個版本,其中的從庫上的Exec_master_log_pos、Exec_master_log_pos;主庫上的 Master上的Log_File,Position,這四個參數可以判斷出當前主從的狀態。以下是適用於mysql所有版本的主從監控shell腳本:

  #/bin/sh user=repl passwd=123415 master_ip="192.168.1.2" log="/data3/check_repl.log" value() { master=`/usr/local/mysql/bin/mysql -u$user -p$passwd -h$master_ip -e "show master statusG;"|egrep "File|Position"` #mysql 4.0 slave=`/usr/local/mysql/bin/mysql -u$user -p$passwd -h127.0.0.1 -e "show slave statusG;"|egrep "Relay_Master_Log_File|Exec_master_log_pos"` #mysql 5.0 #slave=`mysql -u$user -p$passwd -e "show slave statusG;"|egrep "Relay_Master_Log_File|Exec_Master_Log_Pos"` #取主庫上的bin-log號及寫入的當前日誌位置 Master_Log=`echo $master |awk {print $2}|awk -F "." {print $2}` Master_Log_Pos=`echo $master |awk {print $4}` #取從庫上當前同步主庫的位置 Relay_Master_Log_File=`echo $slave |awk {print $2}|awk -F "." {print $2}` Exec_Master_Log_Pos=`echo $slave |awk {print $4}` echo "Master_Log:"$Master_Log>>$log echo "Master_Log_Pos:"$Master_Log_Pos>>$log echo "Relay_Master_Log_File:"$Relay_Master_Log_File>>$log echo "Exec_Master_Log_Pos:"$Exec_Master_Log_Pos>>$log } for((i=1;i

  在mysql5.0以後的版本,mysql主從已經相當的成熟了,可以只監控Slave_IO_Running,Slave_SQL_Running,Seconds_Behind_Master狀態就可以了,這裡不再做說明。

原文詳情地址:roncoo.com/article/deta


推薦閱讀:

爬蟲的資料庫選型,redis, leveldb, mongodb, mysql 選哪個?
在 Docker 中使用 mysql 的一些技巧
在DB操作中,如何避免第一個transaction的修改被第二個transaction覆蓋?
MySQL 和 PostgreSQL 相比,對 JSON 的支持如何?
一條MySQL的select語句,為什麼性能會差別這麼大?

TAG:IT行業 | MySQL | 資料庫 |