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，如需转载请自行联系原作者