自动备份Mysql数据库的shell文件

最新推荐文章于 2025-12-24 21:55:42 发布

原创最新推荐文章于 2025-12-24 21:55:42 发布 · 1.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #shell #数据库 #date #file #delete

Unix&Linux 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一个用于MySQL数据库备份的Shell脚本，并详细展示了如何通过cron定时任务实现每天自动备份及过期备份文件的自动删除。该脚本不仅实现了数据库的备份，还包含了压缩备份文件以及定期清理过期备份的功能。

1 编辑用于备份的shell文件

#!/bin/bash # # MySQL Backup Scripts. ######################################################################## #1 Backup database ######################################################################## USERNAME=root #PASSWORD= BACKUP_DIR1=/var/lib/mysql/dbbackup/ BACKUP_DIR2=/var/lib/mysql/db_mysql_backup/ BACKUP_DB1=mydbname BACKUP_FILE1=$BACKUP_DIR1/backup_$BACKUP_DB1`date '+%Y%m%d_%H%M%S'`.sql BACKUP_FILE2=$BACKUP_DIR2/backup_$BACKUP_DB1`date '+%Y%m%d_%H%M%S'`.sql # Start backup. #mysqldump -u$USERNAME -p$PASSWORD -B $BACKUP_DB1 --add-drop-table -R > $BACKUP_FILE1 #mysqldump -u$USERNAME -p$PASSWORD -B $BACKUP_DB1 --add-drop-table -R > $BACKUP_FILE2 mysqldump -u$USERNAME -B $BACKUP_DB1 --add-drop-table -R > $BACKUP_FILE1 mysqldump -u$USERNAME -B $BACKUP_DB1 --add-drop-table -R > $BACKUP_FILE2 echo `date '+%Y%m%d %H:%M:%S backupfile '`$BACKUP_FILE1 echo `date '+%Y%m%d %H:%M:%S backupfile '`$BACKUP_FILE2 # Compress backup file. gzip $BACKUP_FILE1 gzip $BACKUP_FILE2 echo `date '+%Y%m%d %H:%M:%S gzip file'`$BACKUP_FILE1 echo `date '+%Y%m%d %H:%M:%S gzip file'`$BACKUP_FILE2 ######################################################################## #2 Delete overdue mysql backup file. ######################################################################## # Get the date part. ARR1=`ls $BACKUP_DIR1 | cut -d '_' -f2` # Get 30 days ago. CUR_DATE=`date +'%Y%m%d' -d '-30 day'` # Delete the overdue file. for CUR_FILENAME in $ARR1 do RESULT=`echo $CUR_FILENAME | tr -d "[a-zA-Z]"` if [ $RESULT = "." ] then continue fi if [ "$RESULT" -le "$CUR_DATE" ] then rm -rf `echo "${BACKUP_DIR1}backup_${CUR_FILENAME}_*.sql.gz"` echo `date '+%Y%m%d %H:%M:%S rm -rf '`${BACKUP_DIR1}backup_${CUR_FILENAME}_*.sql.gz rm -rf `echo "${BACKUP_DIR2}backup_${CUR_FILENAME}_*.sql.gz"` echo `date '+%Y%m%d %H:%M:%S rm -rf '`${BACKUP_DIR2}backup_${CUR_FILENAME}_*.sql.gz fi done

2 编辑用于定时的文件每天3点10分执行备份

# vi /etc/crontab
在下面添加

# Backup mysql database
10 03 * * * su - root -c "sh /var/lib/mysql/dbbackup/backupdb.sh >> /var/lib/mysql/dbbackup/backupdblog.log"

3 重新启动crond
# /etc/rc.d/init.d/crond restart