定时获取MySQL库的大小

定时获取MySQL库的大小

获取数据库单个库的大小命令

[root@admin ~]# cat db_size.txt
mysql -h 192.8.1.1 -uUSER -pPASSWORD -e'use information_schema; select concat(round(((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024),2),"MB") as db_size from information_schema.tables where table_schema="db_name";'

将执行结果追加写入到一个文件中

[root@admin ~]# cat db_size.py 
#! /usr/bin/env python
import subprocess
import time

with open("db_size","r") as a:
    b=a.readline().strip()
    res = subprocess.Popen(
        b.decode("utf-8"),
        shell=True,
        stdout=subprocess.PIPE,
        stderr=subprocess.PIPE, )
data = res.stdout.read().decode("utf-8").strip()+"\n\n"

with open("data_size","a",) as data_a:
    data_a.write(time.strftime("%Y-%m-%d %H:%M:%S")+"\n")
    data_a.write(data)

定时执行

[root@admin ~]# crontab -e
30 0 * * * /opt/db_size.py