数据库表结构变动发邮件脚本

#!/bin/sh

# 创建备份文件夹
oldBackHome=/opt/xxx_sql/old
newBackHome=/opt/xxx_sql/new
diffBackHome=/opt/xxx_sql/diff
# 保存的SQL文件统一文件名称
sqlNqme=xxx.sql
diffName=diff.txt

# 获取前一天年月日
yesterday=$(date -d last-day +%Y-%m-%d);
# 获取当前的年月日.
today=$(date +%Y-%m-%d);

# 在 old/new 的bak下,各创建名称为 "今天的文件夹"
mkdir -p $oldBackHome/bak/$today
mkdir -p $newBackHome/bak/$today

# 判断"昨天"文件夹是否存在
if [ -d $oldBackHome/bak/$yesterday/ ];then
	echo $oldBackHome/bak/$yesterday/ "the dir is exit"
else
	mkdir -p $oldBackHome/bak/$yesterday/
fi
if [ -d $newBackHome/bak/$yesterday/ ];then
	echo $newBackHome/bak/$yesterday/ "the dir is exit"
else
	mkdir -p $newBackHome/bak/$yesterday/
fi
if [ -d $diffBackHome/bak/$yesterday/ ];then
	echo $diffBackHome/bak/$yesterday/ "the dir is exit"
else
	mkdir -p $diffBackHome/bak/$yesterday/
fi

# 将sql文件,转移到对应的 bak 下
cp $oldBackHome/$sqlNqme $oldBackHome/bak/$yesterday/
cp $newBackHome/$sqlNqme $newBackHome/bak/$yesterday/

# 将当前 /opt/xxx_sql/new 下的SQL文件, 复制到 /opt/xxx_sql/old 下
cp $newBackHome/$sqlNqme $oldBackHome/

# 获取当前数据库的表结构信息
mysqldump -h 127.0.0.1 -uroot -p********# DB -d xxx> /opt/xxx_sql/new/xxx.sql

# 将已存在的差异文件转移到 bak 中
cp $diffBackHome/$diffName $diffBackHome/bak/$yesterday/


# 执行对比 oldBackHome 和 newBackHome 下的文件差异
diff $oldBackHome/$sqlNqme $newBackHome/$sqlNqme > $diffBackHome/$diffName

# 剔除 AUTO_INCREMENT= 自增列相关变更
sed -i '/AUTO_INCREMENT=/d' $diffBackHome/$diffName
echo "Message : xxx.sql 's diff option is succ!";

# 将变动差异发送邮件
mail -s "测试服表结构变动" xxx@xxx.net < /opt/xxx_sql/diff/diff.txt

echo "excute ~ xxx_diff.sh done";

---

# 2018-11-16 sql变动发送邮件
set from=xxx@xxx.net
set smtp=smtp.exmail.qq.com
set smtp-auth-user=xxx@xxx.net
set smtp-auth-password=xxx
set smtp-auth=login

--- 某些云服务器默认是禁止使用25端口的，请联系云服务提供商开放25端口