老張我呢不僅是個(gè)金庸迷，還是個(gè)三國(guó)迷。就是喜歡看后期蜀國(guó)諸葛亮與魏國(guó)司馬懿之間的斗智斗勇。各種錦囊妙計(jì)的使用，堪稱(chēng)經(jīng)典。針對(duì)管理MySQL數(shù)據(jù)庫(kù)這塊，張老師也有很多妙計(jì)，今后一一給大家介紹。說(shuō)回三國(guó)，我個(gè)人更傾向于蜀國(guó)可以統(tǒng)一，但事與愿違，很可惜，最終還是魏國(guó)司馬炎統(tǒng)一了天下。有人把蜀國(guó)失敗的原因歸結(jié)于一個(gè)扶不起的劉嬋，也有人把原因歸結(jié)于天命，更有甚者說(shuō)是"臥龍鳳雛得其一"才可得天下，而劉備兩人兼得了?，F(xiàn)在聽(tīng)聽(tīng)很可笑，其實(shí)任何人的命運(yùn)還都是掌握在自己手中的。
創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè),香坊企業(yè)網(wǎng)站建設(shè),香坊品牌網(wǎng)站建設(shè),網(wǎng)站定制,香坊網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營(yíng)銷(xiāo),網(wǎng)絡(luò)優(yōu)化,香坊網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力?？沙浞譂M(mǎn)足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專(zhuān)業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶(hù)成長(zhǎng)自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。
我們要學(xué)會(huì)盡人事知天命，努力去做好每一件事兒，不放過(guò)一個(gè)小小的細(xì)節(jié)。尤其是從事數(shù)據(jù)庫(kù)這個(gè)領(lǐng)域，更要細(xì)致細(xì)心。曾經(jīng)我的一位老師跟我說(shuō)過(guò)，你要學(xué)會(huì)把你從事的工作，融入到自己的血液當(dāng)中去。只有真正地愛(ài)上它，才能去用心去研究它！
每次老張寫(xiě)博之前，都喜歡說(shuō)一些心靈雞湯，不愛(ài)聽(tīng)的老鐵們，也希望你們見(jiàn)諒！其實(shí)就是希望大家能夠用心去做每一件事兒，不管在哪個(gè)行業(yè)，你早晚會(huì)成功。
老張的 MySQL 網(wǎng)絡(luò)課程部分也在51CTO學(xué)院正式上線(xiàn)了，想學(xué)習(xí)的同學(xué)們可以去訪(fǎng)問(wèn)
有任何問(wèn)題都可以及時(shí)跟老師溝通。
今兒給大家分享一篇，關(guān)于MySQL DBA必備工具的使用?？梢苑奖銕椭覀児芾砦覀兊臄?shù)據(jù)庫(kù)，讓我們的工作更高效。
這款工具是 MySQL 一個(gè)重要分支 percona 的，名稱(chēng)叫做 percona-toolkit（一把鋒利的瑞士×××），它呢是一組命令的集合。今兒給大家介紹幾個(gè)我們?cè)谏a(chǎn)環(huán)境中最長(zhǎng)用到的。
工具包的下載地址：https://www.percona.com/downloads/percona-toolkit/LATEST/
安裝過(guò)程很簡(jiǎn)單，先解壓：
tar -zxvf percona-toolkit-3.0.3_x86_64.tar.gz
由于是二進(jìn)制的包，解壓完可以直接進(jìn)到percona-toolkit-3.0.3/bin目錄下使用。
錦囊妙計(jì)一：
pt-online-schema-change
功能可以在線(xiàn)整理表結(jié)構(gòu)，收集碎片，給大表添加字段和索引。避免出現(xiàn)鎖表導(dǎo)致阻塞讀寫(xiě)的操作。針對(duì) MySQL 5.7 版本，就可以不需要使用這個(gè)命令，直接在線(xiàn) online DDL 就可以了。
展現(xiàn)過(guò)程如下：
由于是測(cè)試環(huán)境，就不創(chuàng)建一張數(shù)據(jù)量特大的表，主要讓大家理解這個(gè)過(guò)程。
這是表里面數(shù)據(jù)的情況和表結(jié)構(gòu)
mysql> select count(*) from su; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0.03 sec) mysql> desc su; +-------+------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+-------------------+-----------------------------+ | 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 | | | c5 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | c6 | varchar(200) | NO | | | |
在線(xiàn)增加字段的過(guò)程：
[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`.
查看結(jié)果新增了一個(gè) city_id 的字段：
mysql> desc su; +---------+------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+-------------------+-----------------------------+ | 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 | | | c5 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | c6 | varchar(200) | NO | | | | | city_id | int(11) | YES | | NULL | | +---------+------------------+------+-----+-------------------+-----------------------------+
錦囊妙計(jì)二：
pt-query-digest
功能：現(xiàn)在捕獲線(xiàn)上TOP 10 慢 sql 語(yǔ)句。
大家都知道數(shù)據(jù)庫(kù)大多數(shù)的性能問(wèn)題是 sql 語(yǔ)句造成的，所以我們要抓住它們這些犯罪分子。及時(shí)做相關(guān)的優(yōu)化處理。
展現(xiàn)過(guò)程如下：
可以根據(jù)時(shí)間間隔，來(lái)采樣慢 sql 語(yǔ)句。since 是可以調(diào)整的 sql 語(yǔ)句
[root@node3 bin]# ./pt-query-digest --since=24h /data/mysql/slow.log > 1.log
查看 sql 報(bào)告，總結(jié)慢語(yǔ)句有哪些，并可以看針對(duì)時(shí)間的消耗。
如下只是部分報(bào)告過(guò)程
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
可以看到報(bào)告中，列舉出了一些sql語(yǔ)句響應(yīng)時(shí)間占比情況，和sql語(yǔ)句的執(zhí)行時(shí)間情況。方便我們可以很直觀(guān)的觀(guān)察哪些語(yǔ)句有問(wèn)題。（這里只列舉了一條sql）
錦囊妙計(jì)三：
pt-heartbeat
功能監(jiān)控主從延遲。監(jiān)控從庫(kù)落后主庫(kù)大概多少時(shí)間。
環(huán)境介紹：192.168.56.132主庫(kù)，192.168.56.133從庫(kù)
操作如下：
在主庫(kù)上執(zhí)行：
[root@node3 bin]# ./pt-heartbeat --database test --update --create-table --daemonize -uroot -proot123
test為我監(jiān)控同步的庫(kù)，在該庫(kù)下創(chuàng)建一張監(jiān)控表heartbeat，后臺(tái)進(jìn)程會(huì)時(shí)時(shí)更新這張表。
在從庫(kù)上執(zhí)行監(jiān)控主從同步延遲時(shí)間的語(yǔ)句：
master-server-id是主庫(kù)的server-id, -h（主庫(kù)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 ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ]
時(shí)間是0s，目前沒(méi)有延遲的出現(xiàn)。
錦囊妙計(jì)四：
pt-table-checksum
功能檢查主從復(fù)制一致性
原理：在主上執(zhí)行檢查語(yǔ)句去檢查 mysql主從復(fù)制的一致性，生成 replace 語(yǔ)句，然后通過(guò)復(fù)制傳遞到從庫(kù)，再通過(guò)update 更新 master_src 的值。最后通過(guò)檢測(cè)從上 this_src 和 master_src 的
值從而判斷復(fù)制是否一致。
比較test庫(kù)的差異情況，在主庫(kù)上面執(zhí)行：
[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
可見(jiàn)diff都為0，證明主從的test庫(kù)沒(méi)有差異情況。
比較test庫(kù)哪些表有差異(需要添加replicate-check-only)，在主庫(kù)上面執(zhí)行：
[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
可見(jiàn)test庫(kù)下面t這張表主從數(shù)據(jù)不一致。
錦囊妙計(jì)五：
pt-slave-restart
功能：監(jiān)控主從錯(cuò)誤，并嘗試重啟MySQL主從
注意事項(xiàng)：跳過(guò)錯(cuò)誤這個(gè)命令，解決從庫(kù)多數(shù)據(jù)的現(xiàn)象（錯(cuò)誤代碼1062）。如果從庫(kù)少數(shù)據(jù)，還跳過(guò)錯(cuò)誤，就不能從根兒上解決主從同步的問(wèn)題了（錯(cuò)誤代碼1032），就需要先找到缺少的數(shù)據(jù)是什么了，如果缺少的特別多，建議重新搭建主從環(huán)境。
從庫(kù)出現(xiàn)1062的錯(cuò)誤：
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
需要在從庫(kù)上面執(zhí)行：
[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
跳過(guò)錯(cuò)誤之后，檢查主從結(jié)果：
Slave_IO_Running: Yes Slave_SQL_Running: Yes
同步狀態(tài)又恢復(fù)一致了。
錦囊妙計(jì)六：
pt-ioprofile
功能：方便定位IO問(wèn)題，可通過(guò)IO吞吐量來(lái)定位。
[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：從文件中讀出數(shù)據(jù)。要讀取的文件用文件描述符標(biāo)識(shí)，數(shù)據(jù)讀入一個(gè)事先定義好的緩沖區(qū)。 write：把緩沖區(qū)的數(shù)據(jù)寫(xiě)入文件中。 pread：由于lseek和read調(diào)用之間，內(nèi)核可能會(huì)臨時(shí)掛起進(jìn)程，所以對(duì)同步問(wèn)題造成了問(wèn)題， 調(diào)用pread相當(dāng)于順序調(diào)用了lseek和read，這兩個(gè)操作相當(dāng)于一個(gè)捆綁的原子操作。 pwrite：由于lseek和write調(diào)用之間，內(nèi)核可能會(huì)臨時(shí)掛起進(jìn)程，所以對(duì)同步問(wèn)題造成了問(wèn)題， 調(diào)用pwrite相當(dāng)于順序調(diào)用了lseek 和write，這兩個(gè)操作相當(dāng)于一個(gè)捆綁的原子操作。 fsync：確保文件所有已修改的內(nèi)容已經(jīng)正確同步到硬盤(pán)上，該調(diào)用會(huì)阻塞等待直到設(shè)備報(bào)告IO完成。 filename：與磁盤(pán)交互的文件名稱(chēng)
通過(guò)這個(gè)報(bào)告我們可以看到，哪個(gè)文件占用IO的時(shí)間比較多，跟磁盤(pán)交互最為繁忙，便于鎖定IO問(wèn)題。
因?yàn)檫@個(gè)工具集命令很多，今兒先給大家介紹這些比較常用的，其他的一些大家感興趣可以私下去研究下。
官方地址：https://www.percona.com/doc/percona-toolkit/LATEST/index.html
最后老張希望大家都可以成為"臥龍或者鳳雛"，得你們其中任何的一位，公司的數(shù)據(jù)庫(kù)無(wú)憂(yōu)矣！！
重慶分公司
重慶分公司
