mysqldump增量备份脚本
时间: 2023-11-20 11:56:29 浏览: 96
以下是一个mysqldump增量备份脚本的示例:
```shell
#!/bin/bash
# 定义备份目录和日志文件路径
backup_dir="/data/backup/mysql"
log_file="/data/backup/mysql/backup.log"
# 获取当前时间
now=$(date +"%Y-%m-%d-%H-%M-%S")
# 定义全量备份函数
function full_backup() {
# 创建备份目录
mkdir -p $backup_dir/full
# 使用mysqldump备份所有数据库
/usr/local/mysql/bin/mysqldump -uroot -p123456 --all-databases > $backup_dir/full/all-databases-$now.sql
# 记录备份日志
echo "[$now] Full backup success" >> $log_file
}
# 定义增量备份函数
function incremental_backup() {
# 创建备份目录
mkdir -p $backup_dir/incremental
# 获取上次备份的binlog文件名和位置
last_binlog=$(tail -n 1 $backup_dir/last_binlog)
last_binlog_file=$(echo $last_binlog | awk '{print $1}')
last_binlog_pos=$(echo $last_binlog | awk '{print $2}')
# 获取当前binlog文件名和位置
current_binlog=$(mysql -uroot -p123456 -e "show master status\G" | grep -E "File|Position")
current_binlog_file=$(echo $current_binlog | awk '{print $2}')
current_binlog_pos=$(echo $current_binlog | awk '{print $4}')
# 复制上次备份到当前备份之间的binlog文件到备份目录
if [ "$last_binlog_file" != "$current_binlog_file" ]; then
for file in $(ls /usr/local/mysql/data/mysql-bin.0*); do
if [ "$file" \> "/usr/local/mysql/data/$last_binlog_file" ] && [ "$file" \<= "/usr/local/mysql/data/$current_binlog_file" ]; then
cp $file $backup_dir/incremental/
fi
done
else
cp /usr/local/mysql/data/$current_binlog_file $backup_dir/incremental/
fi
# 记录备份日志
echo "$current_binlog_file $current_binlog_pos" > $backup_dir/last_binlog
echo "[$now] Incremental backup success" >> $log_file
}
# 判断当前时间是否为周日凌晨3点,如果是则执行全量备份,否则执行增量备份
if [ $(date +%w) -eq 0 ] && [ $(date +%H) -eq 3 ]; then
full_backup
else
incremental_backup
fi
```
该脚本会在周一到周六凌晨3点执行增量备份,将mysql-bin.00000*文件复制到指定目录;在周日凌晨3点执行全量备份,使用mysqldump将所有的数据库导出,并删除上周留下的备份文件。备份文件会保存在指定的备份目录中,并记录备份日志。
阅读全文