检查mysql主从数据一致性,mysql主从数据一致性检查

mysql主从数据有时会发生不一致，主要可能有以下几点原因：

1.从库没有设置read_only为on

| read_only | OFF |

导致用户误操作了从库的数据

2.从库设置了read_only为on，但是权限控制没有做好，导致拥有super的用户，误操作了从库的数据

所以，为了保证主从数据的一致性，需要

1.从库设置read_only为on

2.对用户权限做梳理，收回所有用户的super以及all权限

3.更彻底的做法，将super-read-only设置为on(5.6.21以上支持)

当主从数据可能发生不一致时，如何做检测呢？

下面提供一个简单的python脚本，检测主从数据库的所有表的行数，并通过excel展示最终结果

import MySQLdb

import xlsxwriter

import time

import multiprocessing as mul

import sys

import warnings

warnings.filterwarnings("ignore")

reload(sys)

sys.setdefaultencoding('UTF-8')

logfile = "/tmp/getcount.log"

logfh = open(logfile, 'w')

master_db_user = 'xxxxx'

master_db_password = 'xxxxx'

master_db_host = 'xxx.xxx.xxx.xxx'

master_db_port = 3306

master_db = 'test'

slave_db_user = 'xxxxx'

slave_db_password = 'xxxxx'

slave_db_host = 'xxx.xxx.xxx'

slave_db_port = 3309

slave_db = 'test'

onlytbs =''

# get mysql connection

def conn_mysql(host, user, password, db,port):

try:

conn = MySQLdb.connect(host=host, user=user, passwd=password, db=db, port=port, charset='utf8');

except MySQLdb.Error, e:

print >> logfh, "MySQL Error:%s" % str(e)

print "MySQL Error:%s" % str(e)

sys.exit(1)

return conn

def create_check_table(tbonly):

master_conn = conn_mysql(master_db_host, master_db_user, master_db_password, master_db,master_db_port)

master_cursor = master_conn.cursor()

master_create_sql = """

create table if not exists %s.check_table_9999 (sowner varchar(255) DEFAULT '',sname varchar(255) DEFAULT '', towner varchar(255) DEFAULT '',tname varchar(255) DEFAULT '')

""" % 'test'

master_cursor.execute(master_create_sql)

if not tbonly:

master_select_sql = """ select table_name from information_schema.tables where table_schema='%s' order by 1""" % master_db

master_cursor.execute(master_select_sql)

for row in master_cursor.fetchall():

table_name = row[0]

insert_sql = """insert into test.check_table_9999(sowner,sname,towner,tname) values('%s','%s','%s','%s')""" % (master_db, table_name, slave_db, table_name)

master_cursor.execute(insert_sql)

master_conn.commit()

else:

tblist = tbonly.split(',')

for i in tblist:

insert_sql = """insert into test.check_table_9999(sowner,sname,towner,tname) values('%s','%s','%s','%s')""" % (master_db, i, slave_db, i)

master_cursor.execute(insert_sql)

master_conn.commit()

master_cursor.close()

master_conn.close()

def drop_check_table():

master_conn = conn_mysql(master_db_host, master_db_user, master_db_password, master_db,master_db_port)

master_cursor = master_conn.cursor()

master_create_sql = """drop table if exists %s.check_table_9999 """ % 'test'

master_cursor.execute(master_create_sql)

master_cursor.close()

master_conn.close()

def get_check_table():

master_conn = conn_mysql(master_db_host, master_db_user, master_db_password, master_db,master_db_port)

master_cursor = master_conn.cursor()

sql = 'select * from test.check_table_9999'

master_cursor.execute(sql)

table_list = []

alldata = master_cursor.fetchall()

for i in alldata:

table_list.append(i)

# print "table_list:", table_list

return table_list

def getcount(host, user, passwd, db, sql, q,port):

conn = conn_mysql(host, user, passwd, db,port)

cursor = conn.cursor()

try:

cursor.execute(sql)

countval = cursor.fetchall()[0][0]

q.put(countval)

except Exception, e:

countval = "Error :" + str(e)

q.put(countval)

def isdigit(num):

try:

int(num)

return True

except Exception, e:

return False

def comp(tblist):

# excel start

xlsxname = 'check_' + str(time.strftime("%Y%m%d%H%M", time.localtime())) + '.xlsx'

print "xlsxname:", xlsxname

workbook = xlsxwriter.Workbook(xlsxname)

top = workbook.add_format({'border': 6, 'align': 'center', 'bg_color': 'cccccc', 'font_size': 13, 'bold': True})

format_data_normal = workbook.add_format({'align': 'center', 'font_size': 13})

format_data_warn = workbook.add_format({'align': 'center', 'font_size': 13, 'bg_color': 'ff0000'})

format_data_err = workbook.add_format({'align': 'center', 'font_size': 13, 'bg_color': 'ffff00'})

worksheet = workbook.add_worksheet('sheet1')

worksheet.set_column('A:A', 12)

worksheet.set_column('B:B', 40)

worksheet.set_column('C:C', 12)

worksheet.set_column('D:D', 12)

worksheet.set_column('E:E', 40)

worksheet.set_column('F:F', 12)

worksheet.set_column('G:G', 12)

title = [u'suser', u'stable', u'scount', u'tuser', u'ttable', u'tcount', u'diff']

worksheet.write_row('A1', title, top)

# excel stop

length = len(tblist)

for i in range(length):

check_result = []

sowner = tablelist[i][0]

sname = tablelist[i][1]

towner = tablelist[i][2]

tname = tablelist[i][3]

sql_s = 'select count(1) from %s.%s' % (sowner, sname)

sql_t = 'select count(1) from %s.%s' % (towner, tname)

q1 = mul.Queue()

q2 = mul.Queue()

p1 = mul.Process(target=getcount, args=(master_db_host, master_db_user, master_db_password, master_db, sql_s, q1,master_db_port))

p2 = mul.Process(target=getcount, args=(slave_db_host, slave_db_user, slave_db_password, slave_db, sql_t, q2,slave_db_port))

p1.start()

p2.start()

count_s = q1.get()

count_t = q2.get()

p1.join()

p2.join()

check_result.append(sowner)

check_result.append(sname)

check_result.append(count_s)

check_result.append(towner)

check_result.append(tname)

check_result.append(count_t)

print '%s %s %s %s %s %s' % (sowner, sname, count_s, towner, tname, count_t)

if isdigit(count_s) and isdigit(count_t):

check_result.append(count_s - count_t)

if count_s == count_t:

worksheet.write_row('A' + str(2 + i), check_result, format_data_normal)

else:

worksheet.write_row('A' + str(2 + i), check_result, format_data_warn)

else:

check_result.append("Error")

worksheet.write_row('A' + str(2 + i), check_result, format_data_err)

workbook.close()

if __name__ == "__main__":

drop_check_table()

create_check_table(onlytbs)

print "AT time {0}".format(time.ctime())

print "Begin compare ..."

tablelist = get_check_table()

comp(tablelist)

print "AT time {0}".format(time.ctime())

print "compare complete!"

drop_check_table()

改脚本允许，需要装一下模块

git clone https://github.com/jmcnamara/XlsxWriter.git

cd XlsxWriter/

sudo python setup.py install

yum -y install MySQL-python

最终效果如下：

本文仅代表作者个人观点，不代表SEO研究协会网官方发声，对观点有疑义请先联系作者本人进行修改，若内容非法请联系平台管理员，邮箱cxb5918@163.com。更多相关资讯，请到SEO研究协会网www.seoxiehui.cn学习互联网营销技术请到巨推学院www.jutuiedu.com。