适用范围

5.7+

方案概述

在生产环境中，我们都会对数据库进行备份，我们知道ORACLE 的RMAN 备份很灵活，有全备，增量，归档 等等备份方式！ 针对MYSQL来讲，也有一款自己的备份工具mysqlbackup ，它是MySQL官方推出的跨平台mysql数据库物理备份与还原的强大工具。它支持全量备份、增量备份、备份压缩、备份验证、备份加密、流式备份等特性，类似Oracle数据库的RMAN。相比于percona公司的xtrabackup，它的功能更强大，效率更高。
下面我们将演示mysqlbackup 增量备份功能！

实施步骤

1.MYSQL 增量备份

1.1 数据准备

CREATE TABLE `test` ( 
             `name` VARCHAR(50), 
             `purchased` DATE
           ) ENGINE=InnoDB DEFAULT CHARSET=utf8
           PARTITION BY RANGE( YEAR(purchased) ) (
           PARTITION p0 VALUES LESS THAN (1990),
           PARTITION p1 VALUES LESS THAN (1995),
           PARTITION p2 VALUES LESS THAN (2000),
           PARTITION p3 VALUES LESS THAN (2005),
           PARTITION p4 VALUES LESS THAN (2010),
           PARTITION p5 VALUES LESS THAN (2015)
           );


   INSERT INTO `test` VALUES
            ('desk organiser', '2003-10-15'),
            ('alarm clock', '1997-11-05'),
            ('chair', '2009-03-10');
            

1.2 全备

rm -rf /tmp/backups/temp/*
mkdir -p /tmp/backups/temp/
/u01/mysql8e/mysql/bin/mysqlbackup  --login-path=backup    --socket=/u01/mysql8e/data/run/mysql3333.sock 
 --backup-image=/tmp/backups/fullbackup_`date +%Y%m%d`.mbi  --backup-dir=/tmp/backups/temp  backup-to-image  

1.3第一次增量

# 插入数据
       INSERT INTO `test` VALUES
            ('bookcase', '1989-01-10'),
            ('exercise bike', '2014-05-09'),
            ('sofa', '1987-06-05');


#incremental 


rm -rf /tmp/backups/temp/*
 mkdir /tmp/backups/incre_backup
/u01/mysql8e/mysql/bin/mysqlbackup  --login-path=backup    --socket=/u01/mysql8e/data/run/mysql3333.sock    
 --incremental=optimistic --incremental-base=history:last_backup   --backup-dir=/tmp/backups/temp/   
--backup-image=/tmp/backups/incre_backup/incre_backup_`date +%Y%m%d`.mbi   backup-to-image

1.4第二次增量

# 插入数据
  INSERT INTO `test` VALUES
            ('espresso maker', '2011-11-22'),
            ('aquarium', '1992-08-04');
#incremental 2 
rm -rf /tmp/backups/temp/*
 mkdir /tmp/backups/incre_backup


/u01/mysql8e/mysql/bin/mysqlbackup  --login-path=backup    --socket=/u01/mysql8e/data/run/mysql3333.sock   
  --incremental=optimistic --incremental-base=history:last_backup   --backup-dir=/tmp/backups/temp/  
 --backup-image=/tmp/backups/incre_backup/incre_backup_`date +%Y%m%d`_2.mbi    backup-to-image

2.增量恢复

2.1 环境清理

cd   /u01/mysql8e/data
mv data  data_bak 
mv binlogs binlogs_bak


mkdir data
mkdir -p binlogs/innodb
chown -R mysql.mysql  /u01/mysql8e/data

2.2 全备恢复

rm -rf /tmp/backups/temp/*
/u01/mysql8e/mysql/bin/mysqlbackup --defaults-file=/u01/mysql8e/data/my3333.cnf   
--datadir=/u01/mysql8e/data/data  --backup-image=/tmp/backups/fullbackup_`date +%Y%m%d`.mbi  
--backup-dir=/tmp/backups/temp     copy-back-and-apply-log 

2.3 第一次增量恢复

#增量恢复1
rm -rf /tmp/backups/temp/*
/u01/mysql8e/mysql/bin/mysqlbackup --defaults-file=/u01/mysql8e/data/my3333.cnf   
 --datadir=/u01/mysql8e/data/data   --backup-image=/tmp/backups/incre_backup/incre_backup_`date +%Y%m%d`.mbi  
 --backup-dir=/tmp/backups/temp   --incremental   copy-back-and-apply-log

2.4 第二次增量恢复

rm -rf /tmp/backups/temp/*
 
/u01/mysql8e/mysql/bin/mysqlbackup --defaults-file=/u01/mysql8e/data/my3333.cnf 
  --datadir=/u01/mysql8e/data/data   --backup-image=/tmp/backups/incre_backup/incre_backup_`date +%Y%m%d`_2.mbi 
  --backup-dir=/tmp/backups/temp    --incremental   copy-back-and-apply-log

2.5 启动DB 检查数据

chmod -R  755 /u01/mysql8e/data
chown -R mysql.mysql  /u01/mysql8e/data
sh start_mysql.sh 


mysql> select * from test ;
+----------------+------------+
| name           | purchased  |
+----------------+------------+
| bookcase       | 1989-01-10 |
| sofa           | 1987-06-05 |
| aquarium       | 1992-08-04 |
| alarm clock    | 1997-11-05 |
| desk organiser | 2003-10-15 |
| chair          | 2009-03-10 |
| exercise bike  | 2014-05-09 |
| espresso maker | 2011-11-22 |
+----------------+------------+
8 rows in set (0.00 sec)

参考文档