MySQL——增量备份和全量备份

前言

MySQL简单的增量备份和全量备份脚本

内容

#!/bin/bash
##环境根据自己的来处理
PATH=/app/local/mysql/bin:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:~/bin
export PATH

echo "-----------------------------------------------------------"
echo "$(date): 开始备份数据库"
date_str=$(date +%Y%m%d_%H-%M-%S)
## 全量备份目录
backupdir=/app/mysqlbackups/
## 数据库目录
srcdir=/app/data/mysql/
## 增量备份目录
inrdir=$backupdir/increase_$date_str

mkdir -p $backupdir
mkdir -p $inrdir
cd $backupdir

# --master-data=2: CHANGE MASTER作为注释写入dump, 为1时为非注释，加载文件的时候CHANGE MASTER会直接生效
mysqldump -h localhost -uchenz --password='<mysql password>' --single-transaction --flush-privileges --flush-logs  --master-data=2 --routines --events --extended-insert --all-databases  | gzip> $backupdir/database_$date_str.sql.gz
echo "$(date): 备份数据库结束"

echo "$(date): 开始增量备份"
find $srcdir -name  'mysql-bin.*'  -mtime  -1 | awk '{if (NR>1){print $1}}' | xargs  cp -t $inrdir;
tar -zcvf inr_database_$date_str.tar.gz $inrdir --remove-files
echo "$(date): 增量备份结束"

echo "$(date): 开始清理老的备份数据库"
ls -1 database_*.sql.gz inr_database_*.tar.gz
find $backupdir -name "database_*.sql.gz" -type f -mtime +5 -exec rm {} \; > /dev/null 2>&1
find $backupdir -name "inr_database_*.tar.gz" -type f -mtime +5 -exec rm {} \; > /dev/null 2>&1
echo "$(date): 清理老的备份数据库结束"
ls -1 database_*.sql.gz inr_database_*.tar.gz

remote_backup_server=<mysqlBack_server_ip>
echo "$(date): 拷贝到远端备份机"
scp $backupdir/database_$date_str.sql.gz $remote_backup_server:/app/data/db_backup/
echo "$(date): 完成拷贝"
echo ""

remote_sftp_server=<sftp_server_ip>
echo "$(date): 拷贝到SFTP"
scp $backupdir/inr_database_$date_str.tar.gz $remote_sftp_server:/app/data/sftp/
echo "$(date): 完成拷贝"