Python實作MySQL DBA小工具一例

  我們知道MySQL所有的運作狀态統計資訊都能從“show global status”語句的結果集中檢視，該結果集儲存的是從MySQL啟動到目前時間之間各狀态值的“總數”，對我們的分析不友好。在我們日常維護和優化中，我們需要持續的跟蹤某些狀态值的的變化（增量或者是平均值）；或者是在調整某些參數後，觀察某些狀态值的變化是否符合我們預期等等。

  基于以上這些需求，作者用Python實作了一個小工具。其實也從“MySQL Workbench”這個圖形化工具中獲得了一點啟發，想到做一個指令行下類似的功能。MySQL Workbench中的dashboard中可顯示如下圖的統計資訊，很漂亮哈~

<a href="http://s4.51cto.com/wyfs02/M00/82/6F/wKiom1dVI53w3a-bAAEnIch64NM659.png" target="_blank"></a>

  言歸正傳，該腳本顯示資訊的類型分兩類：“指定時間内平均值”和“指定時間内增長值”，在指令選項裡可指定，首先來看一下指令的幫助資訊

<a href="http://s4.51cto.com/wyfs02/M01/82/6B/wKioL1dVESzAV4PTAAPdOsiSzzU823.png" target="_blank"></a>

  其中--average選項為檢視指定時間平均值，預設檢視指定時間内的增長值；-t選項為想檢視的資訊類型，每一種資訊類型在腳本裡對應一個元組（即數組）：值為該類型所包含的“狀态值”。

  這裡我本來想做一個比較完美的指令，大家拿來即用，但是應為show status裡的狀态值太多了，我不用也沒必要在腳本裡包含所有的。腳本裡我隻将常用的一些資訊包含了進來，并且我更傾向于大家把這個腳本看做是一個易于根據自己情況定制的通用架構（其實就是如果自己關心的資訊沒有包含在腳本裡的話，自己在腳本裡添加或修改一個分類即可）。

先看幾個例子

  檢視指定時間内qps：

  檢視MySQL網絡相關狀态

<a href="http://s3.51cto.com/wyfs02/M01/82/6D/wKiom1dVF4PAEl1MAAQGkNEheHE906.png" target="_blank"></a>

OK，看完上面截圖覺得這小工具還有點用的，接着看代碼，代碼中注釋還是比較詳細的，隻要有點程式設計基礎，應該是能看的懂的~~

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

<code>#!/bin/env python3</code>

<code>"""</code>

<code>by lijiankai 20160602</code>

<code>from</code> <code>sys </code><code>import</code> <code>exit</code>

<code>from</code> <code>time </code><code>import</code> <code>sleep</code>

<code>from</code> <code>argparse </code><code>import</code> <code>ArgumentParser,RawTextHelpFormatter</code>

<code>import</code> <code>pymysql</code>

<code>#----------</code>

<code>#定義每種顯示類型(由-t指定)所包含的狀态</code>

<code>#添加和修改要展示的資訊修改types_dic字典即可</code>

<code>#同時關注下面add_argument方法添加-t選項時其中choices參數的值，應該和types_dic的鍵一緻</code>

<code>types_dic </code><code>=</code> <code>{}</code>

<code>types_dic[</code><code>'qps'</code><code>] </code><code>=</code> <code>(</code><code>'Com_select'</code><code>,</code><code>'Com_update'</code><code>,</code><code>'Com_insert'</code><code>,</code><code>'Com_delete'</code><code>,</code><code>'Com_replace'</code><code>,</code><code>'Questions'</code><code>,</code><code>'Queries'</code><code>)</code>

<code>types_dic[</code><code>'table_file'</code><code>] </code><code>=</code> <code>(</code><code>'Opened_tables'</code><code>,</code><code>'Opened_table_definitions'</code><code>,</code><code>'Opened_files'</code><code>,</code><code>'Created_tmp_tables'</code><code>,</code><code>'Created_tmp_disk_tables'</code><code>,</code><code>'Created_tmp_files'</code><code>,)</code>

<code>types_dic[</code><code>'general'</code><code>] </code><code>=</code> <code>(</code><code>'Slow_queries'</code><code>,</code><code>'Select_full_join'</code><code>,</code><code>'Select_full_range_join'</code><code>,</code><code>'Select_range'</code><code>,</code><code>'Select_scan'</code><code>,</code><code>'Sort_range'</code><code>,</code><code>'Sort_rows'</code><code>,</code><code>'Sort_scan'</code><code>)</code>

<code>types_dic[</code><code>'threading'</code><code>] </code><code>=</code> <code>(</code><code>'threads_created'</code><code>)</code>

<code>types_dic[</code><code>'networking_stats'</code><code>] </code><code>=</code> <code>(</code><code>'Bytes_received'</code><code>,</code><code>'Bytes_sent'</code><code>,</code><code>'Connections'</code><code>,</code><code>'Aborted_connects'</code><code>)</code>

<code>types_dic[</code><code>'innodb_buffer_pool'</code><code>] </code><code>=</code> <code>(</code><code>'Innodb_buffer_pool_read_requests'</code><code>,</code><code>'Innodb_buffer_pool_reads'</code><code>,</code><code>'Innodb_buffer_pool_write_requests'</code><code>,</code><code>'Innodb_buffer_pool_wait_free'</code><code>)</code>

<code>types_dic[</code><code>'innodb_data'</code><code>] </code><code>=</code> <code>(</code><code>'Innodb_data_fsyncs'</code><code>,</code><code>'Innodb_data_read'</code><code>,</code><code>'Innodb_data_written'</code><code>) </code>

<code>types_dic[</code><code>'innodb_stats'</code><code>] </code><code>=</code> <code>(</code><code>'Innodb_log_write_requests'</code><code>,</code><code>'Innodb_log_writes'</code><code>,</code><code>'Innodb_os_log_fsyncs'</code><code>,</code><code>'Innodb_os_log_writes'</code><code>)</code>

<code>#get_args()函數通過argparse子產品的ArgumentParser類來生成幫助資訊并擷取指令行參數</code>

<code>#生成一個全局變量字典對象args，儲存處理過的指令行參數</code>

<code>def</code> <code>get_args():</code>

<code>    </code><code>#執行個體化類，formatter_class參數允許help資訊以自定義的格式顯示</code>

<code>    </code><code>parser </code><code>=</code> <code>ArgumentParser(description</code><code>=</code><code>"This is a simple tool for MySQL DBA.\nWith this tool you can see several kinds of status's average or increase value in the last N seconds"</code><code>,formatter_class </code><code>=</code><code>RawTextHelpFormatter)</code>

<code>    </code> 

<code>    </code><code>#group_necessary = parser.add_argument_group('necessary arguments')</code>

<code>    </code><code>#group_optional = parser.add_argument_group('optional arguments')    #預設既有該項，所有參數均位于該項下</code>

<code>    </code><code>parser.add_argument(</code><code>'-u'</code><code>,metavar</code><code>=</code><code>'USER'</code><code>,dest</code><code>=</code><code>'user'</code><code>,</code><code>help</code><code>=</code><code>"mysql user"</code><code>,required</code><code>=</code><code>True</code><code>)</code>

<code>    </code><code>parser.add_argument(</code><code>'-p'</code><code>,metavar</code><code>=</code><code>'PASSWORD'</code><code>,dest</code><code>=</code><code>'password'</code><code>,</code><code>help</code><code>=</code><code>"mysql password"</code><code>,required</code><code>=</code><code>True</code><code>)</code>

<code>    </code><code>parser.add_argument(</code><code>'-H'</code><code>,metavar</code><code>=</code><code>'HOSTNAME'</code><code>,dest</code><code>=</code><code>'host'</code><code>,</code><code>help</code><code>=</code><code>"mysql hostname"</code><code>,required</code><code>=</code><code>True</code><code>,)</code>

<code>    </code><code>parser.add_argument(</code><code>'-P'</code><code>,metavar</code><code>=</code><code>'PORT'</code><code>,dest</code><code>=</code><code>'port'</code><code>,</code><code>help</code><code>=</code><code>"mysql port(default 3306)"</code><code>,default</code><code>=</code><code>3306</code><code>,</code><code>type</code><code>=</code><code>int</code><code>)</code>

<code>    </code><code>parser.add_argument(</code><code>'-i'</code><code>,metavar</code><code>=</code><code>'INTERVAL_TIME'</code><code>,dest</code><code>=</code><code>'interval'</code><code>,</code><code>help</code><code>=</code><code>"interval time(unit=second,default 10s)"</code><code>,default</code><code>=</code><code>10</code><code>,</code><code>type</code><code>=</code><code>int</code><code>)</code>

<code>    </code><code>#--average表示是否檢視平均值，預設顯示內插補點    action不能和metavar共存</code>

<code>    </code><code>parser.add_argument(</code><code>'--average'</code><code>,dest</code><code>=</code><code>'average'</code><code>,</code><code>help</code><code>=</code><code>'show average value in the interval time(default is the increase value)'</code><code>,action</code><code>=</code><code>'store_true'</code><code>)    </code><code>#store_true不能喝metavar共存</code>

<code>    </code><code>#下面-t選項中的choices的清單值需和腳本開始處定義的types_dic字典的鍵一緻</code>

<code>    </code><code>parser.add_argument(</code><code>'-t'</code><code>,metavar</code><code>=</code><code>'INFORMATION_TYPE'</code><code>,dest</code><code>=</code><code>'type'</code><code>,choices</code><code>=</code><code>[</code><code>'qps'</code><code>,</code><code>'table_file'</code><code>,</code><code>'general'</code><code>,</code><code>'threading'</code><code>,</code><code>'networking_stats'</code><code>,</code><code>'innodb_buffer_pool'</code><code>,</code><code>'innodb_data'</code><code>,</code><code>'innodb_stats'</code><code>],</code><code>help</code><code>=</code><code>"""information type.See the allowed types below:</code>

<code>qps :   numbers of the DML command executed</code>

<code>table_file :   tables or tmp_tables or tmp_files that are opend or created</code>

<code>general :   something about select</code>

<code>networking_stats :   something about network or connect</code>

<code>innodb_buffer_pool :   something about innodb_buffer_pool stats</code>

<code>innodb_data :   something about innodb_data stats</code>

<code>innodb_stats :   something about innodb stats"""</code><code>,\</code>

<code>    </code><code>required</code><code>=</code><code>True</code><code>)</code>

<code>    </code><code>#全局字典 鍵(add_argument()中的dest):值(使用者輸入)</code>

<code>    </code><code>#vars将Namespace object轉換成dict object</code>

<code>    </code><code>global</code> <code>args</code>

<code>    </code><code>args </code><code>=</code> <code>vars</code><code>(parser.parse_args())</code>

<code>    </code><code>#print(args)</code>

<code>#process_query()函數從get_args()傳回值中拿到登陸mysql需要的相關資訊</code>

<code>#執行show global status語句，并将結果儲存在status_dict字典中</code>

<code>def</code> <code>process_query():</code>

<code>    </code><code>status_dict</code><code>=</code><code>{}    </code><code>#存放所有status值</code>

<code>    </code><code>try</code><code>:</code>

<code>        </code><code>with pymysql.connect(host</code><code>=</code><code>args[</code><code>'host'</code><code>],user</code><code>=</code><code>args[</code><code>'user'</code><code>],password</code><code>=</code><code>args[</code><code>'password'</code><code>],charset</code><code>=</code><code>'utf8'</code><code>,port</code><code>=</code><code>args[</code><code>'port'</code><code>]) as mysql_cur:</code>

<code>            </code><code>mysql_cur.execute(</code><code>'show global status'</code><code>)    </code><code>#執行語句，查詢結果的每一行作為一個元組存進mysql_cur中</code>

<code>    </code><code>except</code> <code>pymysql.err.MySQLError as err:</code>

<code>        </code><code>print</code><code>(</code><code>"ERROR: "</code><code>+</code><code>str</code><code>(err))</code>

<code>        </code><code>exit(</code><code>10</code><code>)</code>

<code>    </code><code>for</code> <code>status </code><code>in</code> <code>mysql_cur:</code>

<code>        </code><code>status_dict[status[</code><code>0</code><code>]]</code><code>=</code><code>status[</code><code>1</code><code>]    </code><code>#更新status_dict字典</code>

<code>    </code><code>return</code> <code>status_dict</code>

<code>#show_result()函數處理相關資料，展示最終結果</code>

<code>def</code> <code>show_result(</code><code>type</code><code>):</code>

<code>    </code><code>#列印頭部</code>

<code>    </code><code>print</code><code>()</code>

<code>    </code><code>for</code> <code>status </code><code>in</code> <code>types_dic[args[</code><code>'type'</code><code>]]:</code>

<code>        </code><code>print</code><code>(</code><code>'     {}'</code><code>.</code><code>format</code><code>(status),end</code><code>=</code><code>'')</code>

<code>    </code><code>#開始循環顯示</code>

<code>        </code><code>while</code> <code>True</code><code>:</code>

<code>            </code><code>status_dic1</code><code>=</code><code>process_query()</code>

<code>            </code><code>sleep(args[</code><code>'interval'</code><code>])</code>

<code>            </code><code>status_dic2</code><code>=</code><code>process_query()</code>

<code>            </code><code>#列印各值</code>

<code>            </code><code>for</code> <code>k </code><code>in</code> <code>types_dic[args[</code><code>'type'</code><code>]]:</code>

<code>                </code><code>if</code> <code>args[</code><code>'average'</code><code>] </code><code>is</code> <code>True</code><code>:</code>

<code>                    </code><code>#有--average選項，輸出指定時間内的平均值</code>

<code>                    </code><code>print</code><code>( </code><code>'     '</code> <code>+</code> <code>str</code><code>(</code><code>round</code><code>((</code><code>int</code><code>(status_dic2[k])</code><code>-</code><code>int</code><code>(status_dic1[k]))</code><code>/</code><code>args[</code><code>'interval'</code><code>],</code><code>2</code><code>)).center(</code><code>len</code><code>(k)),end</code><code>=</code><code>'' )</code>

<code>                </code><code>else</code><code>:</code>

<code>                    </code><code>#輸出指定時間内的增長值</code>

<code>                    </code><code>print</code><code>( </code><code>'     '</code> <code>+</code> <code>str</code><code>(</code><code>round</code><code>(</code><code>int</code><code>(status_dic2[k])</code><code>-</code><code>int</code><code>(status_dic1[k]))).center(</code><code>len</code><code>(k)),end</code><code>=</code><code>'' )</code>

<code>            </code><code>print</code><code>()</code>

<code>    </code><code>except</code> <code>KeyboardInterrupt:</code>

<code>        </code><code>print</code><code>(</code><code>'\n-----bye-----'</code><code>)</code>

<code>             </code> 

<code>if</code> <code>__name__ </code><code>=</code><code>=</code> <code>'__main__'</code><code>:</code>

<code>    </code><code>get_args()</code>

<code>    </code><code>process_query()</code>

<code>    </code><code>show_result(args[</code><code>'type'</code><code>])</code>

最後再啰嗦兩句：

1. 關于MySQL show status中的各狀态，還是建議小夥伴們仔細閱讀下官方文檔的解釋

2. Python編寫指令行工具，用argparse子產品來生成幫助資訊和處理指令行參數還是很友善的。

對這篇文章有任何疑惑或建議都歡迎來讨論，一起進步。

     本文轉自kai404 51CTO部落格，原文連結：http://blog.51cto.com/kaifly/1786586，如需轉載請自行聯系原作者