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升级：https://www.cnblogs.com/gjc592/p/9223005.html

pip安装： https://www.cnblogs.com/gjc592/p/9272209.html

图形化展示：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 NULL AUTO_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）获取一次监控结果，并将记录写入数据库中

更新monitor_time表脚本monitor_tidb.py

import os,time
import pymysql


while True:
        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)

延迟监控脚本get_tidb_delay.py

import os,timeimport pymysql
while True:        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/1000        t2 = t_sor/1000 - t_138/1000        sql_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  &

 三、 可视化展示

以下是python其中一种实现方式，其他如折线图方式可执行修改。建议使用grafana进行展示。grafana，zabbix等监控部署将在后续介绍

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


import pymysql
import plotly.plotly
from plotly.graph_objs import *
import plotly.graph_objs as abc
import matplotlib.pyplot as plt


host = "*.*.*.*"
user = "monitor"
passwd = "monitor"
db = "monitor"
port = 3306
charset = "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 in dfs:
    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)

部分时间段效果如下：

 

 

想进一步沟通或想了解其他文章的同学可以关注我

精彩推荐：

1. MySQL不停地自动重启怎么办

2. 升级python，就是这么简单

3. MySQL里trx_mysql_thread_id为0 的事务导致大量锁等待超时该咋整

4. mysql8.0新增用户及加密规则修改的那些事