查看mysql数据库空间剩余

SELECT 
    TABLE_SCHEMA AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Data Size (MB)',
    ROUND(SUM(data_free) / 1024 / 1024, 2) AS 'Free Space (MB)'
FROM 
    information_schema.TABLES
GROUP BY 
    TABLE_SCHEMA;

#!/bin/bash

# MySQL连接配置
MYSQL_USER="your_mysql_user"
MYSQL_PASS="your_mysql_password"
MYSQL_HOST="your_mysql_host"

# 查询数据库空间剩余
SPACE=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "SELECT 
    TABLE_SCHEMA AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Data Size (MB)',
    ROUND(SUM(data_free) / 1024 / 1024, 2) AS 'Free Space (MB)'
FROM 
    information_schema.TABLES
GROUP BY 
    TABLE_SCHEMA;" | grep -v "Database" | awk '{print $3}')

# 设置警报阈值
ALERT_THRESHOLD=100

# 检查空间剩余并发送警报
if (( $(echo "$SPACE < $ALERT_THRESHOLD" | bc -l) )); then
    echo "Alert: MySQL database free space is below $ALERT_THRESHOLD MB. Current free space: $SPACE MB" | mail -s "MySQL Database Space Alert" your_email@example.com
fi