如何對比兩個MySQL實例的參數情況，生產中常會有這樣的需求，最近寫了個python腳本，可基本實現該需求。
腳本
#!/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 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 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 = '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_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.26 log_bin_index : /var/lib/mysql/mysql-bin.index | log_bin_basename : /var/lib/mysql/mysql-bin | pseudo_thread_id : 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
輸出結果解讀：
第一列是實例參數，第二列和第三列用“|”隔開，其中第二列是第一個實例的參數值，第三列是第二個實例的參數值。
腳本解讀
函數def fetch_variables(ip,user,passwd,port,variable=False)是從數據庫中獲取實例參數及值。variable=False的作用是來處理var為空和var不為空時的情況。
函數def dict_to_set(dict)是將字典轉化為集合，這樣對於字典的比較可以轉為為集合來操作。
