通過Python收集匯聚MySQL 表信息的實(shí)例詳解

更新時(shí)間：2021年10月25日 11:22:35 作者：東山絮柳仔

這篇文章主要介紹了通過Python收集匯聚MySQL 表信息的實(shí)例代碼，核心代碼是創(chuàng)建保存數(shù)據(jù)的腳本，收集的功能腳本，代碼簡單明了,需要的朋友可以參考下

一.需求

統(tǒng)計(jì)收集各個(gè)實(shí)例上table的信息，主要是表的記錄數(shù)及大小。

收集的范圍是cmdb中所有的數(shù)據(jù)庫實(shí)例。

二.公共基礎(chǔ)文件說明

1.配置文件

配置文為db_servers_conf.ini，假設(shè)cmdb的DBServer為119.119.119.119，單獨(dú)存放收集監(jiān)控?cái)?shù)據(jù)的DBserver為110.110.110.110. 這兩個(gè)DB實(shí)例的訪問用戶名一樣，定義在了[uid_mysql] 部分，需要去收集的各個(gè)DB實(shí)例，用到的賬號密碼是另一個(gè)，定義在了[collector_mysql]部分。

[uid_mysql]
dbuid = 用*戶*名
dbuid_p_w_d = 相*應(yīng)*密*碼

[cmdb_server]
db_host = 119.119.119.119
db_port = 3306


[dbmonitor_server]
db_host = 110.110.110.110
db_port = 3306

[collector_mysql]
collector = DB*實(shí)*例*用*戶*名
collector_p_w_d = DB*實(shí)*例*密*碼

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,注意需要導(dǎo)入上面的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()
        ##需要顯式關(guān)閉
        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()
        ##顯式關(guān)閉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 創(chuàng)建保存數(shù)據(jù)的腳本

用來保存收集表信息的表：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 '數(shù)據(jù)庫名字',
  `table_name` varchar(100) NOT NULL DEFAULT '' COMMENT '表名字',
  `table_rows` bigint NOT NULL DEFAULT 0 COMMENT '表行數(shù)',
  `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
;

收集過程，如果訪問某個(gè)實(shí)例異常時(shí)，將失敗的信息保存到表 gather_error_info 中，以便跟蹤分析。

create table `gather_error_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `app_name` varchar(150) NOT NULL DEFAULT '報(bào)錯(cuò)的程序',
  `host_ip` varchar(50) NOT NULL DEFAULT '0',
  `port` varchar(10) NOT NULL DEFAULT '3306',
  `db_name` varchar(60) NOT NULL DEFAULT '0' COMMENT '數(shù)據(jù)庫名字',
  `error_msg` varchar(500) NOT NULL DEFAULT '報(bào)錯(cuò)的程序',
  `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 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
                      ###一定要有一個(gè)exec_sql_insert_table_com，如果是exec_sql_insert_tablesize = exec_sql_insert_tablesize  %  ( db_host_ip.......
                      ####則提示報(bào)錯(cuò):報(bào)錯(cuò)信息是 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的錯(cuò)誤信息如下:' + str(TypeError))
              print(db_host_ip +'  '+str(db_port) + '登入異常無法獲取table信息,請檢查實(shí)例和訪問賬號!')
              exec_sql_error_sql = exec_sql_error  %  ( 'collect_tables_info',db_host_ip , str(db_port),'登入異常，獲取table信息失敗,請檢查實(shí)例和訪問的賬號!!!' )
              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('查詢無結(jié)果集')

collect_tables_info()

到此這篇關(guān)于通過Python收集匯聚MySQL 表信息的文章就介紹到這了,更多相關(guān)Python MySQL 表信息內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: