你的位置:首页 > 数据库

[数据库]MySQL数据库实例参数对比脚本


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

 

脚本

#!/usr/bin/pythonimport MySQLdb,sysdef fetch_variables(ip,user,passwd,port,variable=False):  # Open database connection  try:    db = MySQLdb.connect(host=ip, user=user,passwd=passwd,port=port)  except Exception,e:    print e    exit()  # prepare a cursor object using cursor() method  cursor = db.cursor()  if not variable:    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 in results:      dict[row[0]]=row[1]  except:    print "Error: unable to fecth data"  return dictdef dict_to_set(dict):  return set(dict.keys())def main():  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=False  else:    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 in same_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_filegeneral_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:3306version : 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.logserver_id : 1 | 2hostname : mysql-server1 | keepalived01timestamp : 1462931171.666154 | 1462931171.957681log_bin : ON | OFFgeneral_log_file : /var/lib/mysql/mysql-server1.log | /var/lib/mysql/keepalived01.logmax_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)是将字典转化为集合,这样对于字典的比较可以转为为集合来操作。