mysql5.7的xtrabackup全量和增量备份

mysql5.7  xtrabackup全量备份

1.安装 mysql-community-libs-compat-5.7.29-1.el7.x86_64.rpm依赖

wget  https://downloads.mysql.com/archives/get/p/23/file/mysql-community-libs-compat-5.7.29-1.el7.x86_64.rpm

rpm -ivh    mysql-community-libs-compat-5.7.29-1.el7.x86_64.rpm

2.下载安装xtrabackup

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

yum install  percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

3.创建授权mysql备份用户（主库创建）

mysql -S /var/lib/mysql/mysql.sock  -uroot -p'Passw0rd.^xdkj@2020'

CREATE USER 'backup'@'localhost' IDENTIFIED BY ' sckj1qaz@WSX3edc ';

GRANT reload,lock tables,replication client,CREATE TABLESPACE,PROCESS,SUPER,CREATE,INSERT,SELECT on *.* to 'backup'@'localhost';

flush privileges;

    

4.创建mysql全备

添加计划任务

0 1 * * 6  sh /opt/mysqlfullbackup.sh

脚本内容

#!/bin/bash

backupdir=/mnt/sdb/mysql_fullbackup/

date=$(date +%Y%m%d)

mysqlsocket=`grep socket /etc/my.cnf |awk   '{print $NF}'|head -1`

mkdir -p /mnt/sdb/mysql_fullbackup/$date

/bin/innobackupex --defaults-file=/etc/my.cnf --user=backup --password=sckj1qaz@WSX3edc  --socket=$mysqlsocket   /mnt/sdb/mysql_fullbackup/$date --no-timestamp

#删除一个月以前的数据

find  $backupdir -maxdepth 1 -mtime  +60  -type d  -exec rm -rf {} \;

mysql5.7 xtrabackup全备恢复

1.停止数据库

systemctl stop  mysqld

2.删除之前数据目录和日志文件

rm -rf /var/lib/mysql

rm -rf /var/log/mysqld.log

3.准备恢复

innobackupex    --apply-log  /data/backup/2021-04-08_21-22-03/

4.恢复数据

innobackupex --copy-back /data/backup/2021-04-08_21-22-03/

5.修改数据目录属主属组

chown -R mysql:mysql /var/lib/mysql

6.启动mysql

systemctl start  mysqld

查看数据是否恢复正常

mysql5.7 xtrabackup增量备份

1.基于上次全备第一次增量备份

/bin/innobackupex --defaults-file=/etc/my.cnf --user=backup --password=sckj1qaz@WSX3edc  --socket=/mnt/sdb/mysql/mysql.sock   --incremental  /backup_incremental --incremental-basedir=/backup/2021-07-06_10-52-28/

参数解释

--incremental增量备份路径     

--incremental-basedir上次全量备份位置

2.添加计划任务

0 2 * * *  sh /opt/mysqlincremental.sh

脚本内容

#!/bin/bash

date=$(date +%Y%m%d)

dir=$(cd /mnt/sdb/mysql_fullbackup && ls -lt|sed -n '2p'|awk '{print $9}')

mysqlsocket=`grep socket /etc/my.cnf |awk   '{print $NF}'|head -1`

mkdir -p /mnt/sdb/mysql_incremental/$date

/bin/innobackupex --defaults-file=/etc/my.cnf --user=backup --password=sckj1qaz@WSX3edc  --socket=$mysqlsocket   --incremental  /mnt/sdb/mysql_incremental/$date  --incremental-basedir=/mnt/sdb/mysql_fullbackup/$dir --no-timestamp

#删除一个月以前的数据

find   /mnt/sdb/mysql_incremental/ -maxdepth 1 -mtime  +30  -type d  -exec rm -rf {} \;

增量备份恢复数据

1.停止数据库

systemctl stop  mysqld

2.删除之前数据目录和日志文件

rm -rf /var/lib/mysql

rm -rf /var/log/mysqld.log

选项用于准备全库备份和合并处最有一个备份外的所有增量备份。它强制xtrabackup忽略“rollback”阶段只做“redo”.

innobackupex --apply-log --redo-only /backup/2021-07-06_10-52-28/ --redo-only

3.第一次增量备份刷入全备中：

innobackupex --apply-log --redo-only /backup/2021-07-06_10-52-28/ --incremental-dir=/backup_incremental/2021-07-06_10-55-46/

4.准备恢复

innobackupex    --apply-log  /data/backup/2021-04-08_21-22-03/

5.恢复数据

innobackupex --copy-back /data/backup/2021-04-08_21-22-03/

6.修改数据目录属主属组

chown -R mysql:mysql /var/lib/mysql

7.启动mysql

systemctl start  mysqld

查看数据是否恢复正常