mysql数据库实例参数对比脚本_MySQL数据库实例参数对比脚本

如何对比两个MySQL实例的参数情况，生产中常会有这样的需求，最近写了个python脚本，可基本实现该需求。

脚本

#!/usr/bin/python

importMySQLdb,sysdef fetch_variables(ip,user,passwd,port,variable=False):#Open database connection

try:

db= MySQLdb.connect(host=ip, user=user,passwd=passwd,port=port)exceptException,e:printe

exit()#prepare a cursor object using cursor() method

cursor =db.cursor()if notvariable:

sql= 'show variables'

else:

sql= "show variables like '%"+variable+"%'"

try:#execute SQL query using execute() method.

cursor.execute(sql)#Fetch all the rows in a list of lists.

results =cursor.fetchall()

dict={}for row inresults:

dict[row[0]]=row[1]except:print "Error: unable to fecth data"

returndictdefdict_to_set(dict):returnset(dict.keys())defmain():if len(sys.argv)!=3 and len(sys.argv)!=4:print 'Usage:',sys.argv[0],'ip:port nip:nport var'exit()

user= 'root'password= '123456'ip, port= sys.argv[1].split(':')

nip,nport=sys.argv[2].split(':')if len(sys.argv)==3:

variable=Falseelse:

variable=sys.argv[3]

dict=fetch_variables(ip, user, password, int(port),variable)

ndict=fetch_variables(nip, user, password, int(nport),variable)

set=dict_to_set(dict)

nset=dict_to_set(ndict)

same_variables=set.intersection(nset)for variable insame_variables:if dict[variable] !=ndict[variable]:print variable,':',dict[variable],'|',ndict[variable]if __name__=='__main__':

main()

执行方式

输入：ip:port nip:nport var

功能：如果var为空，表示比较所有参数

带实例参数时，执行结果如下：

[root@mysql-server1 ~]#python diff_parameters.py 192.168.244.145:3306 192.168.244.146:3306 general_log_file

general_log_file : /var/lib/mysql/mysql-server1.log | /var/lib/mysql/keepalived01.log

不带实例参数时，执行结果如下：

[root@mysql-server1 ~]#python diff_parameters.py 192.168.244.145:3306 192.168.244.146:3306

version : 5.6.26-log | 5.6.26log_bin_index :/var/lib/mysql/mysql-bin.index |log_bin_basename :/var/lib/mysql/mysql-bin |pseudo_thread_id :9 | 104slow_query_log_file :/var/lib/mysql/mysql-server1-slow.log | /var/lib/mysql/keepalived01-slow.log

server_id :1 | 2hostname : mysql-server1 |keepalived01

timestamp :1462931171.666154 | 1462931171.957681log_bin : ON|OFF

general_log_file :/var/lib/mysql/mysql-server1.log | /var/lib/mysql/keepalived01.log

max_binlog_size :134217728 | 1073741824server_uuid : c063ba6f-aee7-11e5-820e-000c29b05336 | 959bf641-b9e7-11e5-89c7-000c294c5ed4

输出结果解读：

第一列是实例参数，第二列和第三列用“|”隔开，其中第二列是第一个实例的参数值，第三列是第二个实例的参数值。

脚本解读

函数def fetch_variables(ip,user,passwd,port,variable=False)是从数据库中获取实例参数及值。variable=False的作用是来处理var为空和var不为空时的情况。

函数def dict_to_set(dict)是将字典转化为集合，这样对于字典的比较可以转为为集合来操作。