MySQL每小时统计数据，监测数据库健康状态（mysql一天每个小时）
MySQL每小时统计数据，监测数据库健康状态
MySQL是目前世界上使用最广泛的关系型数据库管理系统之一，其被广泛应用在各类应用场景中，例如电商、社交、游戏、在线教育等等。由于其业务关键性， MySQL 数据库的运行状况监控显得格外重要。为了保证 MySQL 数据库的健康稳定，我们可以利用 crontab 定时任务每小时统计数据，从而监测数据库健康状态。
主要思路
MySQL 数据库的经常需要进行性能、空间、连接数等各方面指标的监控，这里我们主要着眼于利用定时任务统计数据库每小时的查询、执行、等待和锁等四个指标，以及首页的线程使用状态。实现的主要思路如下：
1. 利用 MySQL 自带的SHOW STATUS命令获取数据库四个指标值。
2. 利用 SHOW ENGINE INNODB STATUS 命令获取 InnoDB 引擎的等待和锁状态。
3. 统计上面的命令输出得到每小时的查询、执行、等待和锁四个指标的总运行数。
4. 制定每小时的日志，并将统计得到的日志存储到MySQL指定的表格。
5. 利用定时任务 crontab 每小时执行一次以上监测。
实现过程
第一步：在MySQL中创建数据库和表格
我们在MySQL中首先创建一个数据库monitor，并在该数据库中创建名为stats的表格，用于存储每小时统计的数据。
CREATE DATABASE monitor;
USE monitor;
CREATE TABLE stats (
time datetime NOT NULL,
queries INT(11) NOT NULL,
updates INT(11) NOT NULL,
locks_wted INT(11) NOT NULL,
locks_okay INT(11) NOT NULL,
innodb_rows_read INT(11) NOT NULL,
innodb_rows_inserted INT(11) NOT NULL,
innodb_rows_updated INT(11) NOT NULL,
innodb_rows_deleted INT(11) NOT NULL,
PRIMARY KEY (time)
);
第二步：编写统计脚本
我们编写一个 Python 脚本，用于统计查询、执行、等待和锁四个指标条件、InnoDB引擎的等待和锁状态。主要代码实现如下：
#!/usr/bin/env python
import MySQLdb, time
# MySQL数据库连接参数（根据实际情况修改）
mysql_host = ‘localhost’
mysql_port = 3306
mysql_user = ‘root’
mysql_passwd = ‘password’
mysql_db = ‘monitor’
# 连接MySQL数据库
db = MySQLdb.connect(host=mysql_host, port=mysql_port, user=mysql_user, passwd=mysql_passwd, db=mysql_db)
# 获得游标对象
cursor = db.cursor()
# MySQL的SHOW STATUS命令，获取数据库四个指标值
def get_mysql_stats():
sql = “SHOW GLOBAL STATUS WHERE Variable_name IN(‘Com_select’,’Com_update’,’Innodb_row_lock_wts’,’Innodb_row_lock_time’,’Innodb_rows_read’,’Innodb_rows_inserted’,’Innodb_rows_updated’,’Innodb_rows_deleted’)”
cursor.execute(sql)
results = cursor.fetchall()
return dict(results)
# MySQL的SHOW ENGINE INNODB STATUS命令，获取InnoDB引擎的等待和锁状态
def get_innodb_stats():
sql = “SHOW ENGINE INNODB STATUS”
cursor.execute(sql)
result = cursor.fetchone()[2]
return result
# 统计上面的命令输出得到每小时的查询、执行、等待和锁四个指标的总运行数
def stats():
mysql_result1 = get_mysql_stats()
innodb_result = get_innodb_stats()
mysql_result2 = get_mysql_stats()
ts = time.strftime(‘%Y-%m-%d %H:%M:%S’)
queries = mysql_result2[‘Com_select’] – mysql_result1[‘Com_select’]
updates = mysql_result2[‘Com_update’] – mysql_result1[‘Com_update’]
locks_wted = mysql_result2[‘Innodb_row_lock_wts’] – mysql_result1[‘Innodb_row_lock_wts’]
locks_okay = mysql_result2[‘Innodb_row_lock_time’] – mysql_result1[‘Innodb_row_lock_time’]
rows_read = mysql_result2[‘Innodb_rows_read’] – mysql_result1[‘Innodb_rows_read’]
rows_inserted = mysql_result2[‘Innodb_rows_inserted’] – mysql_result1[‘Innodb_rows_inserted’]
rows_updated = mysql_result2[‘Innodb_rows_updated’] – mysql_result1[‘Innodb_rows_updated’]
rows_deleted = mysql_result2[‘Innodb_rows_deleted’] – mysql_result1[‘Innodb_rows_deleted’]
return ts,queries,updates,locks_wted,locks_okay,rows_read,rows_inserted,rows_updated,rows_deleted,innodb_result
# 将统计得到的日志存储到MySQL指定的表格
def insert_into_mysql():
ts,queries,updates,locks_wted,locks_okay,rows_read,rows_inserted,rows_updated,rows_deleted,innodb_result = stats()
sql = “INSERT INTO stats(time, queries, updates, locks_wted, locks_okay, innodb_rows_read, innodb_rows_inserted, innodb_rows_updated, innodb_rows_deleted) VALUES (‘%s’, %s, %s, %s, %s, %s, %s, %s, %s)”%(ts,queries,updates,locks_wted,locks_okay,rows_read,rows_inserted,rows_updated,rows_deleted)
cursor.execute(sql)
db.commit()
# 关闭数据库连接
def close_connection():
db.close()
# 调用 insert_into_mysql 函数，将统计结果存储到 MySQL 指定的表格中
if __name__ == ‘__mn__’:
insert_into_mysql()
第三步：设置 crontab 定时任务
设置定时任务，每小时执行一次 stats.py 脚本，用于监测数据库的健康状态。主要代码实现如下：
# 编辑crontab列表，输入 crontab -e
# 在文件中添加以下cron命令（一行），保存退出即可
0 * * * * /usr/bin/python /path/to/stats.py
在上面的 cron 命令中，0 * * * * 表示每小时的第 0 分钟执行，/usr/bin/python 表示使用 Python 运行 stats.py 脚本，/path/to/stats.py 则为 stats.py 脚本完整路径。
在指定的每小时时间点，你将会收到 MySQL 数据库的健康状态数据。这将有助于保证 MySQL 数据库在高并发负载情况下的正常运行。
