Linux上通过mysqldump命令实现自动备份
Linux上通过mysqldump命令实现自动备份
直接上代码
#!/bin/bash
mysql_user="root"
mysql_host="localhost"
mysql_port="3306"
mysql_charset="utf8mb4"backup_location=/home/mysql/mysql_back/sql
# 是否开始自动删除过期文件,过期时间10天
expire_backup_delete="ON"
expire_days=10backup_time=$(date +%Y%m%d%H%M)
backup_dir=$backup_locationwelcome_msg="Welcome to use MySQL backup tools!"
# 数据库密码文件
mysql_defaults_file="/home/mysql/mysql_back/.my.cnf"# 检查MySQL实例是否正常运行
mysql_ps=$(ps -ef | grep mysql | wc -l)
mysql_listen=$(netstat -an | grep LISTEN | grep $mysql_port | wc -l)if [ $mysql_ps -eq 0 ] || [ $mysql_listen -eq 0 ]; thenecho "ERROR: MySQL is not running! Backup stopped!"exit 1
elseecho $welcome_msg
fi# 开始备份
/usr/local/mysql/bin/mysqldump --defaults-extra-file=$mysql_defaults_file --add-drop-table -h $mysql_host -P $mysql_port --default-character-set=$mysql_charset --hex-blob dflow_client --single-transaction --routines --events | gzip > $backup_dir/dflow_client-$backup_time.sql.gz
flag=$?
if [ $flag -eq 0 ]; thenecho "Database backup success. Backup saved to $backup_dir/dflow-$backup_time.sql.gz"
elseecho "Database backup failed!"exit 1
fi# 删除过期数据
if [ "$expire_backup_delete" == "ON" ] && [ "$backup_location" != "" ]; thenexpired_file_prefix="dflow-$(date -d "-$expire_days days" +%Y%m%d)"find $backup_location/ -type f -name "$expired_file_prefix*.sql.gz" -deleteecho "Expired backup data deleted!"
fi
说明：
文件备份输出目录：backup_location=/home/mysql/mysql_back/sql
数据库信息文件： mysql_defaults_file=“/home/mysql/mysql_back/.my.cnf”
my.cnf文件
[client]
user=root
password=1223
设置定时任务
#查看任务
crontab -l
#进入任务编辑
crontab -e
#设置凌晨一点执行，并且输出日志
00 01 * * * sh /home/mysql/mysql_back/dflow_bak.sh >> /home/mysql/mysql_back/crontabLog/info.log 2>>/home/mysql/mysql_back/crontabLog/err.log
脚本目录：/home/mysql/mysql_back
日志输出目录：/home/mysql/mysql_back/crontabLog
这个备份一个数据库，备份多个使用这个
/usr/local/mysql/bin/mysqldump --defaults-extra-file=$mysql_defaults_file --add-drop-table -h $mysql_host -P $mysql_port --default-character-set=$mysql_charset --hex-blob --single-transaction --routines --events --databases db1 db2 db3 | gzip > $backup_dir/databases-$backup_time.sql.gz
db1 db2 db3 是数据库名称