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

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/

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

[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