MySQL定时备份数据库shell脚本

• mysqldump命令备份数据
• 整个shell脚本
• 使用crontab定期执行备份脚本

mysqldump命令备份数据

数据库导出，主要用到mysqldump 命令

mysqldump  -h$DB_HOST -u$DB_USER -p$DB_USER_PASSWD $dbname > $TODAYBACKUPPATH/$dbname.sql

整个shell脚本

具体脚本如下：

DB_HOST=\'localhost\'DB_USER=\'root\'DB_USER_PASSWD=\'***\'DB_NAME=\'/home/dbbackup/dbnames.txt\'BACKUP_PATH=\'/home/dbbackup/mysql/\'printf \"createing backup folder!\\n\"DATETIME=$(date +%Y%m%d_%H%M)TODAYBACKUPPATH=\"$BACKUP_PATH$DATETIME\"#创建备份文件夹if [ ! -d \"$TODAYBACKUPPATH\" ];thenprintf \"create dir $TODAYBACKUPPATH \\n\"mkdir -pv \"$TODAYBACKUPPATH\"fiprintf \"checking for databases names file\\n\"#定义执行备份脚本，读取文件中的数据库名称，注意按行读写，不校验是否存在该库run_backup (){#读取 DB_NAME文件中的内容for dbname in $(<$DB_NAME);do#遍历循环执行备份sqlprintf \"now starting backup database %s\\n\" $dbnameprintf \"mysqldump -h$DB_HOST -u$DB_USER -p$DB_USER_PASSWD $dbname > $TODAYBACKUPPATH/$dbname.sql \\n\"mysqldump  -h$DB_HOST -u$DB_USER -p$DB_USER_PASSWD $dbname > $TODAYBACKUPPATH/$dbname.sqldone}#执行压缩的函数run_tar(){# 将今日的备份文件打包成压缩文件compress_file=\"$TODAYBACKUPPATH.tar.gz\"cd $BACKUP_PATHpwdtar -czvf $compress_file $DATETIMEprintf \"compress complete!\\n\"#删除备份文件夹rm -rf $TODAYBACKUPPATH}#删除30天前的文件del_30day_backup_file(){find ${BACKUP_PATH} -name \"*.tar.gz\" -mtime +30 -exec rm -rfv {} \\;}_main(){printf \"starting backup of all db listed in file $DB_NAME \\n\"printf \"time is $DATETIME\\n\"run_backuprun_tardel_30day_backup_fileexit 0}_main

dbnames.txt中内容

脚本中的DB_HOST ，DB_USER_PASSWD，DB_NAME，BACKUP_PATH 根据情况自己更改。dbnames.txt 中一行只能有一个数据库名

使用crontab定期执行备份脚本

crontab 使用详解

crontab -e#将下面这条脚本意思是 每天晚上23点50分钟时调用 /home/firm-pms/39.dump.sh 脚本50 23 * * * /home/firm-pms/39.dump.sh >> /home/firm-pms/back_39_log.log

crontab -l 查看定时任务