MySQL脚本集

事情的起因是服务器硬盘差不多占满了，所以想着删除下磁盘，写个脚本定期删除下一些文件，这样子就不用定期手工删除文件了，不过搞着搞着就突然跑到MySQL去了，所以本篇文章内容只是作为笔记，方便后期查阅。文章主要内容包含快速查找大文件，查看磁盘占用情况，MySQL定时备份脚本,MySQL定时优化脚本。

磁盘占用清理

首先查看下磁盘的占用情况，如下

# df是报告文件系统磁盘用量统计 h代表以可阅读的方式展示 KB MB GB l代表local本地文件系统
df -hl

然后我们可以去根目录下看看那个文件夹占用高

# du是预估文件使用情况的命令 h代表以可阅读的方式展示 d1代表只显示当前目录层级
du -hd1
## 或者
du -h --max-depth=1

df报告磁盘占用19G，而du报告出根目录只用了14G，还有5G有可能是正在运行的进程删除的文件，资源并未并释放。我们看看哪些文件已删除，但是资源没有释放

lsof | grep deleted

发现全是MySQL未释放文件，这些文件基本是MySQL全文索引中的数据，为了提高插入和查询的速度，上面通过du命令逐个查找文件，最后删除不需要的文件即可释放磁盘空间。

MySQL定时备份脚本

解决了磁盘占用问题，发现上次写的MySQL主从同步监控脚本不够日常使用,我们还需要定时备份的脚本以及定时优化的脚本,我们首先来编写备份脚本.如果数据过大脚本可以放在本地

#!/bin/bash
mkdir -p /data/mysql_backup
backup_dir="/data/mysql_backup"
dt=$(date +"%Y%m%d_%H%M")
echo "Backup Begin Date:" $(date +"%Y-%m-%d %H:%M:%S")
# 备份全部数据库
mysqldump -h$IP -P$PORT -u$USERNAME -p$PASSWORD -R -E -B $DATABASE --single-transaction > $backup_dir/mysql_backup_$dt.sql
find $backup_dir -mtime +7 -type f -name '*.sql' -exec rm -rf {} \;
echo "Backup Succeed Date:" $(date +"%Y-%m-%d %H:%M:%S")

MySQL定时优化脚本

MySQL OPTIMIZE是为了重组表数据和关联索引数据的物理存储，减少存储空间，提高访问表时的I/O效率，在InnoDB存储引擎下，使用如下脚本

#!/bin/bash
tables=$(mysql -h$IP -P$PORT -u$USERNAME -p$PASSWORD -D $DATABASE -A -Bse "SHOW TABLES")
for table_name in $tables
do
    # 此操作会锁表，一月次足以,错峰执行
    echo $table_name
    echo $(mysql -h$IP -P$PORT -u$USERNAME -p$PASSWORD -D $DATABASE -A -Bse "ALTER TABLE $table_name ENGINE='INNODB'")
    echo $(mysql -h$IP -P$PORT -u$USERNAME -p$PASSWORD -D $DATABASE -A -Bse "ANALYZE TABLE $table_name")
    check_result=$(mysql -h$IP -P$PORT -u$USERNAME -p$PASSWORD -D $DATABASE -A -Bse "check table $table_name" | awk '{ print $4 }')
    if [ "$check_result" = "OK" ]
    then
        echo "It's no need to repair table $table_name"
    else
        echo $(mysql -h$IP -P$PORT -u$USERNAME -p$PASSWORD -D $DATABASE -A -Bse "repair table $table_name")
    fi
done

启用定时任务及编写Docker脚本

写完脚本之后，我们还需要添加两个文件，一个是定时任务文本，一个是启用定时任务的shell，如下

# crontab.txt
# 每天凌晨2:00执行备份
0 2 * * * /data/script/backup.sh >> /var/log/backup.log
# 每隔一分钟执行一次主从同步检查
* * * * * /data/script/sync.sh >> /var/log/sync.log
# 每月28日执行一次优化
0 0 28 * * /data/script/optimize.sh >> /var/log/optimize.log

#!/bin/sh
# start cron entry.sh
/usr/sbin/crond -f -l 8

最后我们把脚本单独放在一个docker中执行

# MySQL主从同步监控脚本
FROM alpine:3.15.0
RUN apk update \
    && apk add mysql-client bash curl tzdata vim
WORKDIR /data/script
ADD sync.sh sync.sh
ADD backup.sh backup.sh
ADD optimize.sh optimize.sh
ADD entry.sh  entry.sh
ADD crontab.txt  crontab.txt
RUN chmod 755 sync.sh backup.sh optimize.sh entry.sh
RUN /usr/bin/crontab crontab.txt
ENV TIME_ZONE=Asia/Shanghai 
RUN ln -snf /usr/share/zoneinfo/$TIME_ZONE /etc/localtime && echo $TIME_ZONE > /etc/timezone
ENV LANG C.UTF-8
ENV IP 127.0.0.1
ENV PORT 3306
ENV USERNAME root
ENV PASSWORD password
ENV DATABASE sys
ENV TAG "生产环境服务"
ENTRYPOINT ["bash","/data/script/entry.sh"]

通过CI自动打包即可