MySQL基礎運維——percona-toolkit運維工具

dapolan發表於2022-03-08
MySql運維

一、percona-toolkit工具

percona-toolkit是一組高階命令列工具的集合，用來執行各種通過手工執行非常複雜和麻煩的 mysql 任務和系統任務，這些任務包括：

  • 檢查 master 和 slave 資料的一致性

  • 有效地對記錄進行歸檔

  • 查詢重複的索引

  • 對伺服器資訊進行彙總

  • 分析來自日誌和 tcpdump 的查詢

  • 當系統出問題的時候收集重要的系統資訊

percona-toolkit 源自 Maatkit 和 Aspersa 工具，這兩個工具是管理 mysql 的最有名的工具，現在 Maatkit 工具已經不維護了，請大家還是使用 percona-toolkit 吧！ 這些工具主要包括開發、效能、配置、監控、複製、系統、實用六大類，作為一個優秀的 DBA，裡面有的工具非常有用，如果能掌握並加以靈活應用，將能極大的提高工作效率。

1、percona-toolkit軟體包下載

 

2、 percona-toolkit軟體安裝

軟體安裝存在三種安裝方式，分別為RPM包、二進位制包、原始碼安裝。

需安裝依賴包 

yum install perl-DBI perl-DBD-MySQL

1.RPM包安裝方式

rpm –ivh percona-toolkit-3.1.0-2.el7.x86_64.rpm

2.二進位制包安裝 

tar –xvf percona-toolkit-3.0.11_x86_64.tar.gz
cd percona-toolkit-3.0.11
perl Makefile.PL
make
make install

下面介紹它自帶的一些工具

2、pt-kill工具

1、工具介紹

pt-kill 是一個簡單而且很實用的查殺mysql執行緒和查詢的工具，主要是為了防止一些大/複雜/長時間查詢佔用資料庫及系統資源，而對線上業務造成影響的情況。

2、常用引數

常用引數

含義

--user

使用者

--password

密碼

--port

埠

--host

主機

--socket

本地套接字

--match-command

匹配狀態

--match-info

匹配資訊

--match-state 

匹配宣告

--ignore-host/--match-host

匹配主機

--ignore-db/--match-db

匹配資料庫

--ignore-user/--match-user

匹配使用者

--kill

殺掉連線並且退出

--kill-query

只殺掉連線執行的語句，但是執行緒不會被終止

--print

列印滿足條件的語句

--busy-time

SQL執行時間的執行緒

--idle-time

sleep時間的連線執行緒，必須在--match-command sleep時才有效

--interval

query的間隔

--victim   

oldest|all|all-but-oldest 針對範圍

--daemonize

是否放到後臺執行

--interval

執行頻率(s=seconds, m=minutes, h=hours, d=days)

--log-dsn D=test,t=pk_log 

記錄資訊到表中

3、實際案例

以下均省略連線引數！

3.1 殺執行時間超過10s的SQL語句但保留執行緒

pt-kill --busy-time=10 --victims all --print --kill-query

3.2 查某IP來源且執行時間超過20s的會話 

pt-kill --match-host "10.100.50.29" --print --victims all --busy-time=20s

3.3 每10秒檢查一次，狀態為sleep的程式就給幹掉 

pt-kill --match-command Sleep --victims all --interval 10 --kill --print

3.4 查殺select大於10s的會話 

pt-kill --match-info "select|Select|SELECT"  --print  --victims all --busy-time 20s

3.5 查狀態為sleep的程式且時間超過1h 

pt-kill --match-command Sleep --victims all  --idle-time=1h --print

3.6 查殺訪問某使用者的會話 

pt-kill --match-user "test" --victims all  --print --kill

3.7 將執行記錄記錄到表中 

pt-kill --log-dsn D=test,t=pk_log --create-log-table --host=192.168.186.11 --user=root --password=mysql --port=3306 --busy-time=10 --print --kill-query

注：

指定庫必須存在否則會報錯！

test庫中pk_log表，若不存在先建立表--create-log-table

3.8 殺掉正在進行filesort的sql

pt-kill --match-command Query --match-state “Sorting result” --busy-time 10  --interval 10 --run-time 1  --print --kill --victims all

3.9 後臺執行殺掉正在進行filesort的sql 

pt-kill --match-command Query --match-state “Sorting result” --busy-time 10  --interval 10 --run-time 1  --print --kill --victims all --daemonize

注：

--daemonize 會將該命令放到後臺不斷執行。

3.10 篩選檔案中processlist

mysql -e "SHOW PROCESSLIST" > proclist.txt
pt-kill --test-matching proclist.txt --busy-time 60 --print

3、pt-query-digest工具

1、工具介紹

分析查詢執行日誌，併產生一個查詢報告，為 MySQL、 PostgreSQL、memcached 過濾、重放或者轉換語句。

2、常用引數

常用引數

含義

--create-review-table

當使用--review引數把分析結果輸出到表中時，如果沒有表就自動建立

--create-history-table

當使用--history引數把分析結果輸出到表中時，如果沒有表就自動建立

--filter

對輸入的慢查詢按指定的字串進行匹配過濾後再進行分析

--limit

限制輸出結果百分比或數量，預設值是20,即將最慢的20條語句輸出

--host

mysql伺服器地址

--user

mysql使用者名稱

--password

mysql使用者密碼

--history

將分析結果儲存到表中，分析結果比較詳細，下次再使用--history時，如果存在相同的語句，且查詢所在的時間區間和歷史表中的不同，則會記錄到資料表中，可以通過查詢同一CHECKSUM來比較某型別查詢的歷史變化

--review

將分析結果儲存到表中，這個分析只是對查詢條件進行引數化，一個型別的查詢一條記錄，比較簡單。當下次使用--review時，如果存在相同的語句分析，就不會記錄到資料表中

--output

分析結果輸出型別，值可以是report(標準分析報告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便於閱讀

--since

從什麼時間開始分析，值為字串，可以是指定的某個”yyyy-mm-dd (hh:mm:ss)”格式的時間點，也可以是簡單的一個時間值：s(秒)、h(小時)、m(分鐘)、d(天)，如12h就表示從12小時前開始統計。

--until

截止時間，配合—since可以分析一段時間內的慢查詢

3、應用案例

3.1 直接分析慢查詢檔案

pt-query-digest  /var/lib/mysql/log/mysql-slow.log

3.2 分析最近12小時內慢查詢 

pt-query-digest  --since=12h  /var/lib/mysql/log/mysql-slow.log

3.3 分析指定時間範圍內的慢查詢 

pt-query-digest /var/lib/mysql/log/mysql-slow.log --since '2019-11-27 09:30:00' --until '2019-11-27 10:00:00'

3.4 分析指含有select語句的慢查詢 

pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /var/lib/mysql/log/mysql-slow.log

3.5 針對某個使用者的慢查詢 

pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /var/lib/mysql/log/mysql-slow.log

3.6 查詢所有所有的全表掃描或full join的慢查詢 

pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' /var/lib/mysql/log/mysql-slow.log

3.7 把查詢儲存到query_review表 

pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' /var/lib/mysql/log/mysql-slow.log

3.8 把查詢儲存到query_history表 

pt-query-digest  --user=root –password=abc123 --review  h=localhost,D=test,t=query_history--create-review-table  /var/lib/mysql/log/mysql-slow.log_0001
pt-query-digest  --user=root –password=abc123 --review  h=localhost,D=test,t=query_history--create-review-table  /var/lib/mysql/log/mysql-slow.log_0002

3.9 通過tcpdump抓取mysql的tcp協議資料，然後再分析 

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt

3.10 分析binlog 

mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest  --type=binlog  mysql-bin000093.sql > slow_report10.log

3.11 分析general log 

pt-query-digest  --type=genlog  localhost.log

4、結果解析

總體統計結果

#該工具執行日誌分析的使用者時間，系統時間，實體記憶體佔用大小，虛擬記憶體佔用大小
#310ms user time, 20ms system time, 25.21M rss, 207.20M vsz
#工具執行時間
#Current date: Wed Nov 27 10:58:44 2019
#執行分析工具的主機名
#Hostname: test
#被分析的檔名
#Files: /var/lib/mysql/log/mysql-slow.log
#語句總數量，唯一的語句數量，QPS，併發數
#Overall: 1 total, 1 unique, 0 QPS, 0x concurrency ______________________
#日誌記錄的時間範圍
#Time range: all events occurred at 2019-11-27 10:58:42
#Attribute          total     min     max     avg     95%  stddev  median
#============     ======= ======= ======= ======= ======= ======= =======
#語句執行時間
#Exec time             3s      3s      3s      3s      3s       0      3s
#鎖佔用時間
#Lock time              0       0       0       0       0       0       0
#傳送到客戶端的行數
#Rows sent              1       1       1       1       1       0       1
#select語句掃描行數
#Rows examine           0       0       0       0       0       0       0
#查詢的字元數
#Query size            15      15      15      15      15       0      15
查詢分組統計結果
#Profile
#Rank Query ID                           Response time Calls R/Call V/M  
#==== ================================== ============= ===== ====== =====
#1 0x59A74D08D407B5EDF9A57DD5A41825CA 3.0004 100.0%     1 3.0004  0.00 SELECT
Rank      所有語句的排名，預設按查詢時間降序排列，通過--order-by指定
Query ID  語句的ID，（去掉多餘空格和文字字元，計算hash值）
Response  總的響應時間
time      該查詢在本次分析中總的時間佔比
calls     執行次數，即本次分析總共有多少條這種型別的查詢語句
R/Call    平均每次執行的響應時間
V/M       響應時間Variance-to-mean的比率
Item      查詢物件
每一種查詢的詳細統計結果
#Query 1: 0 QPS, 0x concurrency, ID 0x59A74D08D407B5EDF9A57DD5A41825CA at byte 3414
#This item is included in the report because it matches --limit.
#Scores: V/M = 0.00
#Time range: all events occurred at 2019-11-27 10:58:42
#Attribute    pct   total     min     max     avg     95%  stddev  median
#============ === ======= ======= ======= ======= ======= ======= =======
#Count        100       1
#Exec time    100      3s      3s      3s      3s      3s       0      3s
#Lock time      0       0       0       0       0       0       0       0
#Rows sent    100       1       1       1       1       1       0       1
#Rows examine   0       0       0       0       0       0       0       0
#Query size   100      15      15      15      15      15       0      15
#String:
#Hosts        localhost
#Users        root
#Query_time distribution
#1us
#10us
#100us
#1ms
#10ms
#100ms
#1s  ################################################################
#10s+
#EXPLAIN /*!50100 PARTITIONS*/
select sleep(3)\G
ID          查詢的ID號，和上圖的Query ID對應
Databases   資料庫名
Users       各個使用者執行的次數（佔比）
Query_time distribution
          查詢時間分佈, 長短體現區間佔比，本例中1s-10s之間查詢數量是10s以上的兩倍。
Tables      查詢中涉及到的表
Explain     SQL語句

4、pt-archiver工具

1、簡單介紹

pt-archiver 是將MySQL資料庫中的表資料歸檔到另外一個表或者檔案，也可以直接進行記錄的刪除操作。

歸檔表必須存在主鍵。

2、pt-archiver使用場景

  • 1、清理線上過期資料

  • 2、清理過期資料，並把資料歸檔到本地歸檔表中，或者遠端歸檔伺服器

  • 3、兩張表之間的資料不完全相同，希望合併。此時加上–ignore或–replace選項，可以輕鬆實現

  • 4、匯出線上資料，到線下資料作處理

3、常用引數

常用引數

 含義
--source/--dest 源端/目標端
h/D/t/u/p 主機IP/資料庫名/表名/使用者/密碼
--where 操作條件
--(no-)check-charset 檢查連線的字符集與表的字符集是否一致.
--limit  X 每次取X行資料用pt-archive處理
--txn-size X 設定X行為一個事務提交一次
--progress X 每處理X行輸出一次處理資訊
--statistics 輸出執行過程及最後的操作統計
--bulk-delete 批量刪除source上的舊資料
--bulk-insert 批量插入資料到dest主機(實際LOAD DATA插入)
--replace 將insert into 語句改成replace寫入到dest庫
--purge 刪除source資料庫的相關匹配記錄
--file

輸出為本地檔案

%d    Day of the month, numeric (01..31)

%H    Hour (00..23)

%i    Minutes, numeric (00..59)

%m    Month, numeric (01..12)

%s    Seconds (00..59)

%Y    Year, numeric, four digits

%D    Database name

%t    Table name

--header

本地檔案頭部加入列名

注：

1. 歸檔表必須存在主鍵

2. 需要配置client字符集為utf-8,如果你用了utf-8的編碼,防止歸檔資料為亂碼

[client]

default-character-set=utf8

4、應用案例

pt-archiver [OPTIONS] --source DSN --where WHERE

1.--dest, --file, --purge 必須指定其一

2.--ignore | --replace 只能選一

3.--txn-size | --commit-each 只能選一

4.--low-priority-insert | --delayed-insert 只能選一

5.--share-lock | --for-update 只能選一

6.--analyze | --optimize 只能選一

7.--no-ascend | --no-delete 只能選一

4.1 匯出到外部檔案，但是不刪除源表裡的資料

pt-archiver --source h=127.0.0.1,D=test,t=table1,u=root,p=123456 --where '1=1' --no-check-charset --no-delete --file="/tmp/archiver.dat"

4.2 刪除，不匯出和遷移 

pt-archiver --source h=127.0.0.1,D=test,t=table1,u=root,p=123456,A=UTF8 --charset=UTF8 --where "1=1" --limit 1000 --purge --commit-each --no-safe-auto-increment --progress=5 --statistics

4.3 全表歸檔到其他庫

pt-archiver --source h=127.0.0.1,D=test,t=table1,u=root,p=123456 --dest h=127.0.0.1,D=test,t=table2,u=root,p=123456 --where 'id<10000' --no-check-charset --no-delete --limit=1000 --commit-each --progress 2000 --statistics

注：

目標庫需要開啟load data local功能

SET GLOBAL local_infile = 1;

4.4 根據條件歸檔到其他庫 

pt-archiver \
--source h=72.0.0.111,P=3306,u=backup,p='mysql',D=zj20_sunft,t=trans_transreq \
--dest h=192.168.210.126,P=3306,u=backup,p='mysql',D=zj20_sunft,t=trans_transreq \
--charset=UTF8 --where " id < 1043835027 " --progress 10000 --limit=1000 --txn-size 10000 --bulk-insert --bulk-delete --statistics --purge

引數解析 含義

--charset=UTF8 指定字符集為UTF8

--where 'id < 1043835027' 設定操作條件

--progress 10000 每處理10000行輸出一次處理資訊

--limit=1000 每次取1000行資料給pt-archive處理

--txn-size 10000 設定10000行為一個事務提交一次

--bulk-insert 批量插入資料到dest主機

--bulk-delete 批量刪除source上的舊資料

--statistics 輸出統計資訊

--purge 刪除source資料庫的相關匹配記錄


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

相關文章