澳门在线威尼斯官方 > 电脑数据库 > 备份还原篇,MySQL利用xtrabackup举办增量备份详细

原标题:备份还原篇,MySQL利用xtrabackup举办增量备份详细

浏览次数:116 时间:2019-10-13

 

MySQL利用xtrabackup进行增量备份详细经过汇总

Percona XtraBackup 安装介绍篇早已对Percona XtraBackup的意义和装置做了相比较详细的介绍,那么本篇我们直接进去正题,怎样利用XtraBackup做备份、还原,上面首要介绍XtraBackup备份还原操作的细节和现象,错误疏失之处难免存在,如有不足,敬请提议。本篇目前不做原理介绍,后续篇章"Percona 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 将备份消息放入文件中。

 

备份进度音讯如下:

 

[[email protected] ~]# 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.
[[email protected] ~]#

 

 

 

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

 

查阅备份日志新闻:

 

[[email protected] ~]# 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.

 

 

XtraBackup完整备份

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

 

备份消息记录如下:

 

[[email protected] ~]# 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>

 

 

    使用XtraBack做备份,须要三回九转到数据库服务器并在服务器上有相关目录操作权限,所以须要求有数据库的连带操作权限以至有关目录试行READ、W路虎极光ITE以致EXECUTE(实行innobackupex 或xtrabackup 命令)的系统权限。上边大家来简要介绍一下:

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

 

备份消息记录如下:

 

[[email protected] 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
[[email protected] mysql]#

 

 

4.3 恢复生机到第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_20150320 2>/tmp/restore_incre_1.log

回复进程:

 

[[email protected] 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

 

重整旗鼓进度:

 

[[email protected] 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
[[email protected] 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>

 

MySQL 数据库权限

 权限描述

RELOAD/ LOCK TABLES

RELOAD权限是管理员级别权限,用来执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables,flush-threads, refresh, reload等命令的权限。XtraBackup在复制文件之前,必须先执行

FLUSH TABLES WITH READ LOCK和 FLUSH ENGINE LOGS等

REPLICATION CLIENT

此权限是为了获取二进制日志位置(Position)

CREATE TABLESPACE

此权限是为了导入表

PROCESS

此权限是因为要运行SHOW ENGINE INNODB STATUS命令,以及查看服务器上所有运行的线程。

SUPER

此权限是为了开启、关闭复制环境中的slave threads

CREATE

不是必须,Percon Server数据库才需要的。创建PERCONA_SCHEMA.xtrabackup_history的需要

INSERT

不是必须,跟上面权限一样,需要往PERCONA_SCHEMA.xtrabackup_history插入记录

SELECT

不是必须,跟上面权限一样,需要查询ERCONA_SCHEMA.xtrabackup_history中的记录

5,innobackupex先做全量备份

指令如下:

innobackupex --defaults-file=/etc/my.cnf--user=backup --password="123456" --host=192.168.52.129/data/backups/mysql/innobackupex_full_20150319/ --no-timestamp 2>/tmp/innobackupex_full.log

 

备份进度音讯:

 

[[email protected] mysql]# more/tmp/innobackupex_full.log

InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.

Get the latest version of PerconaXtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

150319 02:31:18  innobackupex: Connecting to MySQL server withDSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup;host=192.168.52.129'as 'backup'  (using
 password: YES).
150319 02:31:18  innobackupex: Connected to MySQL server
150319 02:31:18  innobackupex: Executing a version checkagainst the server...
150319 02:31:18  innobackupex: Done.
150319 02:31:18  innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup runcompletes successfully.
          At the end of a successful backup run innobackupex
          prints "completed OK!".

innobackupex:  Using server version 5.6.12-log

innobackupex: Created backup directory/data/backups/mysql/innobackupex_full_20150319

150319 02:31:18  innobackupex: Starting ibbackup with command:xtrabackup --defaults-file="/etc/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end--target-dir=/data/backups/mysql/
innobackupex_full_20150319 --tmpdir=/tmp--extra-lsndir='/tmp'
innobackupex: Waiting for ibbackup(pid=13118) to suspend
innobackupex: Suspend file'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_suspended_2'

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, setto 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 (423920964)
xtrabackup: Generating a list oftablespaces
[01] Copying ./IBdata1 to/data/backups/mysql/innobackupex_full_20150319/IBdata1
>> log scanned up to (423920964)
[01]       ...done
[01] Copying ./IBdata2 to/data/backups/mysql/innobackupex_full_20150319/IBdata2
>> log scanned up to (423920964)
>> log scanned up to (423920964)
[01]       ...done
>> log scanned up to (423920964)
[01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/innobackupex_full_20150319/mysql/slave_master_info.ibd
[01]       ...done
[01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/innobackupex_full_20150319/mysql/slave_relay_log_info.ibd
[01]       ...done
[01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/innobackupex_full_20150319/mysql/innodb_index_stats.ibd
[01]       ...done
[01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/innobackupex_full_20150319/mysql/slave_worker_info.ibd
[01]       ...done
[01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/innobackupex_full_20150319/mysql/innodb_table_stats.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest1.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest5.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest8.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest4.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest2.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest6.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest7.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest3.ibd
[01]       ...done
[01] Copying ./test/t1.ibd to/data/backups/mysql/innobackupex_full_20150319/test/t1.ibd
[01]       ...done
[01] Copying ./test/t4.ibd to/data/backups/mysql/innobackupex_full_20150319/test/t4.ibd
[01]       ...done
[01] Copying ./test/t3.ibd to/data/backups/mysql/innobackupex_full_20150319/test/t3.ibd
[01]       ...done
[01] Copying ./test/t.ibd to /data/backups/mysql/innobackupex_full_20150319/test/t.ibd
[01]       ...done
[01] Copying ./test/t_incre_2.ibd to/data/backups/mysql/innobackupex_full_20150319/test/t_incre_2.ibd
[01]       ...done
[01] Copying ./user_db/t1.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/t1.ibd
[01]       ...done
[01] Copying ./user_db/test.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/test.ibd
[01]       ...done
[01] Copying ./user_db/test2.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/test2.ibd
[01]       ...done
[01] Copying ./user_db/t3.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/t3.ibd
[01]       ...done
[01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/UC_USER.ibd
>> log scanned up to (423920964)
[01]       ...done
[01] Copying ./user_db/t.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/t.ibd
[01]       ...done
xtrabackup: Creating suspend file'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_suspended_2' withpid '13118'

150319 02:31:24  innobackupex: Continuing after ibbackup hassuspended
150319 02:31:24  innobackupex: Executing FLUSH TABLES WITHREAD LOCK...
150319 02:31:24  innobackupex: All tables locked and flushedto disk

150319 02:31:24  innobackupex: Starting to backup non-InnoDBtables and files
innobackupex: in subdirectories of'/home/data/mysql/data/'
innobackupex: Backing up files'/home/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(53 files)
>> log scanned up to (423920964)
innobackupex: Backing up files'/home/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(74 files)
>> log scanned up to (423920964)
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest3.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest4.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest6.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest8.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest1.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/db.opt'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest2.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest7.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest5.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t_incre_2.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t3.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t4.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t1.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/UC_USER.frm'
innobackupex: Backing up file '/home/data/mysql/data//user_db/t.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t3.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/test.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/db.opt'
innobackupex: Backing up file'/home/data/mysql/data//user_db/test2.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t1.frm'
150319 02:31:26  innobackupex: Finished backing up non-InnoDBtables and files

150319 02:31:26  innobackupex: Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS...
150319 02:31:26  innobackupex: Waiting for log copying tofinish

>> log scanned up to (423920964)
xtrabackup: The latest check point (forincremental): '423920964'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423920964)

xtrabackup: Creating suspend file'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_log_copied' with pid'13118'
xtrabackup: Transaction log of lsn(423920964) to (423920964) was copied.
150319 02:31:27  innobackupex: All tables unlocked

innobackupex: Backup created in directory'/data/backups/mysql/innobackupex_full_20150319'
innobackupex: MySQL binlog position:filename 'mysql-bin.000066', position 120
150319 02:31:27  innobackupex: Connection to database serverclosed
150319 02:31:27  innobackupex: completed OK!
[[email protected] mysql]#

 

   

6,innobackupex做增量备份

 

6.1做第叁次增量备份

先录入增量数据

 

mysql> create table incre1 select'incre1 table ' as incre1, 'a' as id;
Query OK, 1 row affected (0.06 sec)
Records: 1 Duplicates: 0  Warnings: 0

mysql> select * from incre1;
+---------------+----+
| incre1        | id |
+---------------+----+
| incre1 table  | a  |
+---------------+----+
1 row in set (0.00 sec)

mysql>

 

 

再张开增量备份,命令如下:

innobackupex --incremental/data/backups/mysql/innobackex_incre_1--incremental-basedir=/data/backups/mysql/innobackupex_full_20150319/--user=backup --password="123456" --host=192.168.52.129 --no-timestamp2>/tmp/innobackex_incre_1.log

 

备份进程新闻如下:

 

[[email protected] mysql]# more/tmp/innobackex_incre_1.log

InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.

Get the latest version of PerconaXtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

150319 02:38:04  innobackupex: Connecting to MySQL server withDSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.52.129' as'backup'  (using password: YES).
150319 02:38:04  innobackupex: Connected to MySQL server
150319 02:38:04  innobackupex: Executing a version checkagainst the server...
150319 02:38:04  innobackupex: Done.
150319 02:38:04  innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup runcompletes successfully.
          At the end of a successful backup run innobackupex
          prints "completed OK!".

innobackupex:  Using server version 5.6.12-log

innobackupex: Created backup directory/data/backups/mysql/innobackex_incre_1

150319 02:38:04  innobackupex: Starting ibbackup with command:xtrabackup --defaults-group="mysqld" --backup --suspend-at-end--target-dir=/data/backups/mysql/innobackex_incre_1 --tmpdir=/tm
p --extra-lsndir='/tmp'--incremental-basedir='/data/backups/mysql/innobackupex_full_20150319/'
innobackupex: Waiting for ibbackup(pid=13358) to suspend
innobackupex: Suspend file'/data/backups/mysql/innobackex_incre_1/xtrabackup_suspended_2'

xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 423920964 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 (423925361)
xtrabackup: Generating a list oftablespaces
xtrabackup: using the full scan forincremental backup
>> log scanned up to (423925361)
>> log scanned up to (423925361)
>> log scanned up to (423925361)
>> log scanned up to (423925361)
[01] Copying ./IBdata1 to/data/backups/mysql/innobackex_incre_1/IBdata1.delta
>> log scanned up to (423925361)
[01]       ...done
>> log scanned up to (423925361)
[01] Copying ./IBdata2 to /data/backups/mysql/innobackex_incre_1/IBdata2.delta
[01]       ...done
[01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/innobackex_incre_1/mysql/slave_master_info.ibd.delta
[01]       ...done
[01] Copying./mysql/slave_relay_log_info.ibd to /data/backups/mysql/innobackex_incre_1/mysql/slave_relay_log_info.ibd.delta
[01]       ...done
[01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/innobackex_incre_1/mysql/innodb_index_stats.ibd.delta
[01]       ...done
[01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/innobackex_incre_1/mysql/slave_worker_info.ibd.delta
[01]       ...done
>> log scanned up to (423925361)
[01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/innobackex_incre_1/mysql/innodb_table_stats.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest1.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest5.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest8.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest4.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest2.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest6.ibd.delta
[01]       ...done
>> log scanned up to (423925361)
[01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest7.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest3.ibd.delta
[01]        ...done
[01] Copying ./test/t1.ibd to/data/backups/mysql/innobackex_incre_1/test/t1.ibd.delta
[01]       ...done
[01] Copying ./test/t4.ibd to/data/backups/mysql/innobackex_incre_1/test/t4.ibd.delta
[01]       ...done
[01] Copying ./test/incre1.ibd to/data/backups/mysql/innobackex_incre_1/test/incre1.ibd.delta
[01]       ...done
[01] Copying ./test/t3.ibd to/data/backups/mysql/innobackex_incre_1/test/t3.ibd.delta
[01]       ...done
[01] Copying ./test/t.ibd to/data/backups/mysql/innobackex_incre_1/test/t.ibd.delta
[01]       ...done
[01] Copying ./test/t_incre_2.ibd to/data/backups/mysql/innobackex_incre_1/test/t_incre_2.ibd.delta
[01]       ...done
[01] Copying ./user_db/t1.ibd to/data/backups/mysql/innobackex_incre_1/user_db/t1.ibd.delta
[01]       ...done
[01] Copying ./user_db/test.ibd to/data/backups/mysql/innobackex_incre_1/user_db/test.ibd.delta
[01]       ...done
[01] Copying ./user_db/test2.ibd to/data/backups/mysql/innobackex_incre_1/user_db/test2.ibd.delta
[01]       ...done
[01] Copying ./user_db/t3.ibd to/data/backups/mysql/innobackex_incre_1/user_db/t3.ibd.delta
[01]       ...done
[01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/innobackex_incre_1/user_db/UC_USER.ibd.delta
[01]       ...done
[01] Copying ./user_db/t.ibd to/data/backups/mysql/innobackex_incre_1/user_db/t.ibd.delta
[01]       ...done
>> log scanned up to (423925361)
xtrabackup: Creating suspend file'/data/backups/mysql/innobackex_incre_1/xtrabackup_suspended_2' with pid'13358'
>> log scanned up to (423925361)

150319 02:38:15  innobackupex: Continuing after ibbackup hassuspended
150319 02:38:15  innobackupex: Executing FLUSH TABLES WITHREAD LOCK...
150319 02:38:15  innobackupex: All tables locked and flushedto disk

150319 02:38:15  innobackupex: Starting to backup non-InnoDBtables and files
innobackupex: in subdirectories of'/home/data/mysql/data/'
innobackupex: Backing up files'/home/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(53 files)
>> log scanned up to (423925361)
innobackupex: Backing up files'/home/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(74 files)
>> log scanned up to (423925361)
innobackupex: Backing up file '/home/data/mysql/data//sbtest/sbtest3.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest4.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest6.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest8.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest1.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/db.opt'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest2.frm'
innobackupex: Backing up file '/home/data/mysql/data//sbtest/sbtest7.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest5.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t_incre_2.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t3.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t4.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/incre1.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t1.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/UC_USER.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t3.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/test.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/db.opt'
innobackupex: Backing up file'/home/data/mysql/data//user_db/test2.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t1.frm'
150319 02:38:17  innobackupex: Finished backing up non-InnoDBtables and files

150319 02:38:17  innobackupex: Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS...
150319 02:38:17  innobackupex: Waiting for log copying tofinish

xtrabackup: The latest check point (forincremental): '423925361'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423925361)

xtrabackup: Creating suspend file'/data/backups/mysql/innobackex_incre_1/xtrabackup_log_copied' with pid '13358'
xtrabackup: Transaction log of lsn(423925361) to (423925361) was copied.
150319 02:38:18  innobackupex: All tables unlocked

innobackupex: Backup created in directory'/data/backups/mysql/innobackex_incre_1'
innobackupex: MySQL binlog position:filename 'mysql-bin.000066', position 120
150319 02:38:18  innobackupex: Connection to database server closed
150319 02:38:18  innobackupex: completed OK!
[[email protected] mysql]#

 

不容置疑所需的矮小权限如下所示,注意:那么些只是是细微权限,实际情况中最棒依照实际要求授予最小权限。不要放大不须要的权杖。

6.2 基于全备和率先个增量备份来做第三回增量备份

先录入增量数据录入

 

mysql> create table incre2 select'incre2' as a,3 as id;
Query OK, 1 row affected (0.12 sec)
Records: 1 Duplicates: 0  Warnings: 0

mysql>
mysql> select * from incre2;
+--------+----+
| a     | id |
+--------+----+
| incre2 | 3 |
+--------+----+
1 row in set (0.00 sec)

mysql>

 

始于开展第三回增量备份,备份命令:

innobackupex --incremental/data/backups/mysql/innobackex_incre_2 --incremental-basedir=/data/backups/mysql/innobackex_incre_1/--user=backup --password="123456" --host=192.168.52.129 --no-timestamp2>/tmp/innobackex_incre_2.log

 

备份过程音信如下:

 

[[email protected] innobackex_incre_1]# more/tmp/innobackex_incre_2.log

InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.

Get the latest version of PerconaXtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

150319 02:51:09  innobackupex: Connecting to MySQL server withDSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.52.129' as'backup'  (using password: YES).
150319 02:51:09  innobackupex: Connected to MySQL server
150319 02:51:09  innobackupex: Executing a version checkagainst the server...
150319 02:51:09  innobackupex: Done.
150319 02:51:09  innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup runcompletes successfully.
          At the end of a successful backup run innobackupex
          prints "completed OK!".

innobackupex:  Using server version 5.6.12-log

innobackupex: Created backup directory/data/backups/mysql/innobackex_incre_2

150319 02:51:10  innobackupex: Starting ibbackup with command:xtrabackup --defaults-group="mysqld" --backup --suspend-at-end--target-dir=/data/backups/mysql/innobackex_incre_2 --tmpdir=/tm
p --extra-lsndir='/tmp'--incremental-basedir='/data/backups/mysql/innobackex_incre_1/'
innobackupex: Waiting for ibbackup(pid=13694) to suspend
innobackupex: Suspend file'/data/backups/mysql/innobackex_incre_2/xtrabackup_suspended_2'

xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 423925361 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 (423929601)
xtrabackup: Generating a list oftablespaces
xtrabackup: using the full scan forincremental backup
>> log scanned up to (423929601)
[01] Copying ./IBdata1 to/data/backups/mysql/innobackex_incre_2/IBdata1.delta
>> log scanned up to (423929601)
[01]       ...done
[01] Copying ./IBdata2 to/data/backups/mysql/innobackex_incre_2/IBdata2.delta
[01]       ...done
[01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/innobackex_incre_2/mysql/slave_master_info.ibd.delta
[01]       ...done
>> log scanned up to (423929601)
[01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/innobackex_incre_2/mysql/slave_relay_log_info.ibd.delta
[01]       ...done
[01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/innobackex_incre_2/mysql/innodb_index_stats.ibd.delta
[01]       ...done
[01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/innobackex_incre_2/mysql/slave_worker_info.ibd.delta
[01]       ...done
[01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/innobackex_incre_2/mysql/innodb_table_stats.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest1.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest5.ibd to /data/backups/mysql/innobackex_incre_2/sbtest/sbtest5.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest8.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest4.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest2.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest6.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest7.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest3.ibd.delta
[01]       ...done
[01] Copying ./test/incre2.ibd to/data/backups/mysql/innobackex_incre_2/test/incre2.ibd.delta
[01]       ...done
[01] Copying ./test/t1.ibd to/data/backups/mysql/innobackex_incre_2/test/t1.ibd.delta
[01]       ...done
[01] Copying ./test/t4.ibd to/data/backups/mysql/innobackex_incre_2/test/t4.ibd.delta
[01]       ...done
[01] Copying ./test/incre1.ibd to/data/backups/mysql/innobackex_incre_2/test/incre1.ibd.delta
[01]       ...done
[01] Copying ./test/t3.ibd to/data/backups/mysql/innobackex_incre_2/test/t3.ibd.delta
[01]       ...done
>> log scanned up to (423929601)
[01] Copying ./test/t.ibd to/data/backups/mysql/innobackex_incre_2/test/t.ibd.delta
[01]       ...done
[01] Copying ./test/t_incre_2.ibd to /data/backups/mysql/innobackex_incre_2/test/t_incre_2.ibd.delta
[01]       ...done
[01] Copying ./user_db/t1.ibd to/data/backups/mysql/innobackex_incre_2/user_db/t1.ibd.delta
[01]       ...done
[01] Copying ./user_db/test.ibd to /data/backups/mysql/innobackex_incre_2/user_db/test.ibd.delta
[01]       ...done
[01] Copying ./user_db/test2.ibd to/data/backups/mysql/innobackex_incre_2/user_db/test2.ibd.delta
[01]       ...done
[01] Copying ./user_db/t3.ibd to/data/backups/mysql/innobackex_incre_2/user_db/t3.ibd.delta
[01]       ...done
[01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/innobackex_incre_2/user_db/UC_USER.ibd.delta
[01]       ...done
[01] Copying ./user_db/t.ibd to/data/backups/mysql/innobackex_incre_2/user_db/t.ibd.delta
[01]       ...done
>> log scanned up to (423929601)
xtrabackup: Creating suspend file'/data/backups/mysql/innobackex_incre_2/xtrabackup_suspended_2' with pid'13694'

150319 02:51:16  innobackupex: Continuing after ibbackup hassuspended
150319 02:51:16  innobackupex: Executing FLUSH TABLES WITHREAD LOCK...
150319 02:51:16  innobackupex: All tables locked and flushedto disk

150319 02:51:16  innobackupex: Starting to backup non-InnoDBtables and files
innobackupex: in subdirectories of '/home/data/mysql/data/'
innobackupex: Backing up files'/home/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(53 files)
>> log scanned up to (423929601)
innobackupex: Backing up files'/home/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(74 files)
>> log scanned up to (423929601)
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest3.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest4.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest6.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest8.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest1.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/db.opt'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest2.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest7.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest5.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t_incre_2.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t3.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t4.frm'
innobackupex: Backing up file '/home/data/mysql/data//test/incre2.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/incre1.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t1.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/UC_USER.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t3.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/test.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/db.opt'
innobackupex: Backing up file'/home/data/mysql/data//user_db/test2.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t1.frm'
150319 02:51:17  innobackupex: Finished backing up non-InnoDBtables and files

150319 02:51:17  innobackupex: Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS...
150319 02:51:17  innobackupex: Waiting for log copying tofinish

xtrabackup: The latest check point (forincremental): '423929601'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423929601)

xtrabackup: Creating suspend file'/data/backups/mysql/innobackex_incre_2/xtrabackup_log_copied' with pid '13694'
xtrabackup: Transaction log of lsn(423929601) to (423929601) was copied.
150319 02:51:18  innobackupex: All tables unlocked

innobackupex: Backup created in directory'/data/backups/mysql/innobackex_incre_2'
innobackupex: MySQL binlog position:filename 'mysql-bin.000066', position 120
150319 02:51:19  innobackupex: Connection to database serverclosed
150319 02:51:19  innobackupex: completed OK!

 

 

7,innobackupex做增量苏醒

 

7.1先删除五次增量数据,用来查看验证恢复生机结果

 

mysql> delete from incre1;
Query OK, 1 row affected (0.05 sec)

mysql> delete from incre2;
Query OK, 1 row affected (0.01 sec)

mysql> select * from incre1;
Empty set (0.00 sec)

mysql> select * from incre2;
Empty set (0.00 sec)

 

mysql> use mysql;

Database changed

mysql> CREATE USER 'backuser'@'localhost' IDENTIFIED BY 'kkk123456';

Query OK, 0 rows affected (0.00 sec)

 

mysql> GRANT RELOAD, LOCK TABLES,PROCESS, REPLICATION CLIENT ON *.* TO 'backuser'@'localhost';

Query OK, 0 rows affected (0.00 sec)

 

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

 

mysql> 

7.2 开端做恢复生机,恢复生机全备份

备份命令如下:

innobackupex --user=backup--password="123456" --host=192.168.52.129 --apply-log/data/backups/mysql/innobackupex_full_20150319/2>/tmp/innobackex_restore_full.log

备份进度音信如下:

 

......
InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053

xtrabackup: starting shutdown withinnodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequencenumber 423921634
150319 03:06:57  innobackupex: completed OK!

 

 

 

7.3 基于全备份实行第一字增量备份的上升

备份命令:

innobackupex --defaults-file=/etc/my.cnf--user=backup --password="123456" --host=192.168.52.129 /data/backups/mysql/innobackupex_full_20150319/--incremental-dir=/data/backups/mysql/innobackex_incre_1 --apply-log2>/tmp/innobackex_restore_incre_1.log

备份进度音信如下:

 

……
xtrabackup: starting shutdown withinnodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequencenumber 423925452
innobackupex: Starting to copy non-InnoDBfiles in '/data/backups/mysql/innobackex_incre_1'
innobackupex: to the full backup directory'/data/backups/mysql/innobackupex_full_20150319'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/xtrabackup_info' to'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_info'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/xtrabackup_binlog_info' to'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_binlog_info'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_history.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_history.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_global_by_event_name.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_global_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/threads.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/threads.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_history_long.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_history_long.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/file_summary_by_instance.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/file_summary_by_instance.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_by_user_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_by_user_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_by_account_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_by_account_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_by_user_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_by_user_by_event_name.frm'
innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_current.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_current.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/table_lock_waits_summary_by_table.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/table_lock_waits_summary_by_table.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/socket_instances.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/socket_instances.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/setup_actors.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/setup_actors.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/table_io_waits_summary_by_table.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/table_io_waits_summary_by_table.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_summary_by_account_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_summary_by_account_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/cond_instances.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/cond_instances.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/users.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/users.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/db.opt' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/db.opt'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_by_instance.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_by_instance.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_summary_by_thread_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_summary_by_thread_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_current.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_current.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/objects_summary_global_by_type.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/objects_summary_global_by_type.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_by_thread_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_by_thread_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_by_host_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_by_host_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/socket_summary_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/socket_summary_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_history.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_history.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_history.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_history.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_by_thread_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_by_thread_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_summary_global_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_summary_global_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/setup_timers.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/setup_timers.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/hosts.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/hosts.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_summary_by_host_by_event_name.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_summary_by_host_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/host_cache.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/host_cache.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/setup_objects.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/setup_objects.frm'
innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/performance_schema/setup_consumers.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/setup_consumers.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/session_account_connect_attrs.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/session_account_connect_attrs.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_by_host_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_by_host_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/socket_summary_by_instance.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/socket_summary_by_instance.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_global_by_event_name.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_global_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_summary_by_user_by_event_name.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_summary_by_user_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/mutex_instances.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/mutex_instances.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/setup_instruments.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/setup_instruments.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_by_digest.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_by_digest.frm'
innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/performance_schema/rwlock_instances.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/rwlock_instances.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/file_summary_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/file_summary_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_by_account_by_event_name.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_by_account_by_event_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_current.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_current.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/session_connect_attrs.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/session_connect_attrs.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/performance_timers.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/performance_timers.frm'
innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_history_long.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_history_long.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/file_instances.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/file_instances.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_history_long.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_history_long.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/table_io_waits_summary_by_index_usage.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/table_io_waits_summary_by_index_usage.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/accounts.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/accounts.frm'
innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/event.MYI'to '/data/backups/mysql/innobackupex_full_20150319/mysql/event.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/columns_priv.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/columns_priv.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/db.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/db.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/user.MYI' to '/data/backups/mysql/innobackupex_full_20150319/mysql/user.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition_type.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition_type.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/procs_priv.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/procs_priv.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_topic.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_topic.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_name.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_name.MYI'
innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/columns_priv.MYI'to '/data/backups/mysql/innobackupex_full_20150319/mysql/columns_priv.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/slow_log.CSV' to'/data/backups/mysql/innobackupex_full_20150319/mysql/slow_log.CSV'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/func.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/func.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/db.frm' to '/data/backups/mysql/innobackupex_full_20150319/mysql/db.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/servers.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/servers.MYD'
innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/time_zone.MYI'to '/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proc.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proc.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/tables_priv.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/tables_priv.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/slave_worker_info.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/slave_worker_info.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/ndb_binlog_index.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/ndb_binlog_index.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/slow_log.CSM' to '/data/backups/mysql/innobackupex_full_20150319/mysql/slow_log.CSM'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/ndb_binlog_index.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/ndb_binlog_index.MYD'
innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/tables_priv.frm'to '/data/backups/mysql/innobackupex_full_20150319/mysql/tables_priv.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/event.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/event.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/func.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/func.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_keyword.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_keyword.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/procs_priv.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/procs_priv.MYI'
innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/slave_relay_log_info.frm'to'/data/backups/mysql/innobackupex_full_20150319/mysql/slave_relay_log_info.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_leap_second.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_leap_second.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/func.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/func.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_keyword.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_keyword.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/procs_priv.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/procs_priv.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_topic.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_topic.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/plugin.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/plugin.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/innodb_index_stats.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/innodb_index_stats.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_category.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_category.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition_type.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition_type.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/user.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/user.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/general_log.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/general_log.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_category.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_category.MYD'
innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/help_relation.frm'to '/data/backups/mysql/innobackupex_full_20150319/mysql/help_relation.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proxies_priv.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proxies_priv.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/tables_priv.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/tables_priv.MYD'
innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition_type.MYI'to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition_type.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/general_log.CSM' to'/data/backups/mysql/innobackupex_full_20150319/mysql/general_log.CSM'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proxies_priv.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proxies_priv.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/ndb_binlog_index.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/ndb_binlog_index.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/slave_master_info.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/slave_master_info.frm'
innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/help_relation.MYI'to '/data/backups/mysql/innobackupex_full_20150319/mysql/help_relation.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/db.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/db.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_leap_second.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_leap_second.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_leap_second.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_leap_second.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_name.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_name.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proc.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proc.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_relation.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_relation.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proxies_priv.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proxies_priv.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/servers.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/servers.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_keyword.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_keyword.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/servers.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/servers.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_category.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_category.MYI'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/innodb_table_stats.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/innodb_table_stats.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/slow_log.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/slow_log.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/user.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/user.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_name.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/plugin.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/plugin.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/columns_priv.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/columns_priv.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/plugin.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/plugin.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/general_log.CSV' to'/data/backups/mysql/innobackupex_full_20150319/mysql/general_log.CSV'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/event.MYD' to '/data/backups/mysql/innobackupex_full_20150319/mysql/event.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proc.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proc.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_topic.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_topic.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest3.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest3.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest4.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest4.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest6.frm' to '/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest6.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest8.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest8.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest1.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest1.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/db.opt' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/db.opt'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest2.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest2.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest7.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest7.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest5.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest5.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/test/t_incre_2.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/t_incre_2.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/test/t3.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/t3.frm'
innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/test/t4.frm'to '/data/backups/mysql/innobackupex_full_20150319/test/t4.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/test/incre1.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/incre1.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/test/t1.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/t1.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/UC_USER.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/UC_USER.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/t.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/t.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/t3.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/t3.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/test.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/test.frm'
innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/user_db/db.opt'to '/data/backups/mysql/innobackupex_full_20150319/user_db/db.opt'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/test2.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/test2.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/t1.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/t1.frm'
150319 03:12:51  innobackupex: completed OK!

 

 

 

7.4, 基于全备份和第贰次增量备份,苏醒第二遍增量备份

备份命令如下:

innobackupex --defaults-file=/etc/my.cnf--user=backup --password="123456" --host=192.168.52.129 /data/backups/mysql/innobackupex_full_20150319/--incremental-dir=/data/backups/mysql/innobackex_incre_2 --apply-log2>/tmp/innobackex_restore_incre_2.log

 

备份进程新闻如下:

 

……
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/user.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/user.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/time_zone_name.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_name.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/plugin.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/plugin.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/columns_priv.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/columns_priv.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/plugin.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/plugin.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/general_log.CSV' to'/data/backups/mysql/innobackupex_full_20150319/mysql/general_log.CSV'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/event.MYD' to '/data/backups/mysql/innobackupex_full_20150319/mysql/event.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/proc.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proc.MYD'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/help_topic.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_topic.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest3.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest3.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest4.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest4.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest6.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest6.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest8.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest8.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest1.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest1.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/db.opt' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/db.opt'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest2.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest2.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest7.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest7.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest5.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest5.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/test/t_incre_2.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/t_incre_2.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/test/t3.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/t3.frm'
innobackupex: Copying '/data/backups/mysql/innobackex_incre_2/test/t4.frm'to '/data/backups/mysql/innobackupex_full_20150319/test/t4.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/test/incre2.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/incre2.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/test/incre1.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/incre1.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/test/t1.frm' to '/data/backups/mysql/innobackupex_full_20150319/test/t1.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/UC_USER.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/UC_USER.frm'
innobackupex: Copying '/data/backups/mysql/innobackex_incre_2/user_db/t.frm'to '/data/backups/mysql/innobackupex_full_20150319/user_db/t.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/t3.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/t3.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/test.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/test.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/db.opt' to'/data/backups/mysql/innobackupex_full_20150319/user_db/db.opt'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/test2.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/test2.frm'
innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/t1.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/t1.frm'
150319 03:18:34  innobackupex: completed OK!
[[email protected] mysql]#

 

比如上边的权能相当不够的话,能够利用上边授权语句。

7.5 复苏整个数据库

停下数据库

service mysqld5612 stop

清空数据目录下具有文件

mkdir -p /tmp/mysqldatabak/

mv/home/data/mysql/data/* /tmp/mysqldatabak/

将卷土而来好的数码依照安顿文件的供给拷贝到相应目录

innobackupex --defaults-file=/etc/my.cnf--user=backup --password="123456" --host=192.168.52.129--defaults-file=/etc/my.cnf --copy-back/data/backups/mysql/innobackupex_full_20150319/ 2>/tmp/innobackex_incre_restore_end.log

予以mysql账号权限

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

启动mysql服务

service mysqld5612 start

PS:--copy-back进度如下:

 

innobackupex: Starting to copy InnoDB undotablespaces
innobackupex: in'/data/backups/mysql/innobackupex_full_20150319'
innobackupex: back to'/home/data/mysql/data'

innobackupex: Starting to copy InnoDB logfiles
innobackupex: in'/data/backups/mysql/innobackupex_full_20150319'
innobackupex: back to original InnoDB logdirectory '/home/data/mysql/data'
innobackupex: Copying '/data/backups/mysql/innobackupex_full_20150319/ib_logfile1'to '/home/data/mysql/data/ib_logfile1'
innobackupex: Copying'/data/backups/mysql/innobackupex_full_20150319/ib_logfile2' to'/home/data/mysql/data/ib_logfile2'
innobackupex: Copying'/data/backups/mysql/innobackupex_full_20150319/ib_logfile0' to'/home/data/mysql/data/ib_logfile0'
innobackupex: Finished copying back files.

150319 03:23:49  innobackupex: completed OK!

 

 

7.6 check已经被delete的数据

签到mysql分界面,查看表incre1和表incre2,数据现已平复,如下所示:

 

mysql> select * from incre1;
+---------------+----+
| incre1        | id |
+---------------+----+
| incre1 table  | a  |
+---------------+----+
1 row in set (0.04 sec)

mysql> select * from incre2;
+--------+----+
| a     | id |
+--------+----+
| incre2 | 3 |
+--------+----+
1 row in set (0.07 sec)

mysql>

 

1,创建mysql备份顾客 mysql -uroot --password= -eCREATE USEPRADO [email protected]% IDENTIFIED BY 123456; mysql -uroot --p...

 

GRANT RELOAD, LOCK TABLES,PROCESS, REPLICATION CLIENT, CREATE TABLESPACE,SUPER ON *.* TO 'backuser'@'localhost';

 

 

系统账号授权相关目录的操作权限。如下所示:

 

 

[root@DB-Server ~]# chown -R mysql:mysql /data

[root@DB-Server ~]# su - mysql

-bash-3.2$ 

 

 

 

完整备份

 

 

 常用的共同体备份(全备)命令如下所示

 

innobackupex  --user=backuser --password=kkk123456  /u02/mysql_backup/full/

innobackupex  --user=root --password=123456  /u02/mysql_backup/full/

innobackupex --defaults-file=/usr/my.cnf --user=backuser --password=kkk123456  /u02/mysql_backup/full

innobackupex --defaults-file=/usr/my.cnf --socket=/var/lib/mysql/mysql.sock --user=backuser --password=kkk123456  /u02/mysql_backup/full

参数--no-timestamp 表示不生成带时间戳的目录

innobackupex  --user=backuser --password=kkk123456  /u02/mysql_backup/full/ --no-timestamp

 

 注意:要是你钦赐了参数--defaults-file,那么此参数就务须放在第叁个,不然就能够报“--defaults-file must be specified first on the command line”

 

 

 我们先在测量试验数据库MyDB成立二个测量试验表,插入几条数据,后边做还原测量试验表明使用。

 

mysql> use MyDB;

Database changed

 

mysql> create table test(id  int primary key);

Query OK, 0 rows affected (0.13 sec)

 

mysql> insert into test

    -> values(1000);

Query OK, 1 row affected (0.01 sec)

 

mysql> insert into test

    -> values(2000);

Query OK, 1 row affected (0.01 sec)

 

mysql> insert into test

    -> values(3000);

Query OK, 1 row affected (0.01 sec)

 

mysql> 

 

 大家采纳上边发号施令对MySQL做了贰个安然无事备份,那么暗中同意就能够在目录/u02/mysql_backup/full/下生成一个日子时间格式的全备文件2017-07-19_17-22-45

 

   $ innobackupex --defaults-file=/usr/my.cnf --user=backuser --password=kkk123456  /u02/mysql_backup/full

 

 

图片 1

 

 

 那么备份出来的文件以致内容是何许的呢? 如下所示,你能够检查xtrabackup_checkpoints、xtrabackup_binlog_info、xtrabackup_info多个文本精通备份的一部分详细音信,举例备份类型、是不是收缩等等。

 

 

[root@DB-Server 2017-07-19_17-39-56]# ls -lrt

total 12380

drwxr-x--- 2 mysql mysql     4096 Jul 19 17:39 test

drwxr-x--- 2 mysql mysql     4096 Jul 19 17:39 performance_schema

drwxr-x--- 2 mysql mysql     4096 Jul 19 17:39 mysql

drwxr-x--- 2 mysql mysql     4096 Jul 19 17:39 MyDB

-rw-r----- 1 mysql mysql 12582912 Jul 19 17:39 ibdata1

-rw-r----- 1 mysql mysql     2560 Jul 19 17:39 xtrabackup_logfile

-rw-r----- 1 mysql mysql      545 Jul 19 17:39 xtrabackup_info

-rw-r----- 1 mysql mysql      113 Jul 19 17:39 xtrabackup_checkpoints

-rw-r----- 1 mysql mysql       25 Jul 19 17:39 xtrabackup_binlog_info

-rw-r----- 1 mysql mysql      418 Jul 19 17:39 backup-my.cnf

[root@DB-Server 2017-07-19_17-39-56]# more xtrabackup_checkpoints 

backup_type = full-backuped

from_lsn = 0

to_lsn = 1637691

last_lsn = 1637691

compact = 0

recover_binlog_info = 0

[root@DB-Server 2017-07-19_17-39-56]# more xtrabackup_binlog_info 

DB-Server-bin.000001    871

[root@DB-Server 2017-07-19_17-39-56]# more xtrabackup_info 

uuid = cfb5c2d8-6cca-11e7-8113-b083fe5532e5

name = 

tool_name = innobackupex

tool_command = --defaults-file=/usr/my.cnf --user=backuser --password=... /u02/mysql_backup/full

tool_version = 2.4.6

ibbackup_version = 2.4.6

server_version = 5.6.20-enterprise-commercial-advanced-log

start_time = 2017-07-19 17:39:56

end_time = 2017-07-19 17:39:58

lock_time = 0

binlog_pos = filename 'DB-Server-bin.000001', position '871'

innodb_from_lsn = 0

innodb_to_lsn = 1637691

partial = N

incremental = N

format = file

compact = N

compressed = N

encrypted = N

 

图片 2

 

 

 xtrabackup_checkpoints     : 备份类型消息(完全备份或增量备份)、LSN(日志连串号)范围音讯、备份是不是压缩

 xtrabackup_binlog_info     : 二进制日志消息,MySQL当前正值使用的二进制日志文件及至备份这一阵子完成二进制日志事件的地方。若无开启二进制日志,那么就不会有xtrabackup_binlog_info这一个文件。

 xtrabackup_info         : xtrabackup工具的版本消息、详细命令、参数以致备份的上马、截止时间。乃至备份详细音讯等。

 xtrabackup_logfile      :

 backup-my.cnf           : 备份命令用到的配备选项音信。

 

 

哪些行使全备苏醒过来呢?  如下步骤所示:

 

 

Step 1:Prepare the Backup。

 

 

 

innobackupex  --defaults-file=/usr/my.cnf   --apply-log  /u02/mysql_backup/full/2017-07-19_17-39-56/

 

 

 

Step 2:关闭MySQL服务,备份数据目录或删除数据目录

 

 

 2.1 :  /etc/init.d/mysql stop

 

 2.2 : mv /data/mysql/   /data/mysql_20170719_bak

 

 

Step 3: 复苏数据,将备份数据文件拷贝到数据目录

 

 

 

 innobackupex  --defaults-file=/usr/my.cnf  --copy-back  /u02/mysql_backup/full/2017-07-19_17-39-56/

 

 若是试行下面命令是在mysql顾客下,而且有权力创制对应目录,那么就无需做其他操作,倘诺是root账号,那么就务须修改对应目录的Owner,不然运行MySQL服务会报错。

 

    chown -R mysql:mysql /data/mysql/*

 

 注意,假若这里权限未有拍卖好,有时候会油但是生一些莫明其妙的一无可取,举个例子,设置文件权限制时间,由于地点脚本处理的是/data/mysql下边包车型客车文书,没有管理/data/mysql那个目录的权力,运营MySQL的时候就报这么些错误

 

[root@DB-Server data]# ls -lrt

total 40

drwxr-xr-x 6 mysql mysql 4096 Jul 21  2017 mysql_20170719_bak

drwxr-x--- 8 root  root  4096 Jun 25 12:09 mysql

[root@DB-Server data]# service mysql start

Starting MySQL.. ERROR! The server quit without updating PID file (/data/mysql/DB-Server.localdomain.pid).

 

[root@DB-Server data]# chown -R mysql:mysql /data/mysql

[root@DB-Server data]# service mysql start

Starting MySQL... SUCCESS!

 

 

 

Step 4: 测量试验数据的完整性:

 

[root@DB-Server ~]# service mysql start

Starting MySQL.....[  OK  ]

[root@DB-Server ~]# mysql -u root -p

Enter password: 

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

Your MySQL connection id is 1

Server version: 5.6.20-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

 

Copyright (c) 2000, 2014, 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 MyDB;

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   |

+------+

| 1000 |

| 2000 |

| 3000 |

+------+

3 rows in set (0.00 sec)

 

 

XtraBackup增量备份

 

 

 在演说Xtrabackup增量备份前,我们先来拜谒上面这几个增量备份,你能看到其难题呢?那么些备份有甚不妥呢? 当然在做增量备份前,都有对数据库实行了一些测量检验操作。

 

 

完全备份

 

innobackupex --defaults-file=/usr/my.cnf --user=backuser --password=kkk123456  /u02/mysql_backup/full

 

增量备份1

 

innobackupex --defaults-file=/usr/my.cnf --user=root --password=123456 --incremental  --incremental-basedir=/u02/mysql_backup/full/2017-07-24_09-38-32/  /u02/mysql_backup/incr/

 

增量备份2

 

innobackupex --defaults-file=/usr/my.cnf --user=root --password=123456 --incremental  --incremental-basedir=/u02/mysql_backup/full/2017-07-24_09-32-22/   /u02/mysql_backup/incr/

 

 

实在首先次完整备份时,生成了备份目录2017-07-24_09-38-32; 第贰遍增量备份时,生成了增量备份目录2017-07-24_09-40-11,关键在于第4回增量备份,--incremental-basedir那么些参数的值不该是首先次成功备份的目录,而应是增量备份1的备份目录2017-07-24_09-40-11,如下所示,如若选拔了不当的参数,增量备份2其实包含了增量备份1的转移部分。

 

 

[root@DB-Server 2017-07-24_09-40-11]# more xtrabackup_info 

uuid = 9f916d24-7075-11e7-8f2e-b083fe5532e5

name = 

tool_name = innobackupex

tool_command = --defaults-file=/usr/my.cnf --user=root --password=... --incremental --incremental-basedir=/u02/mysql_backup/full/2017-07-24_09-38-32/ /u02/mysql_backup/incr/

tool_version = 2.4.6

ibbackup_version = 2.4.6

server_version = 5.6.20-enterprise-commercial-advanced

start_time = 2017-07-24 09:40:12

end_time = 2017-07-24 09:40:14

lock_time = 0

binlog_pos = 

innodb_from_lsn = 1650510

innodb_to_lsn = 1660277

partial = N

incremental = Y

format = file

compact = N

compressed = N

encrypted = N

[root@DB-Server 2017-07-24_09-40-11]# cd ../2017-07-24_09-40-25/

[root@DB-Server 2017-07-24_09-40-25]# more xtrabackup_info 

uuid = a790b642-7075-11e7-8f2e-b083fe5532e5

name = 

tool_name = innobackupex

tool_command = --defaults-file=/usr/my.cnf --user=root --password=... --incremental --incremental-basedir=/u02/mysql_backup/full/2017-07-24_09-38-32/ /u02/mysql_backup/incr/

tool_version = 2.4.6

ibbackup_version = 2.4.6

server_version = 5.6.20-enterprise-commercial-advanced

start_time = 2017-07-24 09:40:25

end_time = 2017-07-24 09:40:28

lock_time = 0

binlog_pos = 

innodb_from_lsn = 1650510

innodb_to_lsn = 1664130

partial = N

incremental = Y

format = file

compact = N

compressed = N

encrypted = N

[root@DB-Server 2017-07-24_09-40-25]# 

 

增量备份注意事项

 

1:--incremental-basedir   

 

    第叁遍增量备份,--incremental-basedir分明是全备目录,第叁次增量备份,--incremental-basedir为第叁回增量备份目录,就那样类推!

 

2:增量备份仅能使用于InnoDB或XtraDB表,对于MyISAM表来讲,实践增量备份时其实举行的是一心备份。

 

 

3:应用增量备份的时候只得依照备份的相继来选用。假若选择顺序错误,那么备份就不可用。假使不只怕鲜明顺序,能够查看xtrabackup-checkpoints来分明顺序。

 

 

那么大家来用案例测量试验一下增量备份的复原,具体步骤如下所示(这里大家使用root账号来)

 

 

Step 1: 完整备份

 

mysql> use MyDB;

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> create table test (id int, name varchar(12));

Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into test values(1000, 'kerry');

Query OK, 1 row affected (0.00 sec)

 

mysql> 

 

实施总体备份

 

innobackupex --default-files=/usr/my.cnf   --user=root --password=123456  /u02/mysql_backup/full/

 

 

图片 3

 

 

Step 2: 增量备份1

 

mysql> insert into test values(2000, 'ken');

Query OK, 1 row affected (0.00 sec)

 

在意--incremental-basedir参数应该依照实际情况明确

 

innobackupex  --defaults-file=/usr/my.cnf  --user=root --password=123456  --incremental --incremental-basedir=/u02/mysql_backup/full/2017-07-24_16-15-59/ /u02/mysql_backup/incr/

 

图片 4

 

Step 3:增量备份2:

 

 

mysql> create table big_table (id  int);

Query OK, 0 rows affected (0.13 sec)

 

mysql> insert into big_table values(10000);

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into big_table values(20000);

Query OK, 1 row affected (0.00 sec)

 

mysql> 

 

只顾 --incremental-basedir参数应该依靠实际情状鲜明

 

innobackupex  --defaults-file=/usr/my.cnf  --user=root --password=123456  --incremental --incremental-basedir=/u02/mysql_backup/incr/2017-07-24_16-17-39/ /u02/mysql_backup/incr/

 

 

图片 5

 

 

介怀:Xtrabackup增量备份时,必要设置参数--incremental-basedir 其实这么些参数不是必须的。能够用--incremental-lsn来代替。

 

 

完全备份后,检查备份目录下的xtrabackup_checkpoints文件,找到to_lsn,然后采取上边脚本做增量备份。

 

innobackupex  --user=backuser --password=kkk123456  /u02/mysql_backup/full/

 

[root@DB-Server 2017-07-25_12-49-57]# more xtrabackup_checkpoints

backup_type = full-backuped

from_lsn = 0

to_lsn = 1733142

last_lsn = 1733142

compact = 0

recover_binlog_info = 0

 

innobackupex --user=backuser  --password=kkk123456  --incremental --incremental-lsn=1733142 /u02/mysql_backup/incr/

 

 

然后,我们先删除或DROP掉一部分数额,模拟数据被误删的状态,然后还原测量检验注明。 如下所示:

 

 

mysql> drop table test;

Query OK, 0 rows affected (0.00 sec)

 

mysql> delete from big_table where id=10000;

Query OK, 1 row affected (0.01 sec)

 

mysql> 

 

 

Step 1: 还原完全备份

 

innobackupex --defaults-file=/usr/my.cnf  --apply-log --redo-only /u02/mysql_backup/full/2017-07-24_16-15-59/

 

图片 6

 

小心要是继续必得使用增量备份,则必需抬高--redo-only. 不然继续不能接纳增量备份。这么些只要对SQL Server备份还原熟练的话,原理也是这般。

 

--redo-only

 

If you replay the committed transactions and rollback the uncommitted ones on the base backup, you will not be able

to add the incremental ones. If you do this on an incremental one, you won’t be able to add data from that moment

and the remaining increments。

 

Note: --redo-only should be used when merging all incrementals except the last one. That’s why the previous

line doesn’t contain the --redo-only option. Even if the --redo-only was used on the last step, backup would

still be consistent but in that case server would perform the rollback phase.

 

日常来讲截图所示,借使在平复全备时从没采纳参数--redo-only ,前边应用增量备份时就能够遇上上边错误。独有当最终多个增量备份时,才无需--redo-only

 

图片 7

 

 

 

Step 2:增量备份还原

 

 innobackupex --apply-log --redo-only --incremental /u02/mysql_backup/full/2017-07-24_16-15-59/

本文由澳门在线威尼斯官方发布于电脑数据库,转载请注明出处:备份还原篇,MySQL利用xtrabackup举办增量备份详细

关键词:

上一篇:【威尼斯澳门在线】SQL必备知识点,SQL卓越总括

下一篇:百万级数据库SQL优化大总结,关于iOS数据库大数