如何通过Python收集汇聚MySQL表信息

本篇内容主要讲解"如何通过Python收集汇聚MySQL表信息"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"如何通过Python收集汇聚MySQL表信息"吧!

目录

• 一.需求

• 二.公共基础文件说明

• 1.配置文件

• 2.定义声明db连接

• 3.定义声明访问db的操作

• 三.主要代码

• 3.1 创建保存数据的脚本

• 3.2 收集的功能脚本

一.需求

统计收集各个实例上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 sysimport osimport configparserimport 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_connectdef 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_rstdef 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

create table `table_info` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `host_ip` varchar(50) NOT NULL DEFAULT '0',  `port` varchar(10) NOT NULL DEFAULT '3306',  `db_name` varchar(100) NOT NULL DEFAULT ''   COMMENT '数据库名字',  `table_name` varchar(100) NOT NULL DEFAULT '' COMMENT '表名字',  `table_rows` bigint NOT NULL DEFAULT 0 COMMENT '表行数',  `table_data_length` bigint,  `table_index_length` bigint,  `table_data_free` bigint,  `table_auto_increment` bigint,  `creator` varchar(50) NOT NULL DEFAULT '',  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `operator` varchar(50) NOT NULL DEFAULT '',  `operate_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

收集过程，如果访问某个实例异常时，将失败的信息保存到表 gather_error_info 中，以便跟踪分析。

create table `gather_error_info` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `app_name` varchar(150) NOT NULL DEFAULT '报错的程序',  `host_ip` varchar(50) NOT NULL DEFAULT '0',  `port` varchar(10) NOT NULL DEFAULT '3306',  `db_name` varchar(60) NOT NULL DEFAULT '0' COMMENT '数据库名字',  `error_msg` varchar(500) NOT NULL DEFAULT '报错的程序',  `status` int(11) NOT NULL DEFAULT '2',  `creator` varchar(50) NOT NULL DEFAULT '',  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `operator` varchar(50) NOT NULL DEFAULT '',  `operate_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

3.2 收集的功能脚本

定义收集 DB_info的脚本collect_tables_info.py

# -*- coding: utf-8 -*-import sysimport osimport datetimeimport configparserimport pymysqlimport mysql_get_db_connectimport mysql_exec_sqlimport mysql_collect_exec_sqlimport pandas as pddef 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,remarkFROM cmdbdb.mysqldb_instance ;    """    exec_sql_tablesizeinfo = """select TABLE_SCHEMA,table_name,table_rows,data_length ,index_length,data_free,auto_incrementfrom information_schema.tableswhere 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表信息"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！