使用备份工具xtrabackup进行增量备份详细讲解

增量备份

第一次修改数据

mysql> insert into tb_user values (4,'sxx',0);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_user;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | Tom     | 1    |
|  2 | Trigger | 0    |
|  3 | Dawn    | 1    |
|  4 | sxx     | 0    |
+----+---------+------+
5 rows in set (0.00 sec)

备份1

[root@service ~]# xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc1/ --incremental-basedir=/data/backup/base -uroot -pWyxbuke00. -H localhost -P 3306
[root@service ~]# ls /data/backup/
base  inc1
[root@service ~]# ls /data/backup/inc1
backup-my.cnf  db01            ibdata1.meta     mysql.ibd.meta      undo_001.delta  undo_002.meta           xtrabackup_info
binlog.000013  ib_buffer_pool  mysql            performance_schema  undo_001.meta   xtrabackup_binlog_info  xtrabackup_logfile
binlog.index   ibdata1.delta   mysql.ibd.delta  sys                 undo_002.delta  xtrabackup_checkpoints  xtrabackup_tablespaces

 第二次修改数据

mysql> insert into tb_user values (5,'wyx',1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_user;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | Tom     | 1    |
|  2 | Trigger | 0    |
|  3 | Dawn    | 1    |
|  4 | sxx     | 0    |
|  5 | wyx     | 1    |
+----+---------+------+
5 rows in set (0.00 sec)

 备份2

[root@service ~]# xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc2/ --incremental-basedir=/data/backup/inc1 -uroot -pWyxbuke00. -H localhost -P 3306
修改路径为inc1为上一次的修改的数据
[root@service ~]# ls /data/backup/
base  inc1  inc2

 第三次修改的数据

mysql> insert into tb_user values (6,'asd',1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_user;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | Tom     | 1    |
|  2 | Trigger | 0    |
|  3 | Dawn    | 1    |
|  4 | sxx     | 0    |
|  5 | wyx     | 1    |
|  6 | asd     | 1    |
+----+---------+------+
6 rows in set (0.00 sec)

 备份3

[root@service ~]#  xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc3/ --incremental-basedir=/data/backup/inc2 -uroot -pWyxbuke00. -H localhost -P 3306

 

恢复增量备份

• 注意最后一次把增量备份恢复到全部不要加 --apply-log-only

先准备完整备份

[root@service ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base

 将第一次修改的数据进行完整备份

[root@service ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base --incremental-dir=/data/backup/inc1

 将第二次修改的数据进行完整备份

[root@service ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base --incremental-dir=/data/backup/inc2

 将第三次修改的数据进行完整备份

[root@service ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare  --target-dir=/data/backup/base --incremental-dir=/data/backup/inc3

 

恢复到数据目录

全库级恢复要先停止数据库并清空数据目录

[root@service ~]# systemctl stop mysqld
[root@service ~]# rm -rf /var/lib/mysql/*

 恢复到完整备份

[root@service ~]# xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/base
[root@service ~]# ls /var/lib/mysql
 binlog.000015   db01             ibdata1  '#innodb_redo'   mysql.ibd            sys        undo_002
 binlog.index    ib_buffer_pool   ibtmp1    mysql           performance_schema   undo_001   xtrabackup_info

 给/var/lib/mysql增加权限，重启数据库登陆

chown -R mysql.mysql /var/lib/mysql
[root@service ~]# systemctl start mysqld
[root@service ~]# mysql -u root -p 
Enter password: