自动执行MySQL查询以获得每周更新

Question

我有一个数据库，在那里我使用MySQL查询获得每个表的大小

SELECT
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = "....."
ORDER BY
(DATA_LENGTH + INDEX_LENGTH)
DESC;

我需要每周运行这个查询来获取表的大小，并检查它们的大小之间的差异。如何通过脚本实现自动化？

Answer 1

您应该编写一个python代码，并创建可以随时运行的cronjob。在python脚本中，将此查询设为

import sqlite3
import os
query ="SELECT
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = "....."
ORDER BY
(DATA_LENGTH + INDEX_LENGTH)
DESC;"
# connect to your db
sql_connect = sqlite3.connect('example.db')
results = cursor.execute(query).fetchall()
# do stuff here, let's say I write the difference value to a file
# the value you get from the query after processe results
value = 100000
os.system(f"echo {value} >> log.txt")
sql_connect.close()

然后使用bash-check将其作为cronjob，在这里进行简单解释：https://help.dreamhost.com/hc/en-us/articles/215767047-Creating-a-custom-Cron-Job