在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你的数据库密码
