MySQL历史数据归档以及释放磁盘空间 Shell 脚本

MySQL 历史数据归档脚本，对较久之前的数据进行备份，以释放磁盘空间的压力。清理历史数据表，注意数据无价，将数据备份好的情况下方可执行该脚本。

MySQL 历史数据归档脚本

[root@seichung ] vim /data/sh/history_backup.sh

#!/bin/bash
# By Seichung


# 数据库信息
Dbname='wordpress'
Host='localhost'
User='root'
Password='123456'
Port=3306

# 时间戳
Date=`date +'%Y%m%d%H%M%S'`

# 导出的历史数据表
Tables='/opt/history_tables.txt'

# 查看状态
mysql -h${Host} -u${User} -p${Password} -P ${Port} -e 'use ${Dbname};' &>/dev/null

# 判断该库是否存在
if [ $? = 0 ];then
    echo -e "The database '${Dbname}' is starting, please wait for the backup to finish……"
# 开始进行数据备份
    cat ${Tables} | while read table
    do
# 将同个库中的所有表都备份归档到同一个文件
        mysqldump -h${Host} -u${User} -p${Password} -P ${Port} --set-gtid-purged=OFF --single-transaction --databases ${Dbname} --tables ${table}  >> /data/backup/history_data/${Dbname}-history-data-${Date}.sql 2>/dev/null
    done
    if [ $? = 0 ];then
        echo "Database '${Dbname}' is backup successfully!" 2>/dev/null
    else
        echo "Database '${Dbname}' is backup failed!" 2>/dev/null
    fi
    exit 2
else
    echo "This database '${Dbname}' is not run. Please check it!"
    exit 2
fi

释放磁盘空间

[root@seichung ] vim history_clean.sh

# By Seichung
# 清理历史数据表，注意数据无价，将数据备份好的情况下方可执行该脚本!

# 数据库信息
Dbname='wordpress'
Host='localhost'
User='root'
Password='123456'
Port=3306

# 时间戳
Date=`date +'%Y%m%d%H%M%S'`

# 导出的历史数据表
Tables='/opt/history_tables.txt'

# 查看状态
mysql -h${Host} -u${User} -p${Password} -P ${Port} -e 'use ${Dbname};' &>/dev/null

# 判断该库是否存在
if [ $? = 0 ];then
  echo "The database ${Dbname} is being cleaned up, please wait for it to be cleaned up"  
  # 开始清理
  cat $Tables | while read table
  do
    mysql -h${Host} -u${User} -p${Password} -P ${Port} -e "truncate table ${table};" 2>/dev/null
  echo "The table '${table}' has been cleared"
  done
else
    echo "This database '${Dbname}' is not run. Please check it!"
    exit 2
fi