MySQL备份

准备环境

编辑/etc/my.cnf,再[mysqld]板块添加如下，重启服务

[mysqld]
datadir=/usr/local/mysql/data
port=3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
socket=/tmp/mysql.sock
symbolic-links=0
max_connections=400
innodb_file_per_table=1
lower_case_table_names=1
#开启、并且可以将mysql-bin改为其他的日志名
log-bin=mysql-bin
#添加id号，如果做主从，就不能一样
server-id=1
#超过200M将生成新文件，最大和默认值是1GB
max_binlog_size=1G
#表示binlog使用最大内存的数，默认1M
max_binlog_cache_size=1M
#表示binlog日志保留时间，默认单位是天
expire_logs_days=7

创建用户并且授权

#创建back用户
create user 'back'@'localhost' identified by 'tang1611';
#授权
grant reload,lock tables,replication client,create tablespace,process,super on *.* to 'back'@'localhost' ;
grant create,insert,select on percona_schema.* to 'back'@'localhost';
use mysql;
update user set user.Host='%' where user.User='back';
flush privileges;

安装innobackupex

#安装依赖库
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev-devel
#安装
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

innobackupex全备份

#创建备份目录
mkdir -p /root/bin
mkdir -p /bak/mysql-xback

编写脚本
vim /root/bin/mybak-all.sh

#!/bin/bash
#全备份
#指定备份目录
backup_dir="/bak/mysql-xback"

#检查目录是否存在，不存在则创建
[[ -d $backup_dir ]] || mkdir -p $backup_dir
if [[ -d $backup_dir/all-backup ]];then
        echo "全备份已经存在"
        exit 1
fi

#开始备份
innobackupex --defaults-file=/etc/my.cnf --user=back --password='tang1611' --no-timestamp $backup_dir/all-backup &> /tmp/mysql-backup.log

#查看临时目录
tail -n 1 /tmp/mysql-backup.log | grep 'completed OK!'
if [[ $? -eq 0 ]];then
        echo "备份成功"
        echo "all-backup" > /tmp/mysql-backup.txt
else
        echo "备份失败"
        exit 1
fi

innobackupex增量备份

编写脚本
vim /root/bin/mybak-section.sh

#!/bin/bash
#增量备份
#备份目录
backup_dir="/bak/mysql-xback"

#新旧备份
old_dir=`cat /tmp/mysql-backup.txt`
new_dir=`date +%F-%H-%M-%S`

#检查目录
if [[ ! -d ${backup_dir}/all-backup ]];then 
        echo "还没有全备份，请先进行全备份！"
        exit 1
fi

#执行/usr/bin/innobackupex --user=back --password='tang1611' --no-timestamp --incremental --incremental-basedir=${backup_dir}/${old_dir} ${backup_dir}/${new_dir} &> /tmp/mysql-backup.log

tail -n 1 /tmp/mysql-backup.log | grep 'completed OK!'
if [[ $? -eq 0 ]];then
        echo "${new_dir}" > /tmp/mysql-backup.txt
else
        echo "备份失败"
        exit 1
fi

恢复

#将已提交的事物重放
innobackupex --apply-log --redo-only /usr/local/back/mysql/all/2020-03-16_11-38-50
#整合第一个增量备份数据到全量备份数据里面
innobackupex --apply-log --redo-only /usr/local/back/mysql/all/2020-03-16_11-38-50 --incremental-dir=//usr/local/back/mysql/increment/2020-03-16_11-40-50
#整合第二个增量备份数据到全量备份数据里面
innobackupex --apply-log --redo-only /usr/local/back/mysql/all/2020-03-16_11-38-50 --incremental-dir=//usr/local/back/mysql/increment/2020-03-16_11-45-30
#此时数据已经是最后一次增量备份的数据了，执行恢复
innobackupex --copy-back /usr/local/back/mysql/all/2020-03-16_11-38-50