通过Python收集汇聚MySQL表信息
一.需求
统计收集各个实例上table的信息，主要是表的记录数及大小。
收集的范围是cmdb中所有的数据库实例。
二.公共基础文件说明
1.配置文件
配置文为db_servers_conf.ini，假设cmdb的DBServer为119.119.119.119，单独存放收集监控数据的DBserver为110.110.110.110. 这两个DB实例的访问用户名一样，定义在了[uid_mysql] 部分，需要去收集的各个DB实例，用到的账号密码是另一个，定义在了[collector_mysql]部分。
[uid_mysql]dbuid = 用*户*名
dbuid_p_w_d = 相*应*密*码
[cmdb_server]
db_host =
119.119.119.119db_port =
3306[dbmonitor_server]
db_host =
110.110.110.110db_port =
3306[collector_mysql]
collector = DB*实*例*用*户*名
collector_p_w_d = DB*实*例*密*码
2.定义声明db连接
文件为get_mysql_db_connect.py
# -*- coding: utf-8 -*-import sys
import os
import configparser
import pymysql
# 获取连接串信息
def mysql_get_db_connect(db_host, db_port):
db_host = db_host
db_port = db_port
db_ps_file = os.path.join(sys.path[0], "db_servers_conf.ini")
config = configparser.ConfigParser()
config.read(db_ps_file, encoding="utf-8")
db_user = config.get("uid_mysql", "dbuid")
db_pwd = config.get("uid_mysql", "dbuid_p_w_d")
conn = pymysql.connect(host=db_host, port=db_port, user=db_user, password=db_pwd, connect_timeout=5, read_timeout=5, write_timeout=5)
return conn
# 获取连接串信息
def mysql_get_collectdb_connect(db_host, db_port):
db_host = db_host
db_port = db_port
db_ps_file = os.path.join(sys.path[0], "db_servers_conf.ini")
config = configparser.ConfigParser()
config.read(db_ps_file, encoding="utf-8")
db_user = config.get("collector_mysql", "collector")
db_pwd = config.get("collector_mysql", "collector_p_w_d")
conn = pymysql.connect(host=db_host, port=db_port, user=db_user, password=db_pwd, connect_timeout=5, read_timeout=5, write_timeout=5)
return conn
3.定义声明访问db的操作
文件为mysql_exec_sql.py,注意需要导入上面的model。
# -*- coding: utf-8 -*-import get_mysql_db_connect
def mysql_exec_dml_sql(db_host, db_port, exec_sql):
conn = mysql_get_db_connect.mysql_get_db_connect(db_host, db_port)
with conn.cursor() as cursor_db:
cursor_db.execute(exec_sql)
conn.commit()
##需要显式关闭
cursor_db.close()
conn.close()
def mysql_exec_select_sql(db_host, db_port, exec_sql):
conn = mysql_get_db_connect.mysql_get_db_connect(db_host, db_port)
with conn.cursor() as cursor_db:
cursor_db.execute(exec_sql)
sql_rst = cursor_db.fetchall()
##显式关闭conn
cursor_db.close()
conn.close()
return sql_rst
def mysql_exec_select_sql_include_colnames(db_host, db_port, exec_sql):
conn = mysql_get_db_connect.mysql_get_db_connect(db_host, db_port)
with conn.cursor() as cursor_db:
cursor_db.execute(exec_sql)
sql_rst = cursor_db.fetchall()
col_names = cursor_db.description
return sql_rst, col_names
三.主要代码
3.1 创建保存数据的脚本
用来保存收集表信息的表：table_info
createtable `table_info` (`id`
int(11) NOTNULL AUTO_INCREMENT,`host_ip`
varchar(50) NOTNULLDEFAULT"0",`port`
varchar(10) NOTNULLDEFAULT"3306",`
db_name` varchar(100) NOTNULLDEFAULT"" COMMENT "数据库名字",`table_name`
varchar(100) NOTNULLDEFAULT"" COMMENT "表名字",`table_rows`
bigintNOTNULLDEFAULT0 COMMENT "表行数",`table_data_length`
bigint,`table_index_length`
bigint,`table_data_free`
bigint,`table_auto_increment`
bigint,`creator`
varchar(50) NOTNULLDEFAULT"",`create_time`
datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,`operator`
varchar(50) NOTNULLDEFAULT"",`operate_time`
datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY (`id`)) ENGINE
=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=utf8mb4;
收集过程，如果访问某个实例异常时，将失败的信息保存到表 gather_error_info 中，以便跟踪分析。
createtable `gather_error_info` (`id`
int(11) NOTNULL AUTO_INCREMENT,`
app_name` varchar(150) NOTNULLDEFAULT"报错的程序",`host_ip`
varchar(50) NOTNULLDEFAULT"0",`port`
varchar(10) NOTNULLDEFAULT"3306",`
db_name` varchar(60) NOTNULLDEFAULT"0" COMMENT "数据库名字",`error_msg`
varchar(500) NOTNULLDEFAULT"报错的程序",`status`
int(11) NOTNULLDEFAULT"2",`creator`
varchar(50) NOTNULLDEFAULT"",`create_time`
datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,`operator`
varchar(50) NOTNULLDEFAULT"",`operate_time`
datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY (`id`)) ENGINE
=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=utf8mb4;
3.2 收集的功能脚本
定义收集 DB_info的脚本 collect_tables_info.py
# -*- coding: utf-8 -*-import sys
import os
import datetime
import configparser
import pymysql
import mysql_get_db_connect
import mysql_exec_sql
import mysql_collect_exec_sql
import pandas as pd
def collect_tables_info():
db_ps_file = os.path.join(sys.path[0], "db_servers_conf.ini")
config = configparser.ConfigParser()
config.read(db_ps_file, encoding="utf-8")
cmdb_host = config.get("cmdb_server", "db_host")
cmdb_port = config.getint("cmdb_server", "db_port")
monitor_db_host = config.get("dbmonitor_server", "db_host")
monitor_db_port = config.getint("dbmonitor_server", "db_port")
# 获取需要遍历的DB列表
exec_sql_1 = """
select vm_ip_address,port,b.vm_host_name,remark
FROM cmdbdb.mysqldb_instance
;
"""
exec_sql_tablesizeinfo = """
select TABLE_SCHEMA,table_name,table_rows,data_length ,index_length,data_free,auto_increment
from information_schema.tables
where TABLE_SCHEMA not in ("mysql","information_schema","performance_schema","sys")
and TABLE_TYPE ="BASE TABLE";
"""
exec_sql_insert_tablesize = " insert into monitordb.table_info (host_ip,port,db_name,table_name,table_rows,table_data_length,table_index_length,table_data_free,table_auto_increment)
VALUES ("%s", "%s","%s","%s", %s ,%s, %s,%s, %s) ;"
exec_sql_error = " insert into monitordb.gather_db_error (app_name,host_ip,port,error_msg)
VALUES ("%s", "%s","%s","%s") ;"
sql_rst_1 = mysql_exec_sql.mysql_exec_select_sql(cmdb_host, cmdb_port, exec_sql_1)
if len(sql_rst_1):
for i in range(len(sql_rst_1)):
rw_host = list(sql_rst_1[i])
db_host_ip = rw_host[0]
db_port_s = rw_host[1]
##print(type(rw_host))
###ValueError: port should be of type int
db_port = int(db_port_s)
try:
sql_rst_tablesize = mysql_collect_exec_sql.mysql_exec_select_sql(db_host_ip, db_port, exec_sql_tablesizeinfo)
##print(sql_rst_tablesize)
if len(sql_rst_tablesize):
for i in range(len(sql_rst_tablesize)):
rw_tableinfo = list(sql_rst_tablesize[i])
rw_db_name = rw_tableinfo[0]
rw_table_name = rw_tableinfo[1]
rw_table_rows = rw_tableinfo[2]
rw_data_length = rw_tableinfo[3]
rw_index_length = rw_tableinfo[4]
rw_data_free = rw_tableinfo[5]
rw_auto_increment = rw_tableinfo[6]
##print(rw_auto_increment)
##Python中对变量是否为None的判断
if rw_auto_increment is None:
rw_auto_increment = 0
###一定要有一个exec_sql_insert_table_com，如果是exec_sql_insert_tablesize = exec_sql_insert_tablesize % ( db_host_ip.......
####则提示报错:报错信息是 TypeError: not all arguments converted during string formatting
exec_sql_insert_table_com = exec_sql_insert_tablesize % ( db_host_ip , db_port_s, rw_db_name, rw_table_name , rw_table_rows , rw_data_length , rw_index_length , rw_data_free , rw_auto_increment)
print(exec_sql_insert_table_com)
sql_insert_rst_1 = mysql_exec_sql.mysql_exec_dml_sql(monitor_db_host, monitor_db_port, exec_sql_insert_table_com)
#print(sql_insert_rst_1)
except:
####print("TypeError的错误信息如下:" + str(TypeError))
print(db_host_ip +""+str(db_port) + "登入异常无法获取table信息,请检查实例和访问账号!")
exec_sql_error_sql = exec_sql_error % ( "collect_tables_info",db_host_ip , str(db_port),"登入异常，获取table信息失败,请检查实例和访问的账号!!!" )
sql_insert_err_rst_1 = mysql_exec_sql.mysql_exec_dml_sql(monitor_db_host, monitor_db_port, exec_sql_error_sql)
##print(sql_rst_1)
else:
print("查询无结果集")
collect_tables_info()
以上是 通过Python收集汇聚MySQL表信息 的全部内容， 来源链接： utcz.com/z/536023.html
