Check the Data Consistency of master-slave important tables in the mysql database.

It is not difficult to master and slave mysql databases, but the consistency of master and slave data is very important. This script is used to roughly check the data consistency of the master and slave important tables of mysql databases, mainly in the normal circumstances of master and slave databases, check whether the number of data entries in the statistical table of the connected database is consistent. The script is divided into two parts: one is the py script and the other is the ini configuration file. The source code of the py script is as follows:

#!/usr/bin/env python  #This script is used check mysql replcation some table  # -*- coding: utf-8 -*-  import sys,time,MySQLdb,threading  import ConfigParser   class Check:      def __init__(self):          self.w =[]          self.table = table      def conf(self):          fp = ConfigParser.ConfigParser()          fp.readfp(open('config.ini'))          iplist = fp.get("global", "iplist")          return iplist       def wd(self,table):          iplist = self.conf()          hostlist = []          self.w = []          threads = []          for i in iplist.split(";"):              hostlist.append(i.split(","))          for i in range(len(hostlist)):              host = hostlist[i][0]              hostname = hostlist[i][1]              t = threading.Thread(target=self.mysql_check,args=(host,hostname,i))              threads.append(t)              t.start()              time.sleep(0.05)          for i in range(len(hostlist)):              threads[i].join()          self.w.sort()          for r in range(len(self.w)):              print self.w[r]       def mysql_check(self,host,hostname,i):          try:              conn = MySQLdb.connect(host = host,user = 'user',passwd = 'passwd',connect_timeout=5)              cursor = conn.cursor()               sql = "SELECT COUNT(*) FROM %s" % self.table              cursor.execute(sql)              alldata = cursor.fetchall()              count = alldata[0][0]              value = hostname + "\t" + str(count)              self.w.append(value)          except:              print "Can not Connect to " + host + " mysql server"             return 0  if __name__ == "__main__":       table_list = ['adb.credit_log','adb.account','ddb.data']      for i in range(len(table_list)):          table = table_list[i]          print "Table Count:  " + table          print         boss = Check()          boss.wd(table)          print 

The config. ini file format is as follows:

 
 

  
  
[Global]
  
  
Iplist = 192.168.50.1, canghai yixiao _ adb (master); 192.168.50.2, canghai yixiao _ adb (slave); 192.168.50.3, colorful _ adb (master); 192.168.50.4, colorful _ adb (slave)
 
 

The execution result is as follows:

Table Count: adb. credit_log

Canghai yixiao _ adb (from) 1485149
Canghai yixiao _ adb (main) 1485149
Kaitianti_adb (from) 877838
Kaitiandi_adb (master) 877838
Legend _ adb (from) 1484796
Legend _ adb (master) 1484796
Ru Hu Tianyi _ adb (from) 1166470
Ru Hu Tianyi _ adb (main) 1166470
Heaven and earth ambition _ adb (from) 609058
Heaven and earth ambition _ adb (main) 609058
Tianji Beidou _ adb (from) 1106899
Tianji Beidou _ adb (main) 1106899

Table Count: adb. account

Canghai yixiao _ adb (from) 4295144
Canghai yixiao _ adb (main) 4295144
Kaitianti_adb (from) 1386242
Kaitiandi_adb (master) 1386242
Legend _ adb (from) 2608761
Legend _ adb (master) 2608761
Ru Hu Tianyi _ adb (from) 1678627
Ru Hu Tianyi _ adb (main) 1678627
Heaven and earth ambition _ adb (from) 2733346
Heaven and earth ambition _ adb (main) 2733346
Tianji Beidou _ adb (from) 3925829
Tianji Beidou _ adb (main) 3925829

Table Count: ddb. data

Canghai yixiao _ adb (from) 19026601
Canghai yixiao _ adb (main) 19026601
Kaitianti_adb (from) 8131988
Kaitiandi_adb (master) 8131988
Legend _ adb (from) 11684648
Legend _ adb (master) 11684648
Ru Hu Tianyi _ adb (from) 8529377
Ru Hu Tianyi _ adb (main) 8529377
Heaven and earth ambition _ adb (from) 11533682
Heaven and earth ambition _ adb (main) 11533682
Tianji Beidou _ adb (from) 15381099
Tianji Beidou _ adb (main) 15381099