MySQL备份与恢复之percona-xtrabackup实现增量备份及恢复

女爷i 2022-09-18 14:47 438阅读 0赞

## 一 文章回顾 ##

在上一篇文章，我们讲到[percona-xtrabackup软件的使用][percona-xtrabackup]，这一篇文章我们讲解percona-xtrabackup实现增量备份及恢复。

## **二 增量备份示意图** ##

![SouthEast][]

## 三 percona-xtrabackup实现增量备份及恢复原理 ##

**      **

首先，使用percona-xtrabackup工具对数据库进行全备，然后再每次数据库的数据更新后对数据进行增量备份，每次增量备份均在上一次备份的基础上。恢复时依次把每次增量备份的数据恢复到全备中，最后使用合并的数据进行数据恢复。

## **四 percona-xtrabackup实现增量备份及恢复** ##

第一步，全备

>     [root@serv01 databackup]# innobackupex --user=root --password=123456 /databackup/

第二步，查看数据

>     mysql> use larrydb;
>     Database changed
>     mysql> select * from class;
>     +------+-------+
>     | cid  | cname |
>     +------+-------+
>     |    1 | linux |
>     |    2 | dab   |
>     |    3 | Devel |
>     +------+-------+
>     3 rows in set (0.00 sec)
>     
>     mysql> select * from stu;
>     +------+----------+------+
>     | sid  | sname    | cid  |
>     +------+----------+------+
>     |    1 | larry007 |    1 |
>     +------+----------+------+
>     1 row in set (0.00 sec)

第三步，更新数据

>     mysql> insert into stu values(2,'larry02',1);
>     Query OK, 1 row affected (0.00 sec)
>     
>     mysql> select * from stu;
>     +------+----------+------+
>     | sid  | sname    | cid  |
>     +------+----------+------+
>     |    1 | larry007 |    1 |
>     |    2 | larry02  |    1 |
>     +------+----------+------+
>     2 rows in set (0.00 sec)

第四步，增量备份，进行了全备和第一次增量备份，所以有两个备份文件夹。我们每次增量备份都是针对上一次备份。

>     #--incremental：增量备份的文件夹
>     #--incremental-dir：针对哪个做增量备份
>     [root@serv01 databackup]# innobackupex --user=root --password=123456 --incremental /databackup/ --incremental-dir /databackup/2013-09-10_22-12-50/
>     
>     InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
>     and Percona Inc 2009-2012.  All Rights Reserved.
>     ……
>     innobackupex: Backup created in directory '/databackup/2013-09-10_22-15-45'
>     innobackupex: MySQL binlog position: filename 'mysql-bin.000004', position 353
>     130910 22:16:04  innobackupex: completed OK!
>     
>     [root@serv01 databackup]# ll
>     total 8
>     drwxr-xr-x. 9 root root 4096 Sep 10 22:13 2013-09-10_22-12-50
>     drwxr-xr-x. 9 root root 4096 Sep 10 22:16 2013-09-10_22-15-45

第五步，再次插入数据

>     mysql> insert into stu values(3,'larry03',1);
>     Query OK, 1 row affected (0.00 sec)
>     
>     mysql> select * from stu;
>     +------+----------+------+
>     | sid  | sname    | cid  |
>     +------+----------+------+
>     |    1 | larry007 |    1 |
>     |    2 | larry02  |    1 |
>     |    3 | larry03  |    1 |
>     +------+----------+------+
>     3 rows in set (0.00 sec)

第六步，再次增量备份

>     [root@serv01 databackup]# ll
>     total 8
>     drwxr-xr-x. 9 root root 4096 Sep 10 22:13 2013-09-10_22-12-50
>     drwxr-xr-x. 9 root root 4096 Sep 10 22:16 2013-09-10_22-15-45
>     [root@serv01 databackup]# innobackupex --user=root --password=123456 --incremental /databackup/ --incremental-dir /databackup/2013-09-10_22-15-45/

第七步，再次插入数据

>     mysql> insert into stu values(4,'larry04',1);
>     Query OK, 1 row affected (0.00 sec)
>     
>     mysql> select * from stu;
>     +------+----------+------+
>     | sid  | sname    | cid  |
>     +------+----------+------+
>     |    1 | larry007 |    1 |
>     |    2 | larry02  |    1 |
>     |    3 | larry03  |    1 |
>     |    4 | larry04  |    1 |
>     +------+----------+------+
>     4 rows in set (0.00 sec)

第八步，再次增量备份。一次全备，三次增量备份，所以有四个备份文件夹

>     [root@serv01 databackup]# innobackupex --user=root --password=123456 --incremental /databackup/ --incremental-dir /databackup/2013-09-10_22-19-21/
>     
>     
>     [root@serv01 databackup]# ll
>     total 16
>     drwxr-xr-x. 9 root root 4096 Sep 10 22:13 2013-09-10_22-12-50
>     drwxr-xr-x. 9 root root 4096 Sep 10 22:16 2013-09-10_22-15-45
>     drwxr-xr-x. 9 root root 4096 Sep 10 22:19 2013-09-10_22-19-21
>     drwxr-xr-x. 9 root root 4096 Sep 10 22:22 2013-09-10_22-21-42

第九步，模拟数据丢失

>     mysql> drop database larrydb;
>     Query OK, 2 rows affected (0.02 sec)

第十步，对全部的数据进行检查。可以看到增量备份和全备的文件占用磁盘大小有很大的差别，显然全备占用磁盘空间多，增量备份占用磁盘空间少

>     [root@serv01 databackup]# innobackupex --apply-log --redo-only /databackup/2013-09-10_22-12-50/
>     
>     InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
>     and Percona Inc 2009-2012.  All Rights Reserved.
>     ……
>     xtrabackup: starting shutdown with innodb_fast_shutdown = 1
>     130910 22:23:35  InnoDB: Starting shutdown...
>     130910 22:23:36  InnoDB: Shutdown completed; log sequence number 2098700
>     130910 22:23:36  innobackupex: completed OK!
>     
>     [root@serv01 databackup]# du -sh ./*
>     22M ./2013-09-10_22-12-50
>     1.5M  ./2013-09-10_22-15-45
>     1.5M  ./2013-09-10_22-19-21
>     1.5M  ./2013-09-10_22-21-42

第十一步，对第一次做的增量备份数据进行合并到全备份中去

>     [root@serv01 databackup]# innobackupex --apply-log --redo-only --incremental /databackup/2013-09-10_22-12-50/ --incremental-dir=/databackup/2013-09-10_22-15-45/
>     
>     InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
>     and Percona Inc 2009-2012.  All Rights Reserved.
>     ……
>     innobackupex: Copying '/databackup/2013-09-10_22-15-45/hello/db.opt' to '/databackup/2013-09-10_22-12-50/hello/db.opt'
>     130910 22:32:26  innobackupex: completed OK!

第十二步，对第二次做的增量备份数据进行合并到全备份中去

>     [root@serv01 databackup]# innobackupex --apply-log --redo-only --incremental /databackup/2013-09-10_22-12-50/ --incremental-dir=/databackup/2013-09-10_22-19-21/

第十三步，对第三次做的增量备份数据进行合并到全备份中去

>     [root@serv01 databackup]# innobackupex --apply-log --redo-only --incremental /databackup/2013-09-10_22-12-50/ --incremental-dir=/databackup/2013-09-10_22-21-42/

第十四步，恢复时需要停掉MySQL，所以我们停掉MySQL

>     [root@serv01 databackup]# /etc/init.d/mysqld stop
>      ERROR! MySQL server PID file could not be found!
>     [root@serv01 databackup]# pkill -9 mysql

第十五步，恢复数据。注意这里指定的文件夹是2013-09-10\_22-12-50

>     [root@serv01 databackup]# innobackupex --copy-back /databackup/2013-09-10_22-12-50/
>     
>     InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
>     and Percona Inc 2009-2012.  All Rights Reserved.
>     
>     This software is published under
>     the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
>     
>     IMPORTANT: Please check that the copy-back run completes successfully.
>                At the end of a successful copy-back run innobackupex
>                prints "completed OK!".
>     
>     Original data directory is not empty! at /usr/bin/innobackupex line 571.
>     
>     #报以上错需要删除数据目录下的东西
>     [root@serv01 data]# pwd
>     /usr/local/mysql/data
>     [root@serv01 data]# ls
>     game     ib_logfile0  mysql             mysql-bin.000003  performance_schema   test
>     hello    ib_logfile1  mysql-bin.000001  mysql-bin.000004  serv01.host.com.err  xtrabackup_binlog_pos_innodb
>     ibdata1  mnt          mysql-bin.000002  mysql-bin.index   serv01.host.com.pid
>     
>     [root@serv01 data]# rm -rf  *
>     
>     #再次恢复数据，并更改数据库数据目录的拥有者和所属组
>     [root@serv01 databackup]# innobackupex --copy-back /databackup/2013-09-10_22-12-50/
>     
>     [root@serv01 data]# ll
>     total 18464
>     drwxr-xr-x. 2 root root     4096 Sep 10 22:37 game
>     drwxr-xr-x. 2 root root     4096 Sep 10 22:37 hello
>     -rw-r-----. 1 root root 18874368 Sep 10 22:33 ibdata1
>     drwxr-xr-x. 2 root root     4096 Sep 10 22:37 larrydb
>     drwxr-xr-x. 2 root root     4096 Sep 10 22:37 mnt
>     drwxr-xr-x. 2 root root     4096 Sep 10 22:37 mysql
>     drwxr-xr-x. 2 root root     4096 Sep 10 22:37 performance_schema
>     drwxr-xr-x. 2 root root     4096 Sep 10 22:37 test
>     -rw-r--r--. 1 root root       24 Sep 10 22:37 xtrabackup_binlog_pos_innodb
>       
>     [root@serv01 data]# chown mysql.mysql /usr/local/mysql/data/ -R

第十六步，启动服务

>     [root@serv01 data]# /etc/init.d/mysqld start
>     Starting MySQL.. SUCCESS!

第十七步，登录数据库，然后查看数据

>     [root@serv01 data]# mysql -uroot -p123456
>     mysql> show databases;
>     +--------------------+
>     | Database           |
>     +--------------------+
>     | information_schema |
>     | game               |
>     | hello              |
>     | larrydb            |
>     | mnt                |
>     | mysql              |
>     | performance_schema |
>     | test               |
>     +--------------------+
>     8 rows in set (0.00 sec)
>     
>     mysql> select * from larrydb.class;
>     +------+-------+
>     | cid  | cname |
>     +------+-------+
>     |    1 | linux |
>     |    2 | dab   |
>     |    3 | Devel |
>     +------+-------+
>     3 rows in set (0.00 sec)
>     
>     mysql> select * from larrydb.stu;
>     +------+----------+------+
>     | sid  | sname    | cid  |
>     +------+----------+------+
>     |    1 | larry007 |    1 |
>     |    2 | larry02  |    1 |
>     |    3 | larry03  |    1 |
>     |    4 | larry04  |    1 |
>     +------+----------+------+
>     4 rows in set (0.00 sec)

>     我的邮箱：wgbno27@163.com   新浪微博：@jutdb         
>       微信公众平台：JustOracle（微信号：justoracle）
>       数据库技术交流群：336882565（加群时验证 From CSDN XXX）
>       All is well
>       2013年12月1日
>       By Larry Wen

<table style="border-bottom:#48c5db 5px double; border-left:#48c5db 5px double; padding-bottom:5px; background-color:black; padding-left:5px; border-spacing:0; width:100%; padding-right:30px; height:90px; border-top:#48c5db 5px double; border-right:#48c5db 5px double; padding-top:5px"> 
 <tbody> 
  <tr> 
   <td><img alt="katoon" src="https://img-my.csdn.net/uploads/201301/06/1357456262_5409.jpg" style="height:80px"></td> 
   <td style="text-align:right"><a title="新浪微博求粉 ^_^" href="http://weibo.com/wentasy" rel="nofollow"><img title="新浪微博求粉 ^_^" alt="Sina" src="https://img-my.csdn.net/uploads/201210/26/1351182273_5476.png.thumb.jpg" style="height:40px"></a>&nbsp;<a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow"><img title="我的CSDN博客——HelloWorld" alt="CSDN" src="https://img-my.csdn.net/uploads/201210/26/1351182268_8254.png.thumb.jpg" style="height:40px"></a></td> 
  </tr> 
  <tr> 
   <td style="text-align:right; font-family:microsoft yahei; color:white; font-size:12px"> <a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow">@Wentasy</a> 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客]</td> 
  </tr> 
 </tbody> 
</table>

[percona-xtrabackup]: http://blog.csdn.net/justdb/article/details/17054579
[SouthEast]: /images/20220828/74f2b22dcc464362be11f9a8c0aa4d88.png