MySQL database instance Parameter comparison script

MySQL database instance Parameter comparison script

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/python
Import MySQLdb, sys
Def fetch_variables (ip, user, passwd, port, variable = False ):
# Open database connection
Try:
Db = MySQLdb. connect (host = ip, user = user, passwd = passwd, port = port)
Except t 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.exe cute (SQL)
# Fetch all the rows in a list of lists.
Results = cursor. fetchall ()
Dict = {}
For row in results:
Dict [row [0] = row [1]
Except t:
Print "Error: unable to fecth data"
Return dict
Def 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 = '000000'
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_file
General_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: 3306
Version: 5.6.26-log | 5.6.26
Log_bin_index:/var/lib/mysql/mysql-bin.index |
Log_bin_basename:/var/lib/mysql-bin |
Fig: 9 | 104
Slow_query_log_file:/var/lib/mysql/mysql-server1-slow.log |/var/lib/mysql/keepalived01-slow.log
Server_id: 1 | 2
Hostname: mysql-server1 | keepalived01
Timestamp: 1462931171.666154 | 1462931171.957681
Log_bin: ON | OFF
General_log_file:/var/lib/mysql/mysql-server1.log |/var/lib/mysql/keepalived01.log
Max_binlog_size: 134217728 | 1073741824
Server_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.

This article permanently updates the link address: