MySQL database instance Parameter comparison script, mysql instance
How to compare the parameters of two MySQL instances is usually the requirement in production. Recently, a python script has been written to achieve this requirement.
Script
#!/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()
Execution Method
Input: ip: port nip: nport var
Function: If var is null, all parameters are compared.
The execution result is as follows:
[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
The execution result is as follows:
[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
Explanation of output results:
The first column is instance parameters, and the second column and the third column are separated by "|". The second column is the parameter value of the first instance, and the third column is the parameter value of the second instance.
Script Interpretation
The def fetch_variables (ip, user, passwd, port, variable = False) function obtains instance parameters and values from the database. Variable = False is used to handle situations where var is empty and var is not empty.
The def dict_to_set (dict) function converts a dictionary to a set. Therefore, you can convert a dictionary comparison to a set.