欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2024-04-06 09:34:07
...
bitsCN.com

一 文章回顾

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

二 增量备份示意图

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

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

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

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

第一步,全备

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

第二步,查看数据

mysql> use larrydb;Database changedmysql> 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 Oyand 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 353130910 22:16:04  innobackupex: completed OK![root@serv01 databackup]# lltotal 8drwxr-xr-x. 9 root root 4096 Sep 10 22:13 2013-09-10_22-12-50drwxr-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]# lltotal 8drwxr-xr-x. 9 root root 4096 Sep 10 22:13 2013-09-10_22-12-50drwxr-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]# lltotal 16drwxr-xr-x. 9 root root 4096 Sep 10 22:13 2013-09-10_22-12-50drwxr-xr-x. 9 root root 4096 Sep 10 22:16 2013-09-10_22-15-45drwxr-xr-x. 9 root root 4096 Sep 10 22:19 2013-09-10_22-19-21drwxr-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 Oyand Percona Inc 2009-2012.  All Rights Reserved.……xtrabackup: starting shutdown with innodb_fast_shutdown = 1130910 22:23:35  InnoDB: Starting shutdown...130910 22:23:36  InnoDB: Shutdown completed; log sequence number 2098700130910 22:23:36  innobackupex: completed OK![root@serv01 databackup]# du -sh ./*22M ./2013-09-10_22-12-501.5M  ./2013-09-10_22-15-451.5M  ./2013-09-10_22-19-211.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 Oyand 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 Oyand Percona Inc 2009-2012.  All Rights Reserved.This software is published underthe 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]# lsgame     ib_logfile0  mysql             mysql-bin.000003  performance_schema   testhello    ib_logfile1  mysql-bin.000001  mysql-bin.000004  serv01.host.com.err  xtrabackup_binlog_pos_innodbibdata1  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]# lltotal 18464drwxr-xr-x. 2 root root     4096 Sep 10 22:37 gamedrwxr-xr-x. 2 root root     4096 Sep 10 22:37 hello-rw-r-----. 1 root root 18874368 Sep 10 22:33 ibdata1drwxr-xr-x. 2 root root     4096 Sep 10 22:37 larrydbdrwxr-xr-x. 2 root root     4096 Sep 10 22:37 mntdrwxr-xr-x. 2 root root     4096 Sep 10 22:37 mysqldrwxr-xr-x. 2 root root     4096 Sep 10 22:37 performance_schemadrwxr-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 startStarting MySQL.. SUCCESS! 

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

[root@serv01 data]# mysql -uroot -p123456mysql> 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

bitsCN.com