mysql tidb 删除延迟_MySQL至TiDB复制延迟监控

因生产环境mysql中有较多复杂sql且运行效率低，因此采用tidb作为生产环境的从库进行部分慢sql及报表的读写分离。其中MySQL至TIDB采用Syncer工具同步。

关于TIDB的安装及Syncer可参照官网指引进行,搭建的主从复制架构如下：

因该方式中TiDB的数据是通过Syncer同步的，且TIDB无show slave status命令查看复制情况，故自己开发脚本对MySQL至TIDB的复制延迟进行监控，并且将结果进行图形化展示以便于直观分析，而且此方法也可以监控MySQL主从延迟，类似于percona toolkit的pt-heartbeat 。

一、 准备工作

1. 监控所需工具

监控：Python 2.7及以上，安装pymysql(或MySQLdb),其中linux升级python及pip安装可参考之前的博文

图形化展示：Python plotly、matplotlib或pandas包

2. 监控延迟思路

1)创建监控数据库(monitor)及相关表(monitor_time,monitor_result)

2)每隔固定时间(看监控精确度，如0.5s)将当期时间或时间戳的结果更新到mysql的监控表中

3)对比mysql与tidb对应的监控库(monitor库)中的monitor_time表的时间差，并将结果记录在monitor_result里

3. 可视化展示结果

用Python 的plotly、matplotlib或pandas等展示监控结果

二、延时监控实施步骤

1. 创建数据库及相关表，并将其加入Syncer同步中

--创建监控数据库monitor;

CREATE DATABASE`monitor`;USE`monitor`;--创建监控时间表monitor_time;

CREATE TABLE`monitor_time` (

`t`bigint(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;--初始化数据，因后面监控程序定时更新该条记录

insert into monitor_time select 1;--创建监控结果表monitor_result;

CREATE TABLE`monitor_result` (

`id`bigint(20) NOT NULLAUTO_INCREMENT,

`t`int(11) DEFAULT NULL COMMENT '延迟时间',

`add_time`timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '监控记录生成时间',

`t_mysql`int(11) DEFAULT NULL COMMENT 'mysql主从延迟时长', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 创建监控账号并授权

单独创建一个用于监控该延迟的账号，并添加相应的权限。

CREATE USER monitor@'192.168.3.42' IDENTIFIED BY 'monitor';GRANT SELECT ,INSERT ,UPDATE ON monitor.* TO monitor@'192.168.3.42';

3. 监控脚本

每隔0.5s更新一次monitor_time表，自定义时间(如例子中10s)获取一次监控结果，并将记录写入数据库中

importos,timeimportpymysqlwhileTrue:

t=time.time()

t1= int(round(t * 1000))

conn= pymysql.connect(host='*.*.*.*',port=3306,user='monitor',passwd='monitor')

cur=conn.cursor()

sql_update= "update monitor.monitor_time set t="+str(t1)

cur.execute(sql_update)

conn.commit()

conn.close()

time.sleep(0.5)

importos,timeimportpymysql

whileTrue:

conn_sor= pymysql.connect(host='*.*.*.*',port=3306,user='monitor',passwd='monitor')

cur_sor=conn_sor.cursor()

conn_138= pymysql.connect(host='*.*.*.*',port=3306,user='monitor',passwd='monitor')

cur_138=conn_138.cursor()

conn_des= pymysql.connect(host='*.*.*.*',port=4000,user='monitor',passwd='monitor')

cur_des=conn_des.cursor()

sql_get_time= "select t from monitor.monitor_time"cur_sor.execute(sql_get_time)

v_src_tuple=cur_sor.fetchone()

t_sor=v_src_tuple[0]

cur_des.execute(sql_get_time)

v_des_tuple=cur_des.fetchone()

t_des=v_des_tuple[0]

cur_138.execute(sql_get_time)

v_138_tuple=cur_138.fetchone()

t_138=v_138_tuple[0]

t1= t_sor/1000 - t_des/1000t2= t_sor/1000 - t_138/1000sql_insert= "insert into monitor.monitor_result(t,t_mysql) select"+str(t1)+","+str(t2)

cur_sor.execute(sql_insert)

conn_sor.commit()

conn_sor.close()

conn_des.close()

time.sleep(10)

将2个脚本放在监控服务器上运行

python monitor_tidb.py &python get_tidb_delay.py&

三 可视化展示

以下是其中一种实现方式，其他如折线图方式可执行修改

#__author__ : 'GJC'#__created__ : '2018/9/17'#coding=utf-8

importpymysqlimportplotly.plotlyfrom plotly.graph_objs import *

importplotly.graph_objs as abcimportmatplotlib.pyplot as plt

host= "*.*.*.*"user= "monitor"passwd= "monitor"db= "monitor"port= 3306charset= "utf8"conn=pymysql.connect(

host=host,

port=port,

user=user,

passwd=passwd,

db=db,

charset=charset,

)

cur=conn.cursor()

re= cur.execute("SELECT add_time,t,t_mysql FROM monitor.monitor_result_t")

dfs=cur.fetchall()

listx=[]

listy=[]

listy2=[]for row indfs:

listx.append(row[0])

listy.append(row[1])

listy2.append(row[2])

cur.close()

conn.commit()

conn.close()

length= listy.__len__()

data_1=abc.Scatter(

x=listx,

y=listy,

name='syncer_delay_time_tidb',

mode='markers',

marker=dict(

size=10,

color="rgba(255,47,167,.9)",

line=dict(

width=2,

color='rgb(2,2,2)')

)

)

data_2=abc.Scatter(

x=listx,

y=listy2,

name='syncer_delay_time_mysql',

mode='markers',

marker=dict(

size=10,

color="rgba(255,47,167,.9)",

line=dict(

width=2,

color='rgb(3,3,3)')

)

)

data1=Data([data_1])

plotly.offline.plot(data1)

data2=Data([data_2])

plotly.offline.plot(data2)

部分时间段效果如下：

耿小厨已开通个人微信公众号，想进一步沟通或想了解其他文章的同学可以关注我