转载：mariadb/mysql定时备份脚本

在DOCKER下运行的要点

进入docker，使用：
bash /root/脚本名，否则会因为解释器报错

开启mariadb的binlog，修改my.cnf

server-id = 1
log_bin = /var/lib/mysql/mariadb-bin
log_bin_index = /var/lib/mysql/mariadb-bin.index

全量备份脚本

#!/bin/bash
# 全备方式，一般在从机上执行，适用于小中型mysql数据库:
source /etc/profile # 加载系统环境变量
source ~/.profile # 加载用户环境变量
# 定义全局变量
#登录数据库用户名
user="root"
#访问数据库密码
password="prQX4e4CmmPiBhGZ"
#访问地址
host="localhost"
#访问数据库端口
port="3306"
#备份数据库名称，多个数据库用空格进行间隔
# db=("mydb") 可选
db=$(mysql -uroot -p密码 -e "show databases" | grep -Ev "Database|sys|information_schema")

local="--single-transaction"
#数据库位置
mysql_path="/var/lib/mysql"
#备份地址
backup_path="/tmp/mysql-backup"
date=$(date +%Y%m%d_%H:%M:%S)
day=30
#日志路径
backup_log="/var/log/mysql/mysql-backlog.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
for db in $db;do
  backup_sql ${db[i]} >> $backup_log 2>&1
done

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

增量备份脚本

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


source /etc/profile # 加载系统环境变量
source ~/.profile # 加载用户环境变量
# 定义全局变量
#存放二进制备份文件的压缩包目录
backup_path="/tmp/mysql-bin-backup"
#二进制备份文件所在位置
mysqlbin_path="/var/lib/mysql/mariadb-bin"

backup_log="/var/log/mysql/mysql-binbacklog.log"
#二进制日志的索引文档
mysqlbinfile="/var/lib/mysql/mariadb-bin.index"
date=$(date +%Y%m%d_%H:%M:%S)
day=30

# 刷新新的mysql-bin.0000*文件
mysqladmin -uroot -pprQX4e4CmmPiBhGZ 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 $backup_name.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}'

全量备份还原

[root@localhost mysql_back]#tar --force-local -zxvf LOGLEO.000015_20200708_09:17:27.tar.gz

[root@localhost mysql_back]# mysql -uroot -p你的mysql密码 要还原的数据库名 </home/mysql-back/LOGLEO.000015_20200708_09:17:27.log

增量备份还原

**查看自己的二进制目录**
[root@localhost ~]#cd /home/mysql-binback
[root@localhost mysql_binback]# ls
LOGLEO.000014_20200708_09:41:21.tar.gz
LOGLEO.000015_20200708_09:41:21.tar.gz
LOGLEO.000017_20200708_09:44:58.tar.gz
LOGLEO.000018_20200708_11:03:15.tar.gz
LOGLEO.000019_20200708_11:07:31.tar.gz
LOGLEO.000020_20200708_11:08:15.tar.gz
LOGLEO.000021_20200708_13:54:59.tar.gz
LOGLEO.000022_20200709_01:00:01.tar.gz
LOGLEO.000023_20200710_01:00:01.tar.gz

**解压文件操作**
[root@localhost mysql_binback]# tar --force-local -zxvf LOGLEO.000023_20200710_01:00:01.tar.gz 
LOGLEO.000023


[root@localhost mysql_binback]#mysqlbinlog  LOGLEO.000023 | mysql -uroot -p你的数据库密码