MySQL数据库全量备份与增量备份

MySQL数据库备份脚本

一、增量备份
1、增量备份需要开启MySQL的binlog日志功能(仅限于非集群数据库及主从数据库，用于单个数据库)，同时配置binlog二进制为混合模式或ROW模式。

cat /etc/my.cnf
  log-bin=/var/lib/mysql/mysql-bin
  binlog_format=mixed  #binlog_format=ROW

2、增量备份脚本

cat /opt/scripts/backup-mysql-incre.sh
#!/bin/bash
# 增量备份方式,在从机上执行,适用于中大型mysql数据库
# 同时数据库配置文件必须开启binlog二进制文件

source /etc/profile # 加载系统环境变量
source ~/.bash_profile # 加载用户环境变量

# 定义全局变量
backup_path="/data/backup/mysqlbak"
mysqlbin_path="/var/lib/mysql"
backup_log="/data/backup/mysqlbak/backup.log"
mysqlbinfile="/var/lib/mysql/mysql-bin.index"
date=$(date +%Y%m%d_%H:%M:%S)
day=30

# 刷新新的mysql-bin.0000*文件
mysqladmin -uroot -pHuawei@123 flush-logs

statistics=`cat $mysqlbinfile|wc -l`
num=0
# 判断是否存在目录,不存在则创建目录
if [ ! -e $backup_path ];then
  mkdir -p $backup_path
fi

# 删除30天以前备份
find $backup_path -type f -mtime +$day -exec rm -rf {} \; > /dev/null 2>&1

# for循环对比是否存在或是否为最新的文件
echo "开始备份数据库: ..."
for file in `cat $mysqlbinfile`
do
  # basename用于截取mysql-bin.0000*文件名,去掉./mysql-bin.0000*前面的./
  dbname=`basename $file`
  backup_name=`basename $file`_$date
  statistics=`expr $num + 1`
  cd $backup_path
  # 判断是否刷新二进制文件
  if [ $num != $statistics ];then
    dest=$backup_path/$dbname*
    # 判断文件是否存在,不存在则备份
    if [ ! -e $dest ];then
      cp $mysqlbin_path/$dbname $backup_path/
      # --force-local，压缩文件中带有冒号需要加上--force-local参数
      tar czvf $backup_name.tar.gz $dbname --force-local
      size=$(du $dbname.tar.gz -sh | awk '{print $1}')
      rm -rf $dbname
      echo "$dbname 备份 $dbname($size) 成功" >> $backup_log
      du $backup_path/* -sh | grep mysql-bin | awk '{print "文件:" $2 ",大小:" $1}'
    else
      echo "$dbname 备份 $dbname 已存在" >> $backup_log
      continue
    fi
  fi
  num+=1
done
 
echo "备份结束,结果查看 $backup_log"
du $backup_path/* -sh | grep mysql-bin | awk '{print "文件:" $2 ",大小:" $1}'

3、保存退出加上可执行权限

chmod +x /opt/scripts/backup-mysql-incre.sh

二、全量备份
1、全量备份是直接对数据库进行备份(可用于集群以及主从数据库),备份参数说明：

  --single-transaction数据一致性
  --lock-all-tables为所有表加读锁(于--single-transaction互斥)
  --routinge存储过程与函数、--all-databases备份所有库
  --triggers触发器、--events记录事件
  --master-data=2在备份文件中记录当前二进制日志的位置，并且为注释的，1是不注释掉在主从复制中才有意义
  #--flush-logs日志滚动一次

2、全量备份脚本

cat /opt/scripts/backup-mysql-all.sh
#!/bin/bash
# 全备方式，一般在从机上执行，适用于小中型mysql数据库

source /etc/profile # 加载系统环境变量
source ~/.bash_profile # 加载用户环境变量

# 定义全局变量
user="root"
password="123"
host="localhost"
port="3306"
db=("zabbix")
local="--single-transaction"
mysql_path="/usr/share/mysql"
backup_path="/data/backup/mysqlbak"
date=$(date +%Y%m%d_%H:%M:%S)
day=30
backup_log="/data/backup/mysqlbak/backup.log"

# 判断是否存在目录,不存在则创建目录
if [ ! -e $backup_path ];then
  mkdir -p $backup_path
fi

# 删除30天以前备份
find $backup_path -type f -mtime +$day -exec rm -rf {} \; > /dev/null 2>&1

echo "开始备份数据库: ${db[*]}"

# 备份数据库后压缩
backup_sql(){
  dbname=$1
  backup_name="${dbname}_${date}.sql"
  mysqldump -h $host -P $port -u $user -p$password $lock --default-character-set=utf8 --flush-logs -R $dbname > $backup_path/$backup_name
  if [[ $? == 0 ]];then
    cd $backup_path
    # tar --force-local参数压缩带有冒号的压缩包
    tar czvf $backup_name.tar.gz $backup_name --force-local
    size=$(du $backup_name.tar.gz -sh | awk '{print $1}')
    rm -rf $backup_name
    echo "$date 备份 $dbname($size) 成功"
  else
    cd $backup_path
    rm -rf $backup_name
    echo "$date 备份 $dbname 失败"
  fi
}

# 多个库循环备份
length=${#db[@]}
for ((i=0;i<$length;i++));do
  backup_sql ${db[i]} >> $backup_log 2>&1
done

echo "备份结束,结果查看 $backup_log"
du $backup_path/*$date* -sh | awk '{print "文件:" $2 ",大小:" $1}'

3、保存退出加上可执行权限

chmod +x /opt/scripts/backup-mysql-all.sh

三、开启定时任务

crontab -e
# 每个周日凌晨一点执行数据库全量备份脚本
00 01 * * 0 /opt/scripts/backup-mysql-all.sh > /dev/null 2>&1
# 周一到周六凌晨一点执行数据库增量备份脚本
00 01 * * 1-6 /opt/scripts/backup-mysql-incre.sh > /dev/null 2>&1

查看定时任务

crontab -l