archery统计大表

archery实例、数据库名、表名、数据库物理文件大小(MB)、行数、数据空间(MB)、索引空间(MB)、碎片空间(MB)、碎片率(%)、创建时间、更新时间、是否删除

CREATE TABLE `database_table_max_top` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `archery_ins` varchar(128) NOT NULL COMMENT 'archery实例名',
  `database_name` varchar(100) NOT NULL COMMENT '库名',
  `table_name` varchar(100) NOT NULL COMMENT '表名',
  `memory_size` DECIMAL(14,2) NOT NULL COMMENT '物理文件大小(MB)',
  `rows` varchar(100) NOT NULL COMMENT '行数',
  `data_space` DECIMAL(14,2) NOT NULL COMMENT '数据空间(MB)',
  `index_space` DECIMAL(14,2) NOT NULL COMMENT '索引空间(MB)',
  `debris_space` DECIMAL(14,2) NOT NULL COMMENT '碎片空间(MB)',
  `debris_percentage` DECIMAL(14,2) NOT NULL COMMENT '碎片率(%)',
  `is_del` tinyint(1) DEFAULT '0' COMMENT '0不删除 1删除',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_archery_ins` (`archery_ins`),
  KEY `idx_database_name` (`database_name`),
  KEY `idx_table_name` (`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库表top排行表'

 

database_table_max_top_usernamepwd.py

import pymysql
import base64

import pymysql
import base64

select_user = base64.b64decode("bW9uaXRvcg==").decode("utf8")
select_password = base64.b64decode("TW9uaXRvcjc4Kg==").decode("utf8")
max_top = 10
metadata_host = "10.21.53.157"
metadata_port = 3316
metadata_user = base64.b64decode("YXJjaF93dA==").decode("utf8")
metadata_password = base64.b64decode("MnVJREA4U05GMk9oR2VjZw==").decode("utf8")

def select_database_table_max_top(host, port, sql):
    dataset = []
    connection = pymysql.connect(host=host, user=select_user, port=int(port), password=select_password, database='information_schema')
    with connection.cursor() as cursor:
        cursor.execute(sql)
        result = cursor.fetchall()
        if not result:
            return None
        for row in result:
            dataset.append(row)
    connection.close()
    return dataset

def exec_sql(host, port, sql):
    connection = pymysql.connect(host=host, user=metadata_user, port=int(port), password=metadata_password, database='information_schema')
    cursor = connection.cursor()
    cursor.execute(sql)
    connection.commit()
    cursor.close()
    connection.close()

sql=f"""
    SELECT 
        CONCAT("INSERT INTO archery.database_table_max_top (`id`, `archery_ins`, `database_name`, `table_name`, `memory_size`, `rows`, `data_space`, `index_space`, `debris_space`, `debris_percentage`, `is_del`, `create_time`, `update_time`) VALUES (NULL,",
        "'{archery_ins}'", ",'", TABLE_SCHEMA, "','", TABLE_NAME, "','", memory_size, "',", TABLE_ROWS, ",'", data_space, "','", index_space, "','", debris_space, "','", debris_percentage,"',0,now(),now());") AS insert_statement
    from  (SELECT TABLE_SCHEMA, 
    TABLE_NAME, 
    round((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as memory_size,
    TABLE_ROWS,
    round(DATA_LENGTH / 1024 / 1024, 2) as data_space,
    round(INDEX_LENGTH / 1024 / 1024, 2) as index_space,
    round(DATA_FREE / 1024 / 1024, 2) as debris_space,
    ROUND(DATA_FREE/data_length,2) AS debris_percentage
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA NOT IN  ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')  
    ORDER BY memory_size DESC limit {max_top}) x; 
"""

# 生产： select concat("('",archery_instance_name ,"', ","'",hostname,"', '",ports ,"')," ) from  db_login_path  where is_del = 0 and  cloud='华为云' and env='PROD' and db_type='MySQL'
# database_instance = [('10.2.30.28', '10.2.30.28', '3316'),('10.2.30.28', '10.2.30.28', '3316')]

database_instance = [
('hwprod-saas-zt(中台)', 'lanprodsaaszt.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-gxt观薪台', 'lanprodsaasgxt.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-cash', 'lanprodsaascash.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-crm', 'lanprodsaascrm.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-iot', 'lanprodiot.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-xgz薪公章-薪资产', 'lanprodsaasxgz.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-mixed-zt(中台)', 'lanprodsaasztmixed.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-xfk访客', 'lanprodsaasxfk.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-zt(中台)-中间件', 'lanprodsaasmiddware.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-xbx薪报销', 'lanprodsaasxbx.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-xsy薪税云', 'lanprodsaasxsy.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-hrm-assessment考勤', 'lanprodsaashrmassessment.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-hrm', 'lanprodsaashrm.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-xqy薪签约', 'lanprodsaasxqy.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-rpa', 'lanprodsaasrpa.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-oa', 'lanprodsaasoa.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-ods', 'lanprodsaasods.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-psi进销存', 'lanprodsaaspsi.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-pmp', 'lanprodsaasbapdpmp.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-xsy薪税云-mixed', 'lanprodsaasxsymixed.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-prod-saas-dsj-ods-mixed', 'lanprodsaasodsmixed.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-bigdata', 'lanprodsaasbigdata.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-im', 'lanprodsaasim.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-approval审批', 'lanprodsaasapproval.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-薪孵化_mysql', 'lanprodsaasxfh.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('prod-saas-xzg薪智购', 'lansaasxzg.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('prod-saas-xtrip薪商旅_mysql', 'lansaasxtrip.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-智会速记_mysql', 'lanprodsaasiim.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-aigc_mysql', 'lanprodaigc.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-hro社保人事考勤', 'lanprodhro.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-hro培训', 'lanprodhrotrain.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-hro-mysql8', 'lanprodhromysql8.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-hro招聘', 'lanprodhrozp.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-saas-hro计薪', 'lanprodhrojixin.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod_发放及账户', 'lanprodfulipay.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod_赋力聚合支付', 'lanprodfulijuhezhifu.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod_赋力现金管理', 'lanprodfulixjgl.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod_赋力渠道', 'lanprodfuliqudao.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod_赋力收入预结', 'lanprodfulidaysalary.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod_赋力白条', 'lanprodfuliious.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-现金日记账', 'lanprodfulixj.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-薪白条', 'lanprodfulinewious.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-经济监管', 'lanprodjjjg.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-薪用车', 'lanprodxyc.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-银河系', 'lanprodgalaxy.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-中台公共组件', 'lanprodztpublic.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-云审批', 'lanprodztapproval.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-中台用户系统', 'lanprodztuser.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-薪报销', 'lanprodxbx.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-薪收支', 'lanprodxsz.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-公告推送', 'lanprodpush.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-薪访客', 'lanprodvisitor.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-薪友IM', 'lanprodim.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-薪结算', 'lanprodsettlement.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033'),
('hwprod-zt-流水服务', 'lanprodztpaytrading.internal.cn-south-1.mysql.rds.myhuaweicloud.com', '6033')
]

for instance_item in database_instance:
    archery_instance_name = instance_item[0]
    host = instance_item[1]
    port = instance_item[2]
    select_result = select_database_table_max_top(host, port, sql)
    for select_item in select_result:
        archery_ins = archery_instance_name
        database = select_item[0]
        table = select_item[1]
        memory_size = select_item[2]
        rows = select_item[3]
        data_space = select_item[4]
        index_space = select_item[5]
        debris_space = select_item[6]
        debris_percentage = select_item[7]
        insert_statement = f"INSERT INTO archery.database_table_max_top (`id`,`archery_ins`, `database`, `table`, `memory_size`, `rows`, `data_space`, `index_space`, `debris_space`, `debris_percentage`,`is_del`,`create_time`,`update_time`) VALUES (NULL,'{archery_ins}', '{database}', '{table}', '{memory_size}', '{rows}', '{data_space}', '{index_space}', '{debris_space}', '{debris_percentage}',0,now(),now());"
        exec_sql(metadata_host, metadata_port, insert_statement)

 

database_table_max_top_loginpath.py

import subprocess

max_top = 10

def select_database_table_max_top(archery_ins,login_path):
    sql = f"""
    SELECT 
        CONCAT("INSERT INTO archery.database_table_max_top (`id`, `archery_ins`, `database_name`, `table_name`, `memory_size`, `rows`, `data_space`, `index_space`, `debris_space`, `debris_percentage`, `is_del`, `create_time`, `update_time`) VALUES (NULL,",
        "'{archery_ins}'", ",'", TABLE_SCHEMA, "','", TABLE_NAME, "','", memory_size, "',", TABLE_ROWS, ",'", data_space, "','", index_space, "','", debris_space, "','", debris_percentage,"',0,now(),now());") AS insert_statement
    from  (SELECT TABLE_SCHEMA, 
    TABLE_NAME, 
    round((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as memory_size,
    TABLE_ROWS,
    round(DATA_LENGTH / 1024 / 1024, 2) as data_space,
    round(INDEX_LENGTH / 1024 / 1024, 2) as index_space,
    round(DATA_FREE / 1024 / 1024, 2) as debris_space,
    ROUND(DATA_FREE/data_length,2) AS debris_percentage
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA NOT IN  ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')  
    ORDER BY memory_size DESC limit {max_top}) x;
    """
    try:
        process = subprocess.Popen(['/usr/local/mysql/bin/mysql', f'--login-path={login_path}', '-N','-e', sql], stdout=subprocess.PIPE, stderr=subprocess.PIPE)
        output, error = process.communicate()
        if output:
            return output.decode('utf-8')
        if error:
            print(error)
    except Exception as e:
        print("An error occurred:", str(e))

def exec_mysql_commond(login_path,sql):
    try:
        subprocess.Popen(['/usr/local/mysql/bin/mysql', f'--login-path={login_path}','-e', sql], stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    except Exception as e:
        print("An error occurred:", str(e))

# select concat("('",archery_instance_name ,"', ","'",login_path,"_monitor')," ) from  db_login_path  where is_del = 0 and  cloud='华为云' and env='PROD' and db_type='MySQL'

database_instance = [
('hwprod-saas-zt(中台)', 'prod-saas-zt_monitor'),
('hwprod-saas-gxt观薪台', 'prod-saas-gxt_monitor'),
('hwprod-saas-cash', 'prod-saas-cash_monitor'),
('hwprod-saas-crm', 'prod-saas-crm_monitor'),
('hwprod-iot', 'prod-saas-iot_monitor'),
('hwprod-saas-xgz薪公章-薪资产', 'prod-saas-xgz_monitor'),
('hwprod-saas-mixed-zt(中台)', 'prod-saas-ztmixed_monitor'),
('hwprod-saas-xfk访客', 'p_saas_xfk_monitor'),
('hwprod-saas-zt(中台)-中间件', 'prod-saas-middware_monitor'),
('hwprod-saas-xbx薪报销', 'prod-saas-xbx_monitor'),
('hwprod-xsy薪税云', 'prod-saas-xsy_monitor'),
('hwprod-saas-hrm-assessment考勤', 'prod-saas-hrmassessment_monitor'),
('hwprod-saas-hrm', 'prod-saas-hrm_monitor'),
('hwprod-saas-xqy薪签约', 'prod-saas-xqy_monitor'),
('hwprod-saas-rpa', 'prod-saas-rpa_monitor'),
('hwprod-saas-oa', 'prod-saas-oa_monitor'),
('hwprod-ods', 'prod_saas_ods_monitor'),
('hwprod-saas-psi进销存', 'prod-saas-psi_monitor'),
('hwprod-saas-pmp', 'prod_saas_bapd_monitor'),
('hwprod-prod-saas-dsj-ods-mixed', 'prod-saas-dsj-ods-mixed_monitor'),
('hwprod-saas-bigdata', 'prod-saas-bigdata_monitor'),
('hwprod-saas-im', 'prod-saas-im_monitor'),
('hwprod-saas-approval审批', 'prod-saas-approval_monitor'),
('hwprod-saas-薪孵化_mysql', 'prod-saas-xfh_monitor'),
('prod-saas-xzg薪智购', 'prod-saas-xzg_monitor'),
('prod-saas-xtrip薪商旅_mysql', 'prod-saas-xtrip_monitor'),
('hwprod-saas-智会速记_mysql', 'prod-saas-iim_monitor'),
('hwprod-aigc_mysql', 'prod-aigc_monitor'),
('hwprod-saas-hro社保人事考勤', 'prod_hro_monitor'),
('hwprod-saas-hro培训', 'prod_hrotrain_monitor'),
('hwprod-saas-hro-mysql8', 'prod_hromysql8_monitor'),
('hwprod-saas-hro招聘', 'prod_hrozp_monitor'),
('hwprod-saas-hro计薪', 'prod_hrojixin_monitor'),
('hwprod_发放及账户', 'prod_flzf_monitor'),
('hwprod_赋力聚合支付', 'prod_juhepay_monitor'),
('hwprod_赋力现金管理', 'prod_xjgl_monitor'),
('hwprod_赋力渠道', 'prod_fuliqd_monitor'),
('hwprod_赋力收入预结', 'prod_xzyj_monitor'),
('hwprod_赋力白条', 'prod_fulibt_monitor'),
('hwprod-现金日记账', 'prod_xjrjz_monitor'),
('hwprod-薪白条', 'prod_xbt_monitor'),
('hwprod-经济监管', 'prod_jjjg_monitor'),
('hwprod-薪用车', 'prod_xyc_monitor'),
('hwprod-银河系', 'prod_yhx_monitor'),
('hwprod-中台公共组件', 'prod_ztggzj_monitor'),
('hwprod-云审批', 'prod_ysp_monitor'),
('hwprod-中台用户系统', 'prod_ztyhxt_monitor'),
('hwprod-薪报销', 'prod_xbx_monitor'),
('hwprod-薪收支', 'prod_xsz_monitor'),
('hwprod-公告推送', 'prod_ggts_monitor'),
('hwprod-薪访客', 'prod_xfk_monitor'),
('hwprod-薪友IM', 'prod_xyim_monitor'),
('hwprod-薪结算', 'prod_xjs_monitor'),
('hwprod-zt-流水服务', 'prod-zt-lsfw_monitor')
]

for instance_item in database_instance:
    item_archery_ins = instance_item[0]
    item_login_path = instance_item[1]
    insert_statement = select_database_table_max_top(item_archery_ins,item_login_path)
    exec_mysql_commond("archery", insert_statement)