MySQL db instance parameter comparison script

How to compare the parameters of two MySQL instances, the production often have such a requirement, recently wrote a Python script, can basically achieve this requirement.

Script

#!/usr/bin/pythonImportMysqldb,sysdefFetch_variables (ip,user,passwd,port,variable=False):#Open Database Connection    Try: DB= MySQLdb.connect (Host=ip, user=user,passwd=passwd,port=Port)exceptexception,e:PrintExit E ()#prepare a Cursor object using cursor () methodcursor =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={}         forRowinchResults:dict[row[0]]=row[1]    except:       Print "error:unable to FECTH data"    returnDictdefDict_to_set (dict):returnSet (Dict.keys ())defMain ():ifLen (SYS.ARGV)!=3 andLen (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 (':')    ifLen (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) forVariableinchSame_variables:ifDict[variable]! =ndict[variable]:PrintVariable':', Dict[variable],'|', Ndict[variable]if __name__=='__main__': Main ()

Execution mode

Input: Ip:port nip:nport var
Function: If Var is empty, it means to compare all parameters

With instance parameters, the execution results are as follows:

[[Email protected] ~] # 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

With no instance parameters, the execution results are as follows:

[[Email protected] ~]#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

Interpretation of output results:

The first column is the instance parameter, and the second and third columns use the ' | ' Separated, where 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 function def fetch_variables (ip,user,passwd,port,variable=false) is to get the instance parameters and values from the database. The Variable=false function is to handle the case where Var is empty and Var is not empty.

The function def dict_to_set (dict) is the conversion of dictionaries into collections, so that comparisons to dictionaries can be manipulated into collections.

MySQL db instance parameter comparison script