老張我呢不僅是個金庸迷，還是個三國迷。就是喜歡看後期蜀國諸葛亮與魏國司馬懿之間的鬥智鬥勇。各種錦囊妙計的使用，堪稱經典。針對管理MySQL資料庫這塊，張老師也有很多妙計，今後一一給大家介紹。說回三國，我個人更傾向於蜀國可以統一，但事與願違，很可惜，最終還是魏國司馬炎統一了天下。有人把蜀國失敗的原因歸結於一個扶不起的劉嬋，也有人把原因歸結於天命，更有甚者說是"臥龍鳳雛得其一"才可得天下，而劉備兩人兼得了。現在聽聽很可笑，其實任何人的命運還都是掌握在自己手中的。

我們要學會盡人事知天命，努力去做好每一件事兒，不放過一個小小的細節。尤其是從事資料庫這個領域，更要細緻細心。曾經我的一位老師跟我說過，你要學會把你從事的工作，融入到自己的血液當中去。只有真正地愛上它，才能去用心去研究它！

每次老張寫博之前，都喜歡說一些心靈雞湯，不愛聽的老鐵們，也希望你們見諒！其實就是希望大家能夠用心去做每一件事兒，不管在哪個行業，你早晚會成功。

老張的 MySQL 網路課程部分也在51CTO學院正式上線了，想學習的同學們可以去訪問

有任何問題都可以及時跟老師溝通。

今兒給大家分享一篇，關於MySQL DBA必備工具的使用。可以方便幫助我們管理我們的資料庫，讓我們的工作更高效。

這款工具是 MySQL 一個重要分支 percona 的，名稱叫做 percona-toolkit（一把鋒利的瑞士軍刀），它呢是一組命令的集合。今兒給大家介紹幾個我們在生產環境中最長用到的。

工具包的下載地址：

安裝過程很簡單，先解壓：

tar -zxvf percona-toolkit-3.0.3_x86_64.tar.gz

由於是二進位制的包，解壓完可以直接進到percona-toolkit-3.0.3/bin目錄下使用。

錦囊妙計一：

pt-online-schema-change

功能可以線上整理表結構，收集碎片，給大表新增欄位和索引。避免出現鎖表導致阻塞讀寫的操作。針對 MySQL 5.7 版本，就可以不需要使用這個命令，直接線上 online DDL 就可以了。

展現過程如下：

由於是測試環境，就不建立一張資料量特大的表，主要讓大家理解這個過程。

這是表裡面資料的情況和表結構

mysql> select count(*) from su;

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

| count(*) |

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

| 100000 |

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

1 row in set (0.03 sec)

mysql> desc su;

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

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

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| c1 | int(11) | NO | | 0 | |

| c2 | int(11) | NO | | 0 | |

| c3 | int(11) | NO | | 0 | |

| c4 | int(11) | NO | | 0 | |

| c6 | varchar(200) | NO | | | |

線上增加欄位的過程：

[root@node3 bin]# ./pt-online-schema-change --user=root --password=root123

--host=localhost --alter="ADD COLUMN city_id INT" D=test,t=su --execute

No slaves found. See --recursion-method if host node3 has slaves.

Not checking slave lag because no slaves were found and --check-slave-lag was not specified.

Operation, tries, wait:

analyze_table, 10, 1

copy_rows, 10, 0.25

create_triggers, 10, 1

drop_triggers, 10, 1

swap_tables, 10, 1

update_foreign_keys, 10, 1

Altering `test`.`su`...

Creating new table...

Created new table test._su_new OK.

Altering new table...

Altered `test`.`_su_new` OK.

2017-08-10T14:53:59 Creating triggers...

2017-08-10T14:53:59 Created triggers OK.

2017-08-10T14:53:59 Copying approximately 100163 rows...

2017-08-10T14:54:00 Copied rows OK.

2017-08-10T14:54:00 Analyzing new table...

2017-08-10T14:54:00 Swapping tables...

2017-08-10T14:54:00 Swapped original and new tables OK.

2017-08-10T14:54:00 Dropping old table...

2017-08-10T14:54:00 Dropped old table `test`.`_su_old` OK.

2017-08-10T14:54:00 Dropping triggers...

2017-08-10T14:54:00 Dropped triggers OK.

Successfully altered `test`.`su`.

檢視結果新增了一個 city_id 的欄位：

mysql> desc su;

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

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

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| c1 | int(11) | NO | | 0 | |

| c2 | int(11) | NO | | 0 | |

| c3 | int(11) | NO | | 0 | |

| c4 | int(11) | NO | | 0 | |

| c6 | varchar(200) | NO | | | |

| city_id | int(11) | YES | | NULL | |

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

錦囊妙計二：

pt-query-digest

功能：現在捕獲線上TOP 10 慢 sql 語句。

大家都知道資料庫大多數的效能問題是 sql 語句造成的，所以我們要抓住它們這些犯罪分子。及時做相關的最佳化處理。

展現過程如下：

可以根據時間間隔，來取樣慢 sql 語句。since 是可以調整的 sql 語句

[root@node3 bin]# ./pt-query-digest --since=24h /data/mysql/slow.log > 1.log

檢視 sql 報告，總結慢語句有哪些，並可以看針對時間的消耗。

如下只是部分報告過程

cat 1.log

# Profile

# Rank Query ID Response time Calls R/Call V/M Item

# ==== ================== ============= ===== ======= ===== ==============

# 1 0x040ADBE3A1EED0A2 16.8901 87.2% 1 16.8901 0.00 CALL insert_su

# 2 0x8E44F4ED46297D4C 1.3013 6.7% 3 0.4338 0.18 INSERT SELECT test._su_new test.su

# 3 0x12E7CAFEA3145EEF 0.7431 3.8% 1 0.7431 0.00 DELETE su

# MISC 0xMISC 0.4434 2.3% 3 0.1478 0.0 <3ITEMS>

# Query 1: 0 QPS, 0x concurrency, ID 0x040ADBE3A1EED0A2 at byte 19060 ____

# Scores: V/M = 0.00

# Time range: all events occurred at 2017-08-02 12:12:07

# Attribute pct total min max avg 95% stddev median

# ============ === ======= ======= ======= ======= ======= ======= =======

# Count 2 1

# Exec time 47 18s 18s 18s 18s 18s 0 18s

# Lock time 0 103us 103us 103us 103us 103us 0 103us

# Rows sent 0 0 0 0 0 0 0 0

# Rows examine 0 0 0 0 0 0 0 0

# Query size 0 21 21 21 21 21 0 21

# String:

# Databases test

# Hosts localhost

# Users root

# Query_time distribution

# 1us

# 10us

# 100us

# 1ms

# 10ms

# 100ms

# 1s

# 10s+ ################################################################

call insert_su(50000)G

可以看到報告中，列舉出了一些sql語句響應時間佔比情況，和sql語句的執行時間情況。方便我們可以很直觀的觀察哪些語句有問題。（這裡只列舉了一條sql）

錦囊妙計三：

pt-heartbeat

功能監控主從延遲。監控從庫落後主庫大概多少時間。

環境介紹：192.168.56.132主庫，192.168.56.133從庫

操作如下：

在主庫上執行：

[root@node3 bin]# ./pt-heartbeat --database test --update

--create-table --daemonize -uroot -proot123

test為我監控同步的庫，在該庫下建立一張監控表heartbeat，後臺程式會時時更新這張表。

在從庫上執行監控主從同步延遲時間的語句：

master-server-id是主庫的server-id, -h（主庫ip）

[root@node4 bin]# ./pt-heartbeat --master-server-id=1323306

--monitor --database test -uzs -p123456 -h 192.168.56.132

0.00s [ 0.00s, 0.00s, 0.00s ]

時間是0s，目前沒有延遲的出現。

錦囊妙計四：

pt-table-checksum

功能檢查主從複製一致性

原理：在主上執行檢查語句去檢查 mysql主從複製的一致性，生成 replace 語句，然後透過複製傳遞到從庫，再透過update 更新 master_src 的值。最後透過檢測從上 this_src 和 master_src 的

值從而判斷複製是否一致。

比較test庫的差異情況，在主庫上面執行：

[root@node3 bin]# ./pt-table-checksum --no-check-binlog-format --nocheck-replication-filters

--databases=test --replicate=test.checksums --host=192.168.56.132 -uzs -p123456

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

08-10T16:01:02 0 0 1 1 0 0.013 test.heartbeat

08-10T16:01:02 0 0 0 1 0 0.015 test.su

08-10T16:01:02 0 0 0 1 0 0.011 test.t

可見diff都為0，證明主從的test庫沒有差異情況。

比較test庫哪些表有差異(需要新增replicate-check-only)，在主庫上面執行：

[root@node3 bin]# ./pt-table-checksum --no-check-binlog-format

--nocheck-replication-filters --databases=test --replicate=test.checksums

--replicate-check-only --host=192.168.56.132 -uzs -p123456

Differences on node4

TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY

test.t 1 1 1

可見test庫下面t這張表主從資料不一致。

錦囊妙計五：

pt-slave-restart

功能：監控主從錯誤，並嘗試重啟MySQL主從

注意事項：跳過錯誤這個命令，解決從庫多資料的現象（錯誤程式碼1062）。如果從庫少資料，還跳過錯誤，就不能從根兒上解決主從同步的問題了（錯誤程式碼1032），就需要先找到缺少的資料是什麼了，如果缺少的特別多，建議重新搭建主從環境。

從庫出現1062的錯誤：

Slave_IO_Running: Yes

Slave_SQL_Running: No

Last_Errno: 1062

Last_Error: Could not execute Write_rows event on table test.t;

Duplicate entry '1' for key 'PRIMARY',

Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;

the event's master log mysql-bin.000006, end_log_pos 757482

需要在從庫上面執行：

[root@node4 bin]# ./pt-slave-restart -uroot -proot123 --error-numbers=1062

2017-08-10T16:28:12 p=...,u=root node4-relay-bin.000002 751437 1062

跳過錯誤之後，檢查主從結果：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

同步狀態又恢復一致了。

錦囊妙計六：

pt-ioprofile

功能：方便定位IO問題，可透過IO吞吐量來定位。

[root@node3 bin]# ./pt-ioprofile

Thu Aug 10 16:33:47 CST 2017

Tracing process ID 3907

total read pwrite write fsync filename

13.949355 0.839006 0.000000 0.286556 12.823793 /data/mysql/mysql-bin.000006

7.454844 0.000000 2.913702 0.000000 4.541142 /data/mysql/ib_logfile0

0.000193 0.000000 0.000000 0.000193 0.000000 /data/mysql/slow.log

read：從檔案中讀出資料。要讀取的檔案用檔案描述符標識，資料讀入一個事先定義好的緩衝區。

write：把緩衝區的資料寫入檔案中。

pread：由於lseek和read呼叫之間，核心可能會臨時掛起程式，所以對同步問題造成了問題，

呼叫pread相當於順序呼叫了lseek和read，這兩個操作相當於一個捆綁的原子操作。

pwrite：由於lseek和write呼叫之間，核心可能會臨時掛起程式，所以對同步問題造成了問題，

呼叫pwrite相當於順序呼叫了lseek 和write，這兩個操作相當於一個捆綁的原子操作。

fsync：確保檔案所有已修改的內容已經正確同步到硬碟上，該呼叫會阻塞等待直到裝置報告IO完成。

filename：與磁碟互動的檔名稱

透過這個報告我們可以看到，哪個檔案佔用IO的時間比較多，跟磁碟互動最為繁忙，便於鎖定IO問題。

因為這個工具集命令很多，今兒先給大家介紹這些比較常用的，其他的一些大家感興趣可以私下去研究下。

官方地址：

最後老張希望大家都可以成為"臥龍或者鳳雛"，得你們其中任何的一位，公司的資料庫無憂矣！！

MySQL DBA必備工具使用的6大錦囊妙計

相關文章