#!/bin/bash
# 注意mysql和mysqldump命令使用绝对路径
host="127.0.0.1"
user="root"
password="password"
port="3306"
backdir="/home/backup_mysql"
time=`date +%Y%m%d%H%M%S`
log="/home/backup_mysql/backup_mysql.log"
for db in `/data/mysql/bin/mysql -h$host -u$user -P$port -p$password -e"show databases"|egrep -v "information_schema|mysql|performance_schema|sys|Database"`
do
/data/mysql/bin/mysqldump -h$host -u$user -P$port -p$password --skip-opt ${db} | gzip > ${backdir}/${db}_${time}.sql.gz
[[ $? == 0 ]] && echo -e " 33[32m $time mysql backup success 33[0m" >> $${log} || echo -e " 33[31m $time mysql backup failed 33[0m" >> ${log}
done
find ${backdir} -mtime +30 -type f -name "*.sql.gz" -exec rm -rf {} ;
[[ $? == 0 ]] && echo -e " 33[32m $time rmdir success 33[0m" >> ${log} || echo -e " 33[31m $time rmdir failed 33[0m" >> ${log}
# chmod a+x /home/backup_mysql/backup_mysql.sh
# 00 4 * * * /usr/bin/sh /home/backup_mysql/backup_mysql.sh > /dev/null 2>&1
# systemctl restart crond.service
# 备份文件解压缩命令:gunzip ${db}_${time}.sql.gz,此时会出现一个以sql结尾的文件