MySQL 备份恢复

1.环境

OS: CentOS 6.5 x64

MySQL: 5.6 for Linux (x86_64)

2.最简单的备份

mysqldump -uroot -p --single-transaction --all-databases > backup.sql

--single-transaction参数的使用是假设备份的表是innodb表，备份是一个事务中完成，只在备份开始时获取一下全局读锁，然后不再锁表。

3.mysqldump方式增量备份

周日全量备份：

mysqldump -uroot -p --single-transaction--flush-logs --master-data=2 incase > backup.sql

此时备份文件里会增加一条：

--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE=' mysql-bin.000019', MASTER_LOG_POS=120;

周一增量备份：

mysqladmin -uroot -pflush-logs

这时会产生新的日志文件，例如mysql-bin.000021，日志文件的查看可以到日志目录（默认是数据目录）下用ll -rth查看，也可以登录mysql客户端执行show master logs;查看。

只要把mysql-bin.000019，mysql-bin.000020复制走就可以了。

如果嫌日志文件太多，在执行全量备份时可以删除不再需要的日志：

mysqldump -uroot -p --single-transaction --flush-logs --master-data=2--delete-master-logs incase > backup.sql

删除日志需慎重，如果只备份一个库，日志也会被删除，这时别的库可能还需要这些日志文件。

4.mysqldump方式恢复

mysql -uroot -p < backup.sql     #先从全量备份里恢复

#假如备份的日志文件为mysql-bin.000019，mysql-bin.000020，执行以下命令：

mysqlbinlogmysql-bin.000019mysql-bin.000020 | mysql -uroot -pxxxx

如果需要执行的日志太多，可以用以下方式执行：

export TMPDIR=/my/big/disk/tmp
mysqlbinlog mysql-bin.* | mysql -uroot -pxxxx

这样会将当前目录下所有的mysql-bin按顺序执行。

5.xtrabackup进行增量备份，恢复

1，创建mysql备份用户


mysql -uroot --password="" -e"CREATE USER 'backup'@'192.168.%' IDENTIFIED BY '123456'";


mysql -uroot --password="" -e"GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE, SUPERON *.* TO 'backup'@'192.168.%'";


 


2，xtrabackup进行全量备份


备份命令：


xtrabackup --defaults-file=/etc/my.cnf--user=backup --password="123456" --port=3306 --backup--target-dir=/data/backups/mysql/full_incre_$(date +%Y%m%d)


PS: 2>/tmp/fullbackup.log 将备份信息放入文件中。


 


备份过程信息如下：


[root@data01 ~]# xtrabackup--defaults-file=/etc/my.cnf --user=backup --password="123456"--port=3306 --backup --target-dir=/data/backups/mysql/full_incre_$(date+%Y%m%d)
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/data/mysql/data
xtrabackup: open files limit requested 0,set to 1024
xtrabackup: using the following InnoDBconfiguration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 67108864
>> log scanned up to (423920168)
xtrabackup: Generating a list of tablespaces
[01] Copying ./IBdata1 to/data/backups/mysql/full_incre_20150319/IBdata1
>> log scanned up to (423920168)
>> log scanned up to (423920168)
[01]       ...done
[01] Copying ./IBdata2 to/data/backups/mysql/full_incre_20150319/IBdata2
>> log scanned up to (423920168)
>> log scanned up to (423920168)
[01]       ...done
[01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/full_incre_20150319/mysql/slave_master_info.ibd
[01]       ...done
[01] Copying ./mysql/slave_relay_log_info.ibdto /data/backups/mysql/full_incre_20150319/mysql/slave_relay_log_info.ibd
[01]       ...done
[01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/full_incre_20150319/mysql/innodb_index_stats.ibd
[01]       ...done
[01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/full_incre_20150319/mysql/slave_worker_info.ibd
[01]       ...done
[01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/full_incre_20150319/mysql/innodb_table_stats.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest1.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest5.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest8.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest4.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest2.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest6.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest7.ibd to /data/backups/mysql/full_incre_20150319/sbtest/sbtest7.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest3.ibd
[01]       ...done
[01] Copying ./test/t1.ibd to/data/backups/mysql/full_incre_20150319/test/t1.ibd
[01]       ...done
[01] Copying ./test/t3.ibd to/data/backups/mysql/full_incre_20150319/test/t3.ibd
[01]       ...done
[01] Copying ./test/t.ibd to/data/backups/mysql/full_incre_20150319/test/t.ibd
[01]       ...done
[01] Copying ./user_db/t1.ibd to/data/backups/mysql/full_incre_20150319/user_db/t1.ibd
[01]       ...done
[01] Copying ./user_db/test.ibd to/data/backups/mysql/full_incre_20150319/user_db/test.ibd
[01]       ...done
[01] Copying ./user_db/test2.ibd to/data/backups/mysql/full_incre_20150319/user_db/test2.ibd
[01]       ...done
[01] Copying ./user_db/t3.ibd to/data/backups/mysql/full_incre_20150319/user_db/t3.ibd
[01]       ...done
[01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/full_incre_20150319/user_db/UC_USER.ibd
>> log scanned up to (423920168)
[01]       ...done
[01] Copying ./user_db/t.ibd to/data/backups/mysql/full_incre_20150319/user_db/t.ibd
[01]       ...done
xtrabackup: The latest check point (forincremental): '423920168'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423920168)
  
xtrabackup: Transaction log of lsn(423920168) to (423920168) was copied.
[root@data01 ~]#




3，xtrabackup进行增量备份


3.1先录入一张表，实现第一次增量数据：
mysql> create table t4 select 'aaa' asa, 1 as b;
Query OK, 1 row affected (0.36 sec)
Records: 1 Duplicates: 0  Warnings: 0
  
mysql>
mysql> insert into t4 select 'bbb',2;
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0  Warnings: 0
  
mysql>


 


3.2 再进行增量备份1
xtrabackup --defaults-file=/etc/my.cnf--backup --user=backup --password="123456" --port=3306 --target-dir=/data/backups/mysql/incre_20150320/--incremental-basedir=/data/backups/mysql/full_incre_201503192>/tmp/incre_backup_1.log


查看备份日志信息：
[root@data01 ~]# more/tmp/incre_backup_1.log
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 423920168 isenabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/data/mysql/data
xtrabackup: open files limit requested 0,set to 1024
xtrabackup: using the following InnoDBconfiguration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 67108864
>> log scanned up to (423925813)
xtrabackup: Generating a list oftablespaces
xtrabackup: using the full scan forincremental backup
[01] Copying ./IBdata1 to/data/backups/mysql/incre_20150320/IBdata1.delta
>> log scanned up to (423925813)
[01]       ...done
[01] Copying ./IBdata2 to/data/backups/mysql/incre_20150320/IBdata2.delta
>> log scanned up to (423925813)
[01]       ...done
>> log scanned up to (423925813)
[01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/incre_20150320/mysql/slave_master_info.ibd.delta
[01]       ...done
[01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/incre_20150320/mysql/slave_relay_log_info.ibd.delta
[01]       ...done
[01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/incre_20150320/mysql/innodb_index_stats.ibd.delta
[01]       ...done
[01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/incre_20150320/mysql/slave_worker_info.ibd.delta
[01]       ...done
[01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/incre_20150320/mysql/innodb_table_stats.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest1.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest5.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest8.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest4.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest2.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest6.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest7.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest3.ibd.delta
[01]       ...done
[01] Copying ./test/t1.ibd to/data/backups/mysql/incre_20150320/test/t1.ibd.delta
[01]       ...done
[01] Copying ./test/t4.ibd to/data/backups/mysql/incre_20150320/test/t4.ibd.delta
[01]       ...done
[01] Copying ./test/t3.ibd to/data/backups/mysql/incre_20150320/test/t3.ibd.delta
[01]       ...done
[01] Copying ./test/t.ibd to/data/backups/mysql/incre_20150320/test/t.ibd.delta
[01]       ...done
[01] Copying ./user_db/t1.ibd to /data/backups/mysql/incre_20150320/user_db/t1.ibd.delta
[01]       ...done
[01] Copying ./user_db/test.ibd to/data/backups/mysql/incre_20150320/user_db/test.ibd.delta
[01]       ...done
>> log scanned up to (423925813)
[01] Copying ./user_db/test2.ibd to /data/backups/mysql/incre_20150320/user_db/test2.ibd.delta
[01]       ...done
[01] Copying ./user_db/t3.ibd to/data/backups/mysql/incre_20150320/user_db/t3.ibd.delta
[01]       ...done
[01] Copying ./user_db/UC_USER.ibd to /data/backups/mysql/incre_20150320/user_db/UC_USER.ibd.delta
[01]       ...done
[01] Copying ./user_db/t.ibd to/data/backups/mysql/incre_20150320/user_db/t.ibd.delta
[01]       ...done
xtrabackup: The latest check point (forincremental): '423925813'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423925813)
  
xtrabackup: Transaction log of lsn(423925813) to (423925813) was copied.




3.3 再建立表2，实现第二天增量数据
建立一张表t_incre_2：
mysql> use test;
Database changed
mysql> create table t_incre_2 select'cc' as a,2 as b;
Query OK, 1 row affected (0.25 sec)
Records: 1 Duplicates: 0  Warnings: 0
  
mysql> insert into t_incre_2 select'dd',2;
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0  Warnings: 0
  
mysql> select * from t_incre_2;
+----+---+
| a | b |
+----+---+
| cc | 2 |
| dd | 2 |
+----+---+
2 rows in set (0.05 sec)
  
mysql>


3.4 进行第二天的增量备份
备份命令：


xtrabackup --defaults-file=/etc/my.cnf--backup --user=backup --password="123456" --port=3306 --target-dir=/data/backups/mysql/incre_20150321/--incremental-basedir=/data/backups/mysql/incre_20150320 2>/tmp/incre_backup_2.log


备份信息记录如下：
[root@data01 ~]# more /tmp/incre_backup_2.log
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 423925813 isenabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/data/mysql/data
xtrabackup: open files limit requested 0,set to 1024
xtrabackup: using the following InnoDBconfiguration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 67108864
>> log scanned up to (423931610)
xtrabackup: Generating a list oftablespaces
xtrabackup: using the full scan forincremental backup
[01] Copying ./IBdata1 to/data/backups/mysql/incre_20150321/IBdata1.delta
>> log scanned up to (423931610)
>> log scanned up to (423931610)
>> log scanned up to (423931610)
>> log scanned up to (423931610)
>> log scanned up to (423931610)
[01]       ...done
>> log scanned up to (423931610)
[01] Copying ./IBdata2 to/data/backups/mysql/incre_20150321/IBdata2.delta
>> log scanned up to (423931610)
>> log scanned up to (423931610)
[01]       ...done
[01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/incre_20150321/mysql/slave_master_info.ibd.delta
[01]       ...done
[01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/incre_20150321/mysql/slave_relay_log_info.ibd.delta
[01]       ...done
>> log scanned up to (423931610)
[01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/incre_20150321/mysql/innodb_index_stats.ibd.delta
[01]       ...done
[01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/incre_20150321/mysql/slave_worker_info.ibd.delta
[01]       ...done
[01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/incre_20150321/mysql/innodb_table_stats.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest1.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest5.ibd to /data/backups/mysql/incre_20150321/sbtest/sbtest5.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest8.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest4.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest2.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest6.ibd to /data/backups/mysql/incre_20150321/sbtest/sbtest6.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest7.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest3.ibd.delta
[01]       ...done
[01] Copying ./test/t1.ibd to/data/backups/mysql/incre_20150321/test/t1.ibd.delta
[01]       ...done
[01] Copying ./test/t4.ibd to/data/backups/mysql/incre_20150321/test/t4.ibd.delta
[01]       ...done
[01] Copying ./test/t3.ibd to/data/backups/mysql/incre_20150321/test/t3.ibd.delta
[01]       ...done
>> log scanned up to (423931610)
[01] Copying ./test/t.ibd to/data/backups/mysql/incre_20150321/test/t.ibd.delta
[01]       ...done
[01] Copying ./test/t_incre_2.ibd to/data/backups/mysql/incre_20150321/test/t_incre_2.ibd.delta
[01]       ...done
[01] Copying ./user_db/t1.ibd to/data/backups/mysql/incre_20150321/user_db/t1.ibd.delta
[01]       ...done
[01] Copying ./user_db/test.ibd to /data/backups/mysql/incre_20150321/user_db/test.ibd.delta
[01]       ...done
[01] Copying ./user_db/test2.ibd to/data/backups/mysql/incre_20150321/user_db/test2.ibd.delta
[01]       ...done
[01] Copying ./user_db/t3.ibd to/data/backups/mysql/incre_20150321/user_db/t3.ibd.delta
[01]       ...done
[01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/incre_20150321/user_db/UC_USER.ibd.delta
[01]       ...done
[01] Copying ./user_db/t.ibd to/data/backups/mysql/incre_20150321/user_db/t.ibd.delta
[01]       ...done
>> log scanned up to (423931610)
xtrabackup: The latest check point (forincremental): '423931610'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423931610)
  
xtrabackup: Transaction log of lsn(423931610) to (423931610) was copied.


 


4，xtrabackup进行增量恢复


4.1为了验证比对，先删除两个增量备份前表里面的数据
mysql> delete from t4;
Query OK, 2 rows affected (0.05 sec)
  
mysql> delete from t_incre_2;
Query OK, 2 rows affected (0.04 sec)
  
mysql> select * from t4;
Empty set (0.00 sec)
  
mysql> select * from t_incre_2;
Empty set (0.00 sec)
  
mysql>


 


 


4.2开始全备份恢复PREPARE THE BASE BACKUP
备份命令如下：


xtrabackup --defaults-file=/etc/my.cnf--prepare --user=backup --password="123456" --apply-log-only--target-dir=/data/backups/mysql/full_incre_20150319 2>/tmp/full_restore.log


备份信息记录如下：
[root@data01 mysql]# xtrabackup--defaults-file=/etc/my.cnf --prepare --user=backup--password="123456" --apply-log-only --target-dir=/data/backups/mysql/full_incre_20150319
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: cd to/data/backups/mysql/full_incre_20150319
xtrabackup: This target seems to be alreadyprepared.
xtrabackup: notice: xtrabackup_logfile wasalready used to '--prepare'.
xtrabackup: using the following InnoDBconfiguration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 67108864
xtrabackup: using the following InnoDBconfiguration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 67108864
xtrabackup: Starting InnoDB instance forrecovery.
xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count bufferpool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomicbuiltins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size =100.0M
InnoDB: Completed initialization of bufferpool
InnoDB: Setting log file ./ib_logfile101size to 64 MB
InnoDB: Setting log file ./ib_logfile1 sizeto 64 MB
InnoDB: Setting log file ./ib_logfile2 sizeto 64 MB
InnoDB: Renaming log file ./ib_logfile101to ./ib_logfile0
InnoDB: New log files created,LSN=423920168
InnoDB: Highest supported file format isBarracuda.
  
[notice (again)]
  Ifyou use binary log and don't use any hack of group commit,
  thebinary log position seems to be:
InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053
  
xtrabackup: starting shutdown withinnodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequencenumber 423920652
[root@data01 mysql]#


 


4.3 恢复到第一次增量的时刻
恢复命令：


xtrabackup --defaults-file=/etc/my.cnf--prepare --user=backup --password="123456" --apply-log-only--target-dir=/data/backups/mysql/full_incre_20150319--incremental-dir=/data/backups/mysql/incre_20150320 2>/tmp/restore_incre_1.log


恢复过程：
[root@data01 mysql]# more /tmp/restore_incre_1.log
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 423920168 isenabled.
xtrabackup: cd to/data/backups/mysql/full_incre_20150319
xtrabackup: This target seems to be alreadyprepared.
xtrabackup: xtrabackup_logfile detected:size=2097152, start_lsn=(423925813)
xtrabackup: using the following InnoDBconfiguration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir =/data/backups/mysql/incre_20150320
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Generating a list oftablespaces
xtrabackup: page size for /data/backups/mysql/incre_20150320/IBdata2.deltais 16384 bytes
Applying/data/backups/mysql/incre_20150320/IBdata2.delta to ./IBdata2...
xtrabackup: page size for/data/backups/mysql/incre_20150320/IBdata1.delta is 16384 bytes
Applying /data/backups/mysql/incre_20150320/IBdata1.deltato ./IBdata1...
xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/slave_worker_info.ibd.delta is 16384bytes
Applying/data/backups/mysql/incre_20150320/mysql/slave_worker_info.ibd.delta to./mysql/slave_worker_info.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/innodb_table_stats.ibd.delta is 16384bytes
Applying/data/backups/mysql/incre_20150320/mysql/innodb_table_stats.ibd.delta to./mysql/innodb_table_stats.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/innodb_index_stats.ibd.delta is 16384bytes
Applying/data/backups/mysql/incre_20150320/mysql/innodb_index_stats.ibd.delta to./mysql/innodb_index_stats.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/slave_relay_log_info.ibd.delta is16384 bytes
Applying/data/backups/mysql/incre_20150320/mysql/slave_relay_log_info.ibd.delta to./mysql/slave_relay_log_info.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/slave_master_info.ibd.delta is 16384bytes
Applying/data/backups/mysql/incre_20150320/mysql/slave_master_info.ibd.delta to./mysql/slave_master_info.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest4.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/sbtest/sbtest4.ibd.delta to./sbtest/sbtest4.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest3.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/sbtest/sbtest3.ibd.delta to./sbtest/sbtest3.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest1.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/sbtest/sbtest1.ibd.delta to./sbtest/sbtest1.ibd...
xtrabackup: page size for /data/backups/mysql/incre_20150320/sbtest/sbtest7.ibd.deltais 16384 bytes
Applying/data/backups/mysql/incre_20150320/sbtest/sbtest7.ibd.delta to./sbtest/sbtest7.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest6.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/sbtest/sbtest6.ibd.delta to./sbtest/sbtest6.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest8.ibd.delta is 16384 bytes
Applying /data/backups/mysql/incre_20150320/sbtest/sbtest8.ibd.deltato ./sbtest/sbtest8.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest2.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/sbtest/sbtest2.ibd.delta to./sbtest/sbtest2.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest5.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/sbtest/sbtest5.ibd.delta to./sbtest/sbtest5.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/test/t3.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/test/t3.ibd.delta to ./test/t3.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/test/t.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/test/t.ibd.delta to ./test/t.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/test/t4.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/test/t4.ibd.delta to ./test/t4.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/test/t1.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/test/t1.ibd.delta to ./test/t1.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/t3.ibd.delta is 16384 bytes
Applying /data/backups/mysql/incre_20150320/user_db/t3.ibd.deltato ./user_db/t3.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/t.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/user_db/t.ibd.delta to ./user_db/t.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/test2.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/user_db/test2.ibd.delta to./user_db/test2.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/test.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/user_db/test.ibd.delta to./user_db/test.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/t1.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/user_db/t1.ibd.delta to ./user_db/t1.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/UC_USER.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/user_db/UC_USER.ibd.delta to./user_db/UC_USER.ibd...
xtrabackup: using the following InnoDBconfiguration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir =/data/backups/mysql/incre_20150320
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance forrecovery.
xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count bufferpool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomicbuiltins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of bufferpool
InnoDB: Highest supported file format isBarracuda.
InnoDB: The log sequence numbers 423920652and 423920652 in ibdata files do not match the log sequence number 423925813 inthe ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information fromthe .ibd files...
InnoDB: Restoring possible half-writtendata pages
InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053
  
[notice (again)]
  Ifyou use binary log and don't use any hack of group commit,
  thebinary log position seems to be:
InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053
  
xtrabackup: starting shutdown withinnodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequencenumber 423925813


 


4.4 恢复到第二次增量备份前面：
恢复命令：
xtrabackup --defaults-file=/etc/my.cnf--prepare --user=backup --password="123456" --apply-log-only --target-dir=/data/backups/mysql/full_incre_20150319--incremental-dir=/data/backups/mysql/incre_201503212>/tmp/restore_incre_2.log


恢复过程：
[root@data01 mysql]# more/tmp/restore_incre_2.log
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 423925813 isenabled.
xtrabackup: cd to/data/backups/mysql/full_incre_20150319
xtrabackup: This target seems to be alreadyprepared.
xtrabackup: xtrabackup_logfile detected:size=2097152, start_lsn=(423931610)
xtrabackup: using the following InnoDBconfiguration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir =/data/backups/mysql/incre_20150321
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Generating a list oftablespaces
xtrabackup: page size for/data/backups/mysql/incre_20150321/IBdata2.delta is 16384 bytes
Applying /data/backups/mysql/incre_20150321/IBdata2.deltato ./IBdata2...
xtrabackup: page size for/data/backups/mysql/incre_20150321/IBdata1.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/IBdata1.delta to ./IBdata1...
xtrabackup: page size for /data/backups/mysql/incre_20150321/mysql/slave_worker_info.ibd.deltais 16384 bytes
Applying/data/backups/mysql/incre_20150321/mysql/slave_worker_info.ibd.delta to./mysql/slave_worker_info.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/mysql/innodb_table_stats.ibd.delta is 16384bytes
Applying/data/backups/mysql/incre_20150321/mysql/innodb_table_stats.ibd.delta to./mysql/innodb_table_stats.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/mysql/innodb_index_stats.ibd.delta is 16384bytes
Applying/data/backups/mysql/incre_20150321/mysql/innodb_index_stats.ibd.delta to./mysql/innodb_index_stats.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/mysql/slave_relay_log_info.ibd.delta is16384 bytes
Applying/data/backups/mysql/incre_20150321/mysql/slave_relay_log_info.ibd.delta to./mysql/slave_relay_log_info.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/mysql/slave_master_info.ibd.delta is 16384bytes
Applying /data/backups/mysql/incre_20150321/mysql/slave_master_info.ibd.deltato ./mysql/slave_master_info.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest4.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/sbtest/sbtest4.ibd.delta to./sbtest/sbtest4.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest3.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/sbtest/sbtest3.ibd.delta to./sbtest/sbtest3.ibd...
xtrabackup: page size for /data/backups/mysql/incre_20150321/sbtest/sbtest1.ibd.deltais 16384 bytes
Applying/data/backups/mysql/incre_20150321/sbtest/sbtest1.ibd.delta to./sbtest/sbtest1.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest7.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/sbtest/sbtest7.ibd.delta to./sbtest/sbtest7.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest6.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/sbtest/sbtest6.ibd.delta to./sbtest/sbtest6.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest8.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/sbtest/sbtest8.ibd.delta to./sbtest/sbtest8.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest2.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/sbtest/sbtest2.ibd.delta to./sbtest/sbtest2.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest5.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/sbtest/sbtest5.ibd.delta to./sbtest/sbtest5.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t3.ibd.delta is 16384 bytes
Applying /data/backups/mysql/incre_20150321/test/t3.ibd.deltato ./test/t3.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t_incre_2.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/test/t_incre_2.ibd.delta to./test/t_incre_2.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/test/t.ibd.delta to ./test/t.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t4.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/test/t4.ibd.delta to ./test/t4.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t1.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/test/t1.ibd.delta to ./test/t1.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/t3.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/user_db/t3.ibd.delta to ./user_db/t3.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/t.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/user_db/t.ibd.delta to ./user_db/t.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/test2.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/user_db/test2.ibd.delta to./user_db/test2.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/test.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/user_db/test.ibd.delta to./user_db/test.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/t1.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/user_db/t1.ibd.delta to ./user_db/t1.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/UC_USER.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/user_db/UC_USER.ibd.delta to./user_db/UC_USER.ibd...
xtrabackup: using the following InnoDBconfiguration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir =/data/backups/mysql/incre_20150321
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance forrecovery.
xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count bufferpool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomicbuiltins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size =100.0M
InnoDB: Completed initialization of bufferpool
InnoDB: Highest supported file format isBarracuda.
InnoDB: The log sequence numbers 423925813and 423925813 in ibdata files do not match the log sequence number 423931610 inthe ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information fromthe .ibd files...
InnoDB: Restoring possible half-writtendata pages
InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053
  
[notice (again)]
  Ifyou use binary log and don't use any hack of group commit,
  thebinary log position seems to be:
InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053
  
xtrabackup: starting shutdown withinnodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequencenumber 423931610
[root@data01 mysql]#




4.5 恢复整个库
恢复命令：


xtrabackup --defaults-file=/etc/my.cnf--prepare --user=backup --password="123456"--target-dir=/data/backups/mysql/full_incre_20150319 2>/tmp/restore_end_3.log


停止mysql服务：


service mysqld5612 stop


开始rsync数据文件：


cd /data/backups/mysql/full_incre_20150319


rsync -rvt --exclude'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' ./ /home/data/mysql/data


授予mysql访问权限：


chown -R mysql:mysql /home/data/mysql/data


启动mysql服务：


service mysqld5612 start


 


4.6，验证
登录mysql，看到以前在备份之后删除的t4以及t_incre_2表的数据已经通过2次增量备份恢复过来了，如下所示：
mysql> select * from t4;
+-----+---+
| a  | b |
+-----+---+
| aaa | 1 |
| bbb | 2 |
+-----+---+
2 rows in set (0.05 sec)
  
mysql> select * from t_incre_2;
+----+---+
| a | b |
+----+---+
| cc | 2 |
| dd | 2 |
+----+---+
2 rows in set (0.06 sec)
  
mysql>