如何監控MySQL

首先介紹下 pt-stalk，它是 Percona-Toolkit 工具包中的一個工具，說起 PT 工具包大家都不陌生，平時常用的 pt-query-digest、 pt-online-schema-change 等工具都是出自于這個工具包，這里就不多介紹了。

創新互聯公司是一家以網絡技術公司，為中小企業提供網站維護、成都網站建設、成都網站制作、網站備案、服務器租用、申請域名、軟件開發、小程序制作等企業互聯網相關業務，是一家有著豐富的互聯網運營推廣經驗的科技公司，有著多年的網站建站經驗，致力于幫助中小企業在互聯網讓打出自已的品牌和口碑，讓企業在互聯網上打開一個面向全國乃至全球的業務窗口：建站聯系電話：18980820575

pt-stalk 的主要功能是在出現問題時收集 OS 及 MySQL 的診斷信息，這其中包括：

1. OS 層面的 CPU、IO、內存、磁盤、網絡等信息；

2. MySQL 層面的行鎖等待、會話連接、主從復制，狀態參數等信息。

而且 pt-stalk 是一個 Shell腳本，對于我這種看不懂 perl 的人來說比較友好，腳本里面的監控邏輯與監控命令也可以拿來參考，用于構建自己的監控體系。

三、使用

接著我們來看下如何使用這個工具。

pt-stalk 通常以后臺服務形式監控 MySQL 并等待觸發條件，當觸發條件時收集相關診斷數據。

觸發條件相關的參數有以下幾個：

function：

°?默認為 status，代表監控 SHOW GLOBAL STATUS 的輸出；

°?也可以設置為 processlist，代表監控 show processlist 的輸出；

variable：

°?默認為 Threads_running，代表 監控參數，根據上述監控輸出指定具體的監控項；

threshold：

°?默認為 25，代表 監控閾值，監控參數超過閾值，則滿足觸發條件；

°?監控參數的值非數字時，需要配合 match 參數一起使用，如 processlist 的 state 列；

cycles：

°?默認為 5，表示連續觀察到五次滿足觸發條件時，才觸發收集；

連接參數：host、password、port、socket。

其他一些重要參數：

iterations：該參數指定 pt-stalk 在觸發收集幾次后退出，默認會一直運行。

run-time：觸發收集后，該參數指定收集多長時間的數據，默認 30 秒。

sleep：該參數指定在觸發收集后，sleep 多久后繼續監控，默認 300 秒。

interval：指定狀態參數的檢查頻率，判斷是否需要觸發收集，默認 1 秒。

dest：監控數據存放路徑，默認為 /var/lib/pt-stalk。

retention-time ：監控數據保留時長，默認 30 天。

daemonize：以后臺服務運行，默認不開啟。

log：后臺運行日志，默認為 /var/log/pt-stalk.log。

collect：觸發發生時收集診斷數據，默認開啟。

°?collect-gdb：收集 GDB 堆棧跟蹤，需要 gdb 工具。

°?collect-strace：收集跟蹤數據，需要 strace 工具。

°?collect-tcpdump：收集 tcpdump 數據，需要 tcpdump 工具。

mysql 異步復制和半同步復制

在MySQL5.5之前，MySQL 的復制是異步操作，主庫和從庫的數據之間存在一定的延遲，這樣存在一個隱患:當在主庫上寫入一個事務并提交成功，而從庫尚未得到主庫推送的Binlog日志時，主庫宕機了，例如主庫可能因磁盤損壞、內存故障等造成主庫上該事務Binlog丟失，此時從庫就可能損失這個事務，從而造成主從不一致。

為了解決這個問題, MySQL5.5引人了半同步復制機制。

在MySQL 5.5之前的異步復制時，主庫執行完 Commit提交操作后，在主庫寫入 Binlog日志后即可成功返回客戶端，無需等待Binlog日志傳送給從庫，如圖31-7所示。

而半同步復制時，為了保證主庫上的每一個 Binlog 事務都能夠被可靠的復制到從庫上，主庫在每次事務成功提交時，并不及時反饋給前端應用用戶，而是等待其中一個從庫也接收到 Binlog事務并成功寫入中繼日志后，主庫才返回Commit操作成功給客戶端。 半同步復制保證了事務成功提交后，至少有兩份日志記錄 ，一份在主庫的 Binlog日志上，另一份在至少一個從庫的中繼日志Relay Log 上，從而更進一步保證了數據的完整性。半同步復制的大致流程如圖31-8所示。

半同步復制模式下，假如在圖31-8的步驟1、2、3中的任何一個步驟中主庫宕機，則事務并未提交成功，從庫上也沒有收到事務對應的 Binlog日志，所以主從數據是一致的;

假如在步驟4傳送 Binlog日志到從庫時，從庫宕機或者網絡故障，導致 Binlog并沒有及時地傳送到從庫上，此時主庫上的事務會等待一段時間(時間長短由參數rpl_semi_sync_master_timeout設置的毫秒數決定)，如果 Binlog 在這段時間內都無法成功推送到從庫上，則 MySQL自動調整復制為異步模式，事務正常返回提交結果給客戶端。

半同步復制很大程度上取決于主從庫之間的網絡情況，往返時延RTT 越小決定了從庫的實時性越好。通俗地說，主從庫之間網絡越快，從庫越實時。

半同步模式是作為MySQL5.5的一個插件來實現的，主庫和從庫使用不同的插件。安裝比較簡單，在上一小節異步復制的環境上，安裝半同步復制插件即可。

1、首先,判斷MySQL服務器是否支持動態增加插件:

2、安裝插件

3、可以查看到已安裝的插件

4、在安裝完插件后，半同步復制默認是關閉的，這時需設置參數來開啟半同步

主：

從：

以上的啟動方式是在命令行操作，也可寫在配置文件中。

主：

從：

4、重啟從上的IO線程

從：

如果沒有重啟，則默認還是異步復制，重啟后，slave會在master上注冊為半同步復制的slave角色。這時候，主的error.log中會打印如下信息：

查看半同步是否在運行

主：

從：

這兩個變量常用來監控主從是否運行在半同步復制模式下。至此，MySQL半同步復制搭建完畢~

來做個實驗，觀察半同步狀態參數的變化。

1、在主庫上insert一條記錄，觀察下變化；

Rpl_semi_sync_master_net_waits加1，說明剛才的insert已經發送到從機并且主機還接收到從機的反饋響應；

2、我們將從機mysql停止，再次在主機上進行insert后查看狀態

可以看到，主機進行insert阻塞了10秒才返回結果。Rpl_semi_sync_master_status變為OFF，Rpl_semi_sync_master_no_tx加1，說明這條insert沒有同步到從機。后面再一次執行了insert立馬返回了結果，說明此時已經降級為異步復制；Rpl_semi_sync_master_no_tx也是增加了1；

3、現在恢復啟動從機，再次在主機上進行insert后查看狀態

Rpl_semi_sync_master_status還是OFF，Rpl_semi_sync_master_no_tx又增加了1。說明從庫重啟并不會自動恢復為原來的半同步復制，需要手動操作：

主 SET GLOBAL rpl_semi_sync_master_enabled = 1;

從 SET GLOBAL rpl_semi_sync_slave_enabled = 1; STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

上面是從機重啟后的變化，那么主到從之間的網絡問題呢，我們可以利用防火墻來模擬。

對于全同步復制，當主庫提交事務之后，所有的從庫節點必須收到，APPLY并且提交這些事務，然后主庫線程才能繼續做后續操作。這里面有一個很明顯的缺點就是，主庫完成一個事務的時間被拉長，性能降低。

如何動態監控mysql binlog

有兩種方法，一種方法使用mysql的check table和repair table 的sql語句，另一種方法是使用MySQL提供的多個myisamchk, isamchk數據檢測恢復工具。前者使用起來比較簡便。推薦使用。

1. check table 和 repair table

登陸mysql 終端：

mysql -uxxxxx -p dbname

check table tabTest;

如果出現的結果說Status是OK，則不用修復，如果有Error，可以用：

repair table tabTest;

進行修復，修復之后可以在用check table命令來進行檢查。在新版本的phpMyAdmin里面也可以使用check/repair的功能。

2. myisamchk, isamchk

其中myisamchk適用于MYISAM類型的數據表，而isamchk適用于ISAM類型的數據表。這兩條命令的主要參數相同，一般新的系統都使用MYISAM作為缺省的數據表類型，這里以myisamchk為例子進行說明。當發現某個數據表出現問題時可以使用：

myisamchk tablename.MYI

進行檢測，如果需要修復的話，可以使用：

myisamchk -of tablename.MYI

關于myisamchk的詳細參數說明，可以參見它的使用幫助。需要注意的時在進行修改時必須確保MySQL服務器沒有訪問這個數據表，保險的情況下是最好在進行檢測時把MySQL服務器Shutdown掉。

另外可以把下面的命令放在你的rc.local里面啟動MySQL服務器前：

[ -x /tmp/mysql.sock ] /pathtochk/myisamchk -of /DATA_DIR/*/*.MYI

其中的/tmp/mysql.sock是MySQL監聽的Sock文件位置，對于使用RPM安裝的用戶應該是/var/lib/mysql/mysql.sock，對于使用源碼安裝則是/tmp/mysql.sock可以根據自己的實際情況進行變更，而pathtochk則是myisamchk所在的位置，DATA_DIR是你的MySQL數據庫存放的位置。

需要注意的時，如果你打算把這條命令放在你的rc.local里面，必須確認在執行這條指令時MySQL服務器必須沒有啟動！檢測修復所有數據庫(表)

MySQL數據庫怎么管理？

1 下載官方文檔、2 建議你看你本書：使用：

MySQL必知必會

MySQL Cookbook 第2版

優化：

高性能MySQL（第二版） 復制監控：

高可用MySQL：構建健壯的數據中心 源代碼：

深入理解MySQL

深入理解MySQL核心技術

MySQL技術內幕InnoDB存儲引擎

mysql 如何監控表結構修改

Online DDL 工具：pt-osc

對于 MySQL Online DDL 目前主流的有三種工具：

原生 Online DDL；

pt-osc(online-schema-change)，

gh-ost

本文主要講解 pt-online-schema-change 的使用以及三種工具的簡單對比。

一、原理及限制

1.1 原理

1.?創建一個與原表結構相同的空表，表名是?_new?后綴；

2. 修改步驟 1 創建的空表的表結構；

3. 在原表上加三個觸發器：delete/update/insert，用于 copy 數據過程中，將原表中要執行的語句在新表中執行；

4. 將原表數據以數據塊（chunk）的形式 copy 到新表；

5. rename 原表為 old 表，并把新表 rename 為原表名，然后刪除舊表；

6. 刪除觸發器。

MySQL如何復制表中的一條記錄并插入

1、打開navicat軟件，打開要復制表的數據庫，如下圖所示：

2、點擊上方的“工具-數據傳輸”，如下圖所示：

3、進去之后，左邊選擇的是要復制的表的數據庫，右邊選擇的將表復制到目標數據庫，如下圖所示：

4、打開左邊數據庫對象中的“表”，選擇要復制哪幾張表，點擊開始。

5、點擊開始，會彈出一個框，點擊是，等待一下，出現如下界面，復制成功，點擊“關閉”。

6、可以看到表已經復制到另外一個數據庫上了，如下圖所示：