mysql welcomeback_MySql在生产环境中是用mysqldump还是xtrabackup备份和恢复数据

ibdata1:10M:autoextend

xtrabackup: innodb_log_group_home_dir = ./

xtrabackup: innodb_log_files_in_group = 2

xtrabackup: innodb_log_file_size = 5242880

130307 21:01:47 InnoDB: Using Linux native AIO

xtrabackup: Stream mode.

>> log scanned up to (59605543)

130307 21:01:49 innobackupex: Continuing after ibbackup has suspended

innobackupex: Starting to backup InnoDB tables and indexes

innobackupex: from original InnoDB data directory '/var/lib/mysql'

innobackupex: Backing up as tar stream 'ibdata1'

130307 21:01:52 innobackupex: Starting mysql with options: --defaults-file='/etc/mysql/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --

130307 21:01:52 innobackupex: Connected to database with mysql child process (pid=12494)

>> log scanned up to (59605543)

130307 21:01:54 innobackupex: Starting to lock all tables...

>> log scanned up to (59605543)

>> log scanned up to (59605543)

130307 21:02:04 innobackupex: All tables locked and flushed to disk

130307 21:02:04 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,

innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in

innobackupex: subdirectories of '/var/lib/mysql'

innobackupex: Backing up file '/var/lib/mysql/test/test.frm'

innobackupex: Backing up file '/var/lib/mysql/test/db.opt'

130307 21:02:04 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files

innobackupex: Resuming ibbackup

xtrabackup: The latest check point (for incremental): '59605543'

>> log scanned up to (59605543)

xtrabackup: Transaction log of lsn (59605543) to (59605543) was copied.

130307 21:02:06 innobackupex: All tables unlocked

130307 21:02:06 innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/tmp/data'

innobackupex: MySQL binlog position: filename 'mysql-bin.000022', position 107

innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream.

130307 21:02:06 innobackupex: completed OK!

可以看到备份完成了

(3)恢复数据库

先关闭mysql服务，然后再删除test数据库文件

root@client2:/tmp/data# service mysql stop

mysql stop/waiting

root@client2:/tmp/data# cd /var/lib/mysql/

root@client2:/var/lib/mysql# ll

total 77860

drwx------ 8 mysql mysql 4096 Mar 7 20:59 ./

drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../

-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag

drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/

-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1

-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0

-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1

drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/

drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/

-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info

drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/

drwx------ 2 mysql mysql 4096 Mar 7 21:00 test/

drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/

root@client2:/var/lib/mysql# rm -rf test

root@client2:/var/lib/mysql# ll

total 77856

drwx------ 7 mysql mysql 4096 Mar 7 21:03 ./

drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../

-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag

drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/

-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1

-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0

-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1

drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/

drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/

-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info

drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/

drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/

开始恢复数据库

先把之前/tmp/data里的数据库压缩版给解压

root@client2:cd /tmp/data

root@client2:/tmp/data# tar -izxvf test-201303072101.tar.gz -C /tmp/restore/

backup-my.cnf

ibdata1

xtrabackup_binlog_info

test/test.frm

test/db.opt

xtrabackup_logfile

xtrabackup_checkpoints

xtrabackup_binary

注意：这里tar解包必须使用-i参数，否则解压出来的文件只有一个backup-my.cnf

查看一下restore的内容

root@client2:/tmp/data# cd /tmp/restore/

root@client2:/tmp/restore# ll

total 67616

drwxr-xr-x 3 root root 4096 Mar 7 21:03 ./

drwxrwxrwt 14 root root 4096 Mar 7 21:03 ../

-rw-r--r-- 1 root root 260 Mar 7 21:01 backup-my.cnf

-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:01 ibdata1

drwxr-xr-x 2 root root 4096 Mar 7 21:03 test/

-rw-r--r-- 1 root root13 Mar 7 21:02 xtrabackup_binary

-rw-r--r-- 1 root root23 Mar 7 21:02 xtrabackup_binlog_info

-rw-r--r-- 1 root root79 Mar 7 21:02 xtrabackup_checkpoints

-rw-r--r-- 1 root root 2560 Mar 7 21:02 xtrabackup_logfile

然后将备份文件中的日志应用到备份文件中的数据文件上

root@client2:/tmp/restore# innobackupex --user=root --password=123456 --apply-log /tmp/restore/

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 apply-log run completes successfully.

At the end of a successful apply-log run innobackupex

prints "completed OK!".

130307 21:04:18 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/tmp/restore/backup-my.cnf" --prepare --target-dir=/tmp/restore

xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)

xtrabackup: cd to /tmp/restore

xtrabackup: This target seems to be not prepared yet.

xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(59605543)

xtrabackup: Temporary instance for recovery is set as followings.

xtrabackup: innodb_data_home_dir = ./

xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend

xtrabackup: innodb_log_group_home_dir = ./

xtrabackup: innodb_log_files_in_group = 1

xtrabackup: innodb_log_file_size = 2097152

130307 21:04:19 InnoDB: Using Linux native AIO

xtrabackup: Starting InnoDB instance for recovery.

xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)

130307 21:04:19 InnoDB: The InnoDB memory heap is disabled

130307 21:04:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins

130307 21:04:19 InnoDB: Compressed tables use zlib 1.2.3

130307 21:04:19 InnoDB: Using Linux native AIO

130307 21:04:19 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead

130307 21:04:19 InnoDB: Initializing buffer pool, size = 100.0M

130307 21:04:19 InnoDB: Completed initialization of buffer pool

130307 21:04:19 InnoDB: highest supported file format is Barracuda.

InnoDB: The log sequence number in ibdata files does not match

InnoDB: the log sequence number in the ib_logfiles!

130307 21:04:19 InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000022

130307 21:04:20 InnoDB: Waiting for the background threads to start

130307 21:04:21 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59605543

[notice (again)]

If you use binary log and don't use any hack of group commit,

the binary log position seems to be:

InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000022

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

130307 21:04:21 InnoDB: Starting shutdown...

130307 21:04:25 InnoDB: Shutdown completed; log sequence number 59606758

130307 21:04:25 innobackupex: Restarting xtrabackup with command: xtrabackup_55 --defaults-file="/tmp/restore/backup-my.cnf" --prepare --target-dir=/tmp/restore

for creating ib_logfile*

xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)

xtrabackup: cd to /tmp/restore

xtrabackup: This target seems to be already prepared.

xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.

xtrabackup: Temporary instance for recovery is set as followings.

xtrabackup: innodb_data_home_dir = ./

xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend

xtrabackup: innodb_log_group_home_dir = ./

xtrabackup: innodb_log_files_in_group = 2

xtrabackup: innodb_log_file_size = 5242880

130307 21:04:25 InnoDB: Using Linux native AIO

xtrabackup: Starting InnoDB instance for recovery.

xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)

130307 21:04:25 InnoDB: The InnoDB memory heap is disabled

130307 21:04:25 InnoDB: Mutexes and rw_locks use GCC atomic builtins

130307 21:04:25 InnoDB: Compressed tables use zlib 1.2.3

130307 21:04:25 InnoDB: Using Linux native AIO

130307 21:04:25 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead

130307 21:04:25 InnoDB: Initializing buffer pool, size = 100.0M

130307 21:04:25 InnoDB: Completed initialization of buffer pool

130307 21:04:25 InnoDB: Log file ./ib_logfile0 did not exist: new to be created

InnoDB: Setting log file ./ib_logfile0 size to 5 MB

InnoDB: Database physically writes the file full: wait...

130307 21:04:25 InnoDB: Log file ./ib_logfile1 did not exist: new to be created

InnoDB: Setting log file ./ib_logfile1 size to 5 MB

InnoDB: Database physically writes the file full: wait...

130307 21:04:25 InnoDB: highest supported file format is Barracuda.

InnoDB: The log sequence number in ibdata files does not match

InnoDB: the log sequence number in the ib_logfiles!

130307 21:04:25 InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000022

130307 21:04:26 InnoDB: Waiting for the background threads to start

130307 21:04:27 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59607052

[notice (again)]

If you use binary log and don't use any hack of group commit,

the binary log position seems to be:

InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000022

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

130307 21:04:27 InnoDB: Starting shutdown...

130307 21:04:31 InnoDB: Shutdown completed; log sequence number 59607052

130307 21:04:31 innobackupex: completed OK!

这里的--apply-log指明是将日志应用到数据文件上，完成之后将备份文件中的数据恢复到数据库中：

然后再查看一下当前目录内容

root@client2:/tmp/restore# ll

total 79904

drwxr-xr-x 3 root root 4096 Mar 7 21:04 ./

drwxrwxrwt 14 root root 4096 Mar 7 21:04 ../

-rw-r--r-- 1 root root 260 Mar 7 21:01 backup-my.cnf

-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:04 ibdata1

-rw-r--r-- 1 root root 5242880 Mar 7 21:04 ib_logfile0

-rw-r--r-- 1 root root 5242880 Mar 7 21:04 ib_logfile1

drwxr-xr-x 2 root root 4096 Mar 7 21:03 test/

-rw-r--r-- 1 root root13 Mar 7 21:02 xtrabackup_binary

-rw-r--r-- 1 root root23 Mar 7 21:02 xtrabackup_binlog_info

-rw-r--r-- 1 root root36 Mar 7 21:04 xtrabackup_binlog_pos_innodb

-rw-r--r-- 1 root root79 Mar 7 21:04 xtrabackup_checkpoints

-rw-r--r-- 1 root root 2097152 Mar 7 21:04 xtrabackup_logfile

然后把test目录复制到/var/lib/mysql目录

root@client2:/tmp/restore# rsync -avz test ib* /var/lib/mysql/

sending incremental file list

test/

test/db.opt

test/test.frm

sent 381 bytes received 54 bytes 870.00 bytes/sec

total size is 8621 speedup is 19.82

root@client2:/tmp/restore# cd /var/lib/mysql/

root@client2:/var/lib/mysql# ll

total 77860

drwx------ 8 mysql mysql 4096 Mar 7 21:06 ./

drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../

-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag

drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/

-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1

-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0

-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1

drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/

drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/

-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info

drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/

drwxr-xr-x 2 root root 4096 Mar 7 21:03 test/

drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/

然后再修改test的用户与组为mysql

root@client2:/var/lib/mysql# chown -R mysql:mysql test

root@client2:/var/lib/mysql# ll

total 77860

drwx------ 8 mysql mysql 4096 Mar 7 21:06 ./

drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../

-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag

drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/

-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1

-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0

-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1

drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/

drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/

-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info

drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/

drwxr-xr-x 2 mysql mysql 4096 Mar 7 21:03 test/

drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/

然后启动mysql，并查看test数据库的表里内容

root@client2:/var/lib/mysql# service mysql start

mysql start/running, process 12730

root@client2:/var/lib/mysql# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 36

Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from test;

+------+

| id |

+------+

|1 |

|2 |

|3 |

|4 |

|5 |

+------+

5 rows in set (0.01 sec)

可以看到数据库已经恢复完成

可能大家有个疑问，为什么我这里不像很多网上的文章里是在apply-log后，使用copy-back如果使用/usr/bin/innobackupex --copy-back命令后，会报Original data directory is not empty! at /usr/local/xtrabackup/bin/innobackupex line 538.恢复的目录必须为空。经查官网，这是xtrabackup的一个BUG。

innobackupex–copy-back was run. With this bug fix, innobackupex–copy-back operation if the destination is not empty, avoiding potential data loss or a strang combination of a restored backup and previous data. Bug Fixed: #737569 (Valentine Gostev) will now error out of the did not check that MySQL datadir was empty before

所以在apply-log后直接复制数据目录到数据库的位置上吧。

三、对数据库的增量备份与恢复

为了进行增量备份，先对数据库添加一些数据

mysql> insert into test values(11);

Query OK, 1 row affected (0.10 sec)

mysql> insert into test values(12);

Query OK, 1 row affected (0.05 sec)

mysql> insert into test values(13);

Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(14);

Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(15);

Query OK, 1 row affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql> select * from test;

+------+

| id |

+------+

|1 |

|2 |

|3 |

|4 |

|5 |

| 11 |

| 12 |

| 13 |

| 14 |

| 15 |

+------+

10 rows in set (0.00 sec)

然后进行增量的备份

root@client2:/var/lib/mysql# innobackupex --user=root --password=123456 --database=test --incremental --incremental-basedir=/tmp/restore/ /tmp/data

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.

130307 21:13:38 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered --

130307 21:13:38 innobackupex: Connected to database with mysql child process (pid=12864)

130307 21:13:44 innobackupex: Connection to database server closed

IMPORTANT: Please check that the backup run completes successfully.

At the end of a successful backup run innobackupex

prints "completed OK!".

innobackupex: Using mysql Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (x86_64) using readline 6.2

innobackupex: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

innobackupex: Created backup directory /tmp/data/2013-03-07_21-13-44

130307 21:13:44 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered --

130307 21:13:44 innobackupex: Connected to database with mysql child process (pid=12891)

130307 21:13:46 innobackupex: Connection to database server closed

130307 21:13:46 innobackupex: Starting ibbackup with command: xtrabackup_55 --backup --suspend-at-end --target-dir=/tmp/data/2013-03-07_21-13-44 --incremental-basedir='/tmp/restore/'

innobackupex: Waiting for ibbackup (pid=12898) to suspend

innobackupex: Suspend file '/tmp/data/2013-03-07_21-13-44/xtrabackup_suspended'

xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)

incremental backup from 59605543 is enabled.

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /var/lib/mysql

xtrabackup: Target instance is assumed as followings.

xtrabackup: innodb_data_home_dir = ./

xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend

xtrabackup: innodb_log_group_home_dir = ./

xtrabackup: innodb_log_files_in_group = 2

xtrabackup: innodb_log_file_size = 5242880

130307 21:13:46 InnoDB: Using Linux native AIO

>> log scanned up to (59606124)

[01] Copying ./ibdata1

to /tmp/data/2013-03-07_21-13-44/ibdata1.delta

[01]...done

130307 21:13:50 innobackupex: Continuing after ibbackup has suspended

130307 21:13:50 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered --

130307 21:13:50 innobackupex: Connected to database with mysql child process (pid=12913)

>> log scanned up to (59606124)

130307 21:13:52 innobackupex: Starting to lock all tables...

>> log scanned up to (59606124)

>> log scanned up to (59606124)

130307 21:14:03 innobackupex: All tables locked and flushed to disk

130307 21:14:03 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,

innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in

innobackupex: subdirectories of '/var/lib/mysql'

innobackupex: Backing up file '/var/lib/mysql/test/test.frm'

innobackupex: Backing up file '/var/lib/mysql/test/db.opt'

130307 21:14:03 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files

innobackupex: Resuming ibbackup

xtrabackup: The latest check point (for incremental): '59606124'

>> log scanned up to (59606124)

xtrabackup: Stopping log copying thread.

xtrabackup: Transaction log of lsn (59606124) to (59606124) was copied.

130307 21:14:05 innobackupex: All tables unlocked

130307 21:14:05 innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/tmp/data/2013-03-07_21-13-44'

innobackupex: MySQL binlog position: filename 'mysql-bin.000023', position 107

130307 21:14:05 innobackupex: completed OK!

其中，--incremental指明是增量备份，--incremental-basedir指定上次完整备份或者增量备份文件的位置。这里的增量备份其实只针对的是InnoDB，对于MyISAM来说，还是完整备份。

在进行增量备份的恢复之前，先关闭数据库，然后删除数据库test

root@client2:/var/lib/mysql# service mysql stop

mysql stop/waiting

root@client2:/var/lib/mysql# rm -rf test

root@client2:/var/lib/mysql# ll

total 77856

drwx------ 7 mysql mysql 4096 Mar 7 21:17 ./

drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../

-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag

drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/

-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:17 ibdata1

-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:17 ib_logfile0

-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:11 ib_logfile1

drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/

drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/

-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info

drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/

drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/

增量备份的恢复

root@client2:/var/lib/mysql# innobackupex -user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --apply-log /tmp/restore/ --incremental-dir=/tmp/data/2013-03-07_21-13-44/

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 apply-log run completes successfully.

At the end of a successful apply-log run innobackupex

prints "completed OK!".

130307 21:18:20 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/mysql/my.cnf" --prepare --target-dir=/tmp/restore --incremental-dir=/tmp/data/2013-03-07_21-13-44/

xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)

incremental backup from 59605543 is enabled.

xtrabackup: cd to /tmp/restore

xtrabackup: This target seems to be already prepared.

xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(59606124)

xtrabackup: page size for /tmp/data/2013-03-07_21-13-44//ibdata1.delta is 16384 bytes

Applying /tmp/data/2013-03-07_21-13-44//ibdata1.delta ...

xtrabackup: Temporary instance for recovery is set as followings.

xtrabackup: innodb_data_home_dir = ./

xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend

xtrabackup: innodb_log_group_home_dir = /tmp/data/2013-03-07_21-13-44/

xtrabackup: innodb_log_files_in_group = 1

xtrabackup: innodb_log_file_size = 2097152

130307 21:18:20 InnoDB: Using Linux native AIO

xtrabackup: Starting InnoDB instance for recovery.

xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)

130307 21:18:20 InnoDB: The InnoDB memory heap is disabled

130307 21:18:20 InnoDB: Mutexes and rw_locks use GCC atomic builtins

130307 21:18:20 InnoDB: Compressed tables use zlib 1.2.3

130307 21:18:20 InnoDB: Using Linux native AIO

130307 21:18:20 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead

130307 21:18:20 InnoDB: Initializing buffer pool, size = 100.0M

130307 21:18:20 InnoDB: Completed initialization of buffer pool

130307 21:18:20 InnoDB: highest supported file format is Barracuda.

InnoDB: ##########################################################

InnoDB: WARNING!

InnoDB: The log sequence number in ibdata files is higher

InnoDB: than the log sequence number in the ib_logfiles! Are you sure

InnoDB: you are using the right ib_logfiles to start up the database?

InnoDB: Log sequence number in ib_logfiles is 59606124, log

InnoDB: sequence numbers stamped to ibdata file headers are between

InnoDB: 59607052 and 59607052.

InnoDB: ##########################################################

InnoDB: The log sequence number in ibdata files does not match

InnoDB: the log sequence number in the ib_logfiles!

130307 21:18:20 InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000023

130307 21:18:29 InnoDB: Waiting for the background threads to start

130307 21:18:30 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59606124

[notice (again)]

If you use binary log and don't use any hack of group commit,

the binary log position seems to be:

InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000023

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

130307 21:18:30 InnoDB: Starting shutdown...

130307 21:18:34 InnoDB: Shutdown completed; log sequence number 59607339

innobackupex: Starting to copy non-InnoDB files in '/tmp/data/2013-03-07_21-13-44/'

innobackupex: to the full backup directory '/tmp/restore'

innobackupex: Copying '/tmp/data/2013-03-07_21-13-44/xtrabackup_binlog_info' to '/tmp/restore/xtrabackup_binlog_info'

innobackupex: Copying '/tmp/data/2013-03-07_21-13-44/test/test.frm' to '/tmp/restore/test/test.frm'

innobackupex: Copying '/tmp/data/2013-03-07_21-13-44/test/db.opt' to '/tmp/restore/test/db.opt'

130307 21:18:34 innobackupex: completed OK!

然后再进入恢复的目录/tmp/data

root@client2:/var/lib/mysql# cd /tmp/data

root@client2:/tmp/data# ll

total 3276

drwxr-xr-x 3 root root4096 Mar 7 21:13 ./

drwxrwxrwt 14 root root4096 Mar 7 21:18 ../

drwxr-xr-x 3 root root4096 Mar 7 21:18 2013-03-07_21-13-44/

-rw-r--r-- 1 root root3780 Mar 7 21:02 test-201303072101.log

-rw-r--r-- 1 root root 3336909 Mar 7 21:02 test-201303072101.tar.gz

root@client2:/tmp/data# cd 2013-03-07_21-13-44/

root@client2:/tmp/data/2013-03-07_21-13-44# ll

total 2288

drwxr-xr-x 3 root root4096 Mar 7 21:18 ./

drwxr-xr-x 3 root root4096 Mar 7 21:13 ../

-rw-r--r-- 1 root root 260 Mar 7 21:13 backup-my.cnf

-rw-r--r-- 1 root root 212992 Mar 7 21:13 ibdata1.delta

-rw-r--r-- 1 root root 18 Mar 7 21:13 ibdata1.meta

drwxr-xr-x 2 root root4096 Mar 7 21:14 test/

-rw-r--r-- 1 root root 13 Mar 7 21:14 xtrabackup_binary

-rw-r--r-- 1 root root 23 Mar 7 21:14 xtrabackup_binlog_info

-rw-r--r-- 1 root root 84 Mar 7 21:14 xtrabackup_checkpoints

-rw-r--r-- 1 root root 2097152 Mar 7 21:18 xtrabackup_logfile

跟全部备份一样，把test恢复到/var/lib/mysql里

root@client2:/tmp/data/2013-03-07_21-13-44# rsync -avz test ib* /var/lib/mysql/

sending incremental file list

test/

test/db.opt

test/test.frm

sent 381 bytes received 54 bytes 870.00 bytes/sec

total size is 8621 speedup is 19.82

root@client2:/tmp/data/2013-03-07_21-13-44# cd /var/lib/mysql/

root@client2:/var/lib/mysql# ll

total 77860

drwx------ 8 mysql mysql 4096 Mar 7 21:19 ./

drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../

-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag

drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/

-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:17 ibdata1

-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:17 ib_logfile0

-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:11 ib_logfile1

drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/

drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/

-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info

drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/

drwxr-xr-x 2 root root 4096 Mar 7 21:14 test/

drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/

然后修改用户与组

root@client2:/var/lib/mysql# chown -R mysql:mysql test/

root@client2:/var/lib/mysql# ll

total 77860

drwx------ 8 mysql mysql 4096 Mar 7 21:19 ./

drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../

-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag

drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/

-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:17 ibdata1

-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:17 ib_logfile0

-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:11 ib_logfile1

drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/

drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/

-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info

drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/

drwxr-xr-x 2 mysql mysql 4096 Mar 7 21:14 test/

drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/

然后启动msyql，并查看test数据库里test表的内容

root@client2:/var/lib/mysql# service mysql start

mysql start/running, process 13109

root@client2:/var/lib/mysql# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 36

Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from test;

+------+

| id |

+------+

|1 |

|2 |

|3 |

|4 |

|5 |

| 11 |

| 12 |

| 13 |

| 14 |

| 15 |

+------+

10 rows in set (0.00 sec)

可以看到增量备份已经恢复完成。

下面的链接