一点一滴努力,满仓满谷收成

1、统计某一个库中各表空间占用多少MB

use information_schema;

SELECT TABLE_NAME,
       CONCAT(TRUNCATE(DATA_LENGTH / 1024 / 1024, 2), ' mb') AS DATA_SIZE,
       CONCAT(TRUNCATE(INDEX_LENGTH / 1024 / 1024, 2), ' mb') AS INDEX_SIZE
  FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'zabbix'
 GROUP BY TABLE_NAME
 ORDER BY DATA_LENGTH DESC;

2、查看会话

MariaDB [(none)]> show processlist;

3、使用python程序清理zabbix history_uint表历史数据

#!/usr/bin/python

import MySQLdb

# Open database connection
conn = MySQLdb.connect(host='127.0.0.1'  
                       ,user='root'  
                       ,passwd=''  
                       ,db='zabbix')  

# prepare a cursor object using cursor() method
cursor = conn.cursor()

sql = "SELECT distinct(itemid) FROM history_uint"
#clock_d = ['2019-01-28','2019-02-28','2019-03-28','2019-04-28','2019-05-28','2019-06-28','2019-07-28','2019-08-28','2019-09-28','2019-10-28','2019-11-28','2019-12-28']
clock_d = ['2019-01-28','2019-02-28','2019-03-28','2019-04-28','2019-05-28','2019-06-15']

# Execute the SQL command
cursor.execute(sql)
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
for row in results:
  item = str(row[0])
  print(item)
  for cd in clock_d:
    sql_d = "delete from history_uint where itemid=" + item + " and clock <= UNIX_TIMESTAMP(" + "'" + cd + "'" + ")"
    try:
        cursor.execute(sql_d)
        conn.commit()
    except:
      conn.rollback()
      print('exception')
   
# disconnect from server
conn.close()