mysql innodb 增量备份_MySQL在线数据备份之物理备份（Xtrabackup增量备份）

Xtrabackup工具增量：

xtrabackup有两个主要的工具：innobackupex和xtrabackup，xtrabackup只能备份InnoDB和XtraDB数据表，innobackupex封装了xtrabackup，可以备份MyISAM数据表。

MySQL数据库本身提供的工具并不支持真正的增量备份，二进制日志恢复是point-in-time(时间点)的恢复而不是增量备份。Xtrabackup工具支持对InnoDB存储引擎的增量备份，工作原理如下：

首先完成一个完全备份，并记录下此时检查点的LSN(LogSequence Number)。

在进程增量备份时，比较表空间中每个页的LSN是否大于上次备份时的LSN，如果是，则备份该页，同时记录当前检查点的LSN。

配置数据备份环境：

mysql -uroot -predhat -e "create database olda;use olda;CREATE TABLE student(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学生学号',name VARCHAR(50) NOT NULL COMMENT '学生姓名',age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '学生年龄',gender ENUM('男','女') NOT NULL DEFAULT '男' COMMENT '学生性别',birthday DATETIME NOT NULL DEFAULT NOW() COMMENT '学生入学时间');"

mysql -uroot -predhat -e "use olda;insert into olda.student(name,age,gender) values('olda_1','20','男');"

检查数据环境：

mysql -uroot -predhat -e "use olda;select * from student;"

mysql: [Warning] Using a password on the command line interface can be insecure.

+----+--------+-----+--------+---------------------+

| id | name | age | gender | birthday |

+----+--------+-----+--------+---------------------+

| 1 | olda_1 | 20 | 男 | 2019-03-13 16:22:00 |

+----+--------+-----+--------+---------------------+

指定目录全备full：

#加上--no-timestamp参数，数据文件备份到full这个目录下

innobackupex --defaults-file=/usr/local/mysql/conf/my.cnf --user=root --password=redhat --no-timestamp /backup/full

模拟数据变化：

#第一个模拟数据变化

mysql -uroot -predhat -e "use olda;insert into olda.student(name,age,gender) values('olda_2','21','女');"

检查数据环境：

mysql -uroot -predhat -e "use olda;select * from student;"

mysql: [Warning] Using a password on the command line interface can be insecure.

+----+--------+-----+--------+---------------------+

| id | name | age | gender | birthday |

+----+--------+-----+--------+---------------------+

| 1 | olda_1 | 20 | 男 | 2019-03-13 16:22:00 |

| 2 | olda_2 | 21 | 女 | 2019-03-13 16:33:45 |

+----+--------+-----+--------+---------------------+

基于full全备的第一个增量备份：

#加上--incremental打开增量开关，加上--incremental-basedir指定基于full目录的全备数据做增量备份，并存放在inc01目录。

innobackupex --defaults-file=/usr/local/mysql/conf/my.cnf --user=root --password=redhat --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc01

模拟数据变化：

#第二个模拟数据变化

mysql -uroot -predhat -e "use olda;insert into olda.student(name,age,gender) values('olda_3','22','男');"

检查数据环境：

mysql -uroot -predhat -e "use olda;select * from student;"

mysql: [Warning] Using a password on the command line interface can be insecure.

+----+--------+-----+--------+---------------------+

| id | name | age | gender | birthday |

+----+--------+-----+--------+---------------------+

| 1 | olda_1 | 20 | 男 | 2019-03-13 16:22:00 |

| 3 | olda_2 | 21 | 女 | 2019-03-13 16:33:45 |

| 4 | olda_3 | 22 | 男 | 2019-03-13 16:34:59 |

+----+--------+-----+--------+---------------------+

基于inc01增备的第二个增量备份：

#加上--incremental打开增量开关，加上--incremental-basedir指定基于full目录的全备数据做增量备份，并存放在inc01目录。

innobackupex --defaults-file=/usr/local/mysql/conf/my.cnf --user=root --password=redhat --no-timestamp --incremental --incremental-basedir=/backup/inc01 /backup/inc02

破坏数据：

rm -rf /usr/local/mysql/mydata/*

查看数据库文件：

mysql -uroot -predhat -e "show databases;"

mysql: [Warning] Using a password on the command line interface can be insecure.

+--------------------+

| Database |

+--------------------+

| information_schema |

+--------------------+

恢复数据思路：

全备(full) + 增量(inc01) + 增量(inc02)= 完整数据(缺失数据可以用binlog恢复)

如果是增量恢复数据，在恢复最后一次的增量备份才要应用redo与undo，不是最后一份增量的数据都只应用redo。

全备数据准备：

#全备数据合并(本次全备恢复只应用redo)

innobackupex --defaults-file=/usr/local/mysql/conf/my.cnf --apply-log --redo-only /backup/full/

合并inc01到full备份：

#加入--incremental-dir参数，指定inc1增量备份合并到full全备中(本次恢复准备只应用redo)(合并数据)

innobackupex --defaults-file=/usr/local/mysql/conf/my.cnf --apply-log --redo-only --incremental-dir=/backup/inc01 /backup/full/

合并inc02到inc01增备：

#加入--incremental-dir参数，指定inc02增量备份合并到inc01增量备份中，(本次恢复准备应用redo与undo，因为这是最后一次增量的数据)(合并数据)

innobackupex --defaults-file=/usr/local/mysql/conf/my.cnf --apply-log --incremental-dir=/backup/inc02 /backup/full/

所有全备与增备数据恢复准备：

#对合并后的数据总体进行数据准备

innobackupex --defaults-file=/usr/local/mysql/conf/my.cnf --apply-log /backup/full/

真正恢复数据：

#真正恢复数据，(拷贝ibd等数据文件到MySQL的data目录)

innobackupex --defaults-file=/usr/local/mysql/conf/my.cnf --copy-back /backup/full/

重启数据库：

chown -R mysql.mysql /usr/local/mysql/mydata/*

systemctl restart mysqld

检查插入的三条数据是否恢复：

mysql -uroot -predhat -e "use olda;select * from student;"

mysql: [Warning] Using a password on the command line interface can be insecure.

+----+--------+-----+--------+---------------------+

| id | name | age | gender | birthday |

+----+--------+-----+--------+---------------------+

| 1 | olda_1 | 20 | 男 | 2019-03-13 16:22:00 |

| 3 | olda_2 | 21 | 女 | 2019-03-13 16:33:45 |

| 4 | olda_3 | 22 | 男 | 2019-03-13 16:34:59 |

+----+--------+-----+--------+---------------------+

MySQL-8.0版本-Xtrabackup：

恢复过程中首先要执行prepare，将所有的数据文件都准备到同一个时间点，因为在备份过程中所有的数据文件备份都是在不同的时间点，如果直接启动可能会导致冲突。

因为在物理备份时也是需要时间进行拷贝数据文件的，执行prepare是把所有数据置为同一时间的数据。

当前模拟数据增量备份恢复逻辑为：增量inc1合并数据到全量full，然后增量inc2再合并数据到全量full，然后把全量full文件恢复(拷贝)到MySQL data目录。

Xtrabackup全量备份：

当前 /mysql_backup目录下就是物理备份文件

[root@db01 mysql_backup]# mysql -uroot -p -e "select * from course.temp;"

Enter password:

+------+------+

| id | name |

+------+------+

| 1 | a |

+------+------+

[root@db01 ~]# mkdir -p /backup/{full,inc1,inc2}

[root@db01 ~]# xtrabackup --backup --target-dir=/backup/full --user=root --password=123456 --host=127.0.0.1 --port=3306

Xtrabackup数据变化与增量备份：

[root@db01 mysql_backup]# mysql -uroot -p -e "insert into course.temp values(2,'b');"

Enter password:

[root@db01 mysql_backup]# mysql -uroot -p -e "select * from course.temp;"

Enter password:

+------+------+

| id | name |

+------+------+

| 1 | a |

| 2 | b |

+------+------+

''

# 基于/backup/full全量备份又做了数据变化的增量备份 inc1

[root@db01 ~]# xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full --user=root --password=123456 --host=127.0.0.1 --port=3306

Xtrabackup数据变化与增量备份：

[root@db01 mysql_backup]# mysql -uroot -p -e "insert into course.temp values(3,'c');"

Enter password:

[root@db01 mysql_backup]# mysql -uroot -p -e "select * from course.temp;"

Enter password:

+------+------+

| id | name |

+------+------+

| 1 | a |

| 2 | b |

| 3 | c |

+------+------+

# 基于/backup/inc1增量备份又做了数据变化的增量备份 inc2

[root@db01 ~]# xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1 --user=root --password=123456 --host=127.0.0.1 --port=3306

Xtrabackup数据恢复之全量数据合并：

全备数据合并(本次全备恢复只应用redo)

[root@db01 ~]# systemctl stop mysqld

[root@db01 ~]# rm -rf /usr/local/mysql-8.0.16/data/*

[root@db01 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/full --datadir=/usr/local/mysql-8.0.16/data

Xtrabackup数据恢复之增量数据合并 一：

指定inc1增量备份合并到full全备中(本次恢复准备只应用redo)

[root@db01 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/full --incremental-dir=/backup/inc1 --datadir=/usr/local/mysql-8.0.16/data

Xtrabackup数据恢复之增量数据合并 二：

指定inc2增量备份合并到full全备中(本次恢复准备只应用redo)

[root@db01 ~]# xtrabackup --prepare --target-dir=/backup/full --incremental-dir=/backup/inc2 --datadir=/usr/local/mysql-8.0.16/data

Xtrabackup数据恢复之full数据恢复：

数据已经恢复成功

[root@db01 ~]# xtrabackup --copy-back --target-dir=/backup/full --datadir=/usr/local/mysql-8.0.16/data

[root@db01 ~]# chown -R mysql.mysql /usr/local/mysql-8.0.16/

[root@db01 ~]# systemctl start mysqld

[root@db01 mysql_backup]# mysql -uroot -p -e "select * from course.temp;"

Enter password:

+------+------+

| id | name |

+------+------+

| 1 | a |

| 2 | b |

| 3 | c |

+------+------+