ITPub博客

首页 > 数据库 > MySQL > MySQL入门学习之——实战XtraBackup

MySQL入门学习之——实战XtraBackup

原创 MySQL 作者:wxjzqym 时间:2016-01-20 17:26:08 0 删除 编辑

1.使用xtrabckup对mysql进行全备
mysql1@hdh-votst2:/mvnobss/users/mysql1>xtrabackup  --defaults-file=mysql/bin/my.cnf --target-dir=bak/ --backup
xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mvnobss/users/mysql1/mysql/mydata
xtrabackup: open files limit requested 10240, set to 1048576
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 268435456
>> log scanned up to (63557721)
xtrabackup: Generating a list of tablespaces
[01] Copying ./ibdata1 to /mvnobss/users/mysql1/bak/ibdata1
>> log scanned up to (63557721)
[01]        ...done
[01] Copying ./test/t1.ibd to /mvnobss/users/mysql1/bak/test/t1.ibd
[01]        ...done
[01] Copying ./test/t2.ibd to /mvnobss/users/mysql1/bak/test/t2.ibd
[01]        ...done
[01] Copying ./test/t3.ibd to /mvnobss/users/mysql1/bak/test/t3.ibd
[01]        ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /mvnobss/users/mysql1/bak/mysql/innodb_index_stats.ibd
[01]        ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /mvnobss/users/mysql1/bak/mysql/slave_relay_log_info.ibd
[01]        ...done
[01] Copying ./mysql/slave_master_info.ibd to /mvnobss/users/mysql1/bak/mysql/slave_master_info.ibd
[01]        ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /mvnobss/users/mysql1/bak/mysql/innodb_table_stats.ibd
[01]        ...done
[01] Copying ./mysql/slave_worker_info.ibd to /mvnobss/users/mysql1/bak/mysql/slave_worker_info.ibd
[01]        ...done
>> log scanned up to (63557721)
xtrabackup: The latest check point (for incremental): '63557721'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (63557721)

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



2.使用innobackupex对mysql进行全备
mysql1@hdh-votst2:/mvnobss/users/mysql1>innobackupex --defaults-file=mysql/bin/my.cnf --user=root --password=123 bak/

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

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

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

160120 15:45:52  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=mysql/bin/my.cnf;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
160120 15:45:52  innobackupex: Connected to MySQL server
160120 15:45:52  innobackupex: Executing a version check against the server...
160120 15:45:54  innobackupex: Done.
160120 15:45:54  innobackupex: Starting the backup operation

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

innobackupex:  Using server version 5.6.21-70.1-log

innobackupex: Created backup directory /mvnobss/users/mysql1/bak/2016-01-20_15-45-54

160120 15:45:54  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="mysql/bin/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/mvnobss/users/mysql1/bak/2016-01-20_15-45-54 --innodb_data_file_path="ibdata1:12M:autoextend" --tmpdir=/tmp --extra-lsndir='/tmp'
innobackupex: Waiting for ibbackup (pid=21045) to suspend
innobackupex: Suspend file '/mvnobss/users/mysql1/bak/2016-01-20_15-45-54/xtrabackup_suspended_2'

xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mvnobss/users/mysql1/mysql/mydata
xtrabackup: open files limit requested 10240, set to 1048576
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 268435456
>> log scanned up to (63557721)
xtrabackup: Generating a list of tablespaces
[01] Copying ./ibdata1 to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/ibdata1
>> log scanned up to (63557721)
[01]        ...done
[01] Copying ./test/t1.ibd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/test/t1.ibd
[01]        ...done
[01] Copying ./test/t2.ibd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/test/t2.ibd
[01]        ...done
[01] Copying ./test/t3.ibd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/test/t3.ibd
[01]        ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/mysql/innodb_index_stats.ibd
[01]        ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/mysql/slave_relay_log_info.ibd
[01]        ...done
[01] Copying ./mysql/slave_master_info.ibd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/mysql/slave_master_info.ibd
[01]        ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/mysql/innodb_table_stats.ibd
[01]        ...done
[01] Copying ./mysql/slave_worker_info.ibd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/mysql/slave_worker_info.ibd
[01]        ...done
>> log scanned up to (63557721)
xtrabackup: Creating suspend file '/mvnobss/users/mysql1/bak/2016-01-20_15-45-54/xtrabackup_suspended_2' with pid '21045'

160120 15:45:57  innobackupex: Continuing after ibbackup has suspended
160120 15:45:57  innobackupex: Executing LOCK TABLES FOR BACKUP...
160120 15:45:57  innobackupex: Backup tables lock acquired

160120 15:45:57  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/mvnobss/users/mysql1/mysql/mydata/'
innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/db.opt'
innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/t3.frm'
innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/t1.frm'
innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/t2.frm'
innobackupex: Backing up files '/mvnobss/users/mysql1/mysql/mydata//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
>> log scanned up to (63557721)
innobackupex: Backing up files '/mvnobss/users/mysql1/mysql/mydata//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
>> log scanned up to (63557721)
160120 15:45:58  innobackupex: Finished backing up non-InnoDB tables and files

160120 15:45:58  innobackupex: Executing LOCK BINLOG FOR BACKUP...
160120 15:45:58  innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
160120 15:45:58  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '63557721'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (63557721)

xtrabackup: Creating suspend file '/mvnobss/users/mysql1/bak/2016-01-20_15-45-54/xtrabackup_log_copied' with pid '21045'
xtrabackup: Transaction log of lsn (63557721) to (63557721) was copied.
160120 15:45:59  innobackupex: Executing UNLOCK BINLOG
160120 15:45:59  innobackupex: Executing UNLOCK TABLES
160120 15:45:59  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/mvnobss/users/mysql1/bak/2016-01-20_15-45-54'
innobackupex: MySQL binlog position: filename 'mysql-bin-yt-votst2-3306.000009', position 3396
160120 15:45:59  innobackupex: Connection to database server closed
160120 15:45:59  innobackupex: completed OK!



3.使用innobackupex对mysql进行增量备份
--创建测试数据
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table t4(id int,name varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t4 values(1,'tl'),(2,'lj'),(3,'lx');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t4;
+------+------+
| id   | name |
+------+------+
|    1 | tl   |
|    2 | lj   |
|    3 | lx   |
+------+------+
3 rows in set (0.00 sec)


mysql1@hdh-votst2:/mvnobss/users/mysql1>innobackupex --defaults-file=mysql/bin/my.cnf --user=root --password=123 \
> --incremental --incremental-basedir=bak/2016-01-20_15-45-54/ bak/incr/

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

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

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

160120 15:57:54  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=mysql/bin/my.cnf;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
160120 15:57:54  innobackupex: Connected to MySQL server
160120 15:57:54  innobackupex: Executing a version check against the server...
160120 15:57:54  innobackupex: Done.
160120 15:57:54  innobackupex: Starting the backup operation

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

innobackupex:  Using server version 5.6.21-70.1-log

innobackupex: Created backup directory /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54

160120 15:57:54  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="mysql/bin/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54 --innodb_data_file_path="ibdata1:12M:autoextend" --tmpdir=/tmp --extra-lsndir='/tmp' --incremental-basedir='bak/2016-01-20_15-45-54/'
innobackupex: Waiting for ibbackup (pid=21280) to suspend
innobackupex: Suspend file '/mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/xtrabackup_suspended_2'

xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 63557721 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mvnobss/users/mysql1/mysql/mydata
xtrabackup: open files limit requested 10240, set to 1048576
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 268435456
>> log scanned up to (63564362)
xtrabackup: Generating a list of tablespaces
xtrabackup: using the full scan for incremental backup
[01] Copying ./ibdata1 to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/ibdata1.delta
[01]        ...done
[01] Copying ./test/t1.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/test/t1.ibd.delta
[01]        ...done
>> log scanned up to (63564362)
[01] Copying ./test/t4.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/test/t4.ibd.delta
[01]        ...done
[01] Copying ./test/t2.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/test/t2.ibd.delta
[01]        ...done
[01] Copying ./test/t3.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/test/t3.ibd.delta
[01]        ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/mysql/innodb_index_stats.ibd.delta
[01]        ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/mysql/slave_relay_log_info.ibd.delta
[01]        ...done
[01] Copying ./mysql/slave_master_info.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/mysql/slave_master_info.ibd.delta
[01]        ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/mysql/innodb_table_stats.ibd.delta
[01]        ...done
[01] Copying ./mysql/slave_worker_info.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/mysql/slave_worker_info.ibd.delta
[01]        ...done
>> log scanned up to (63564362)
xtrabackup: Creating suspend file '/mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/xtrabackup_suspended_2' with pid '21280'

160120 15:57:57  innobackupex: Continuing after ibbackup has suspended
160120 15:57:57  innobackupex: Executing LOCK TABLES FOR BACKUP...
160120 15:57:57  innobackupex: Backup tables lock acquired

160120 15:57:57  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/mvnobss/users/mysql1/mysql/mydata/'
innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/db.opt'
innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/t4.frm'
innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/t3.frm'
innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/t1.frm'
innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/t2.frm'
innobackupex: Backing up files '/mvnobss/users/mysql1/mysql/mydata//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
>> log scanned up to (63564362)
innobackupex: Backing up files '/mvnobss/users/mysql1/mysql/mydata//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
>> log scanned up to (63564362)
160120 15:57:58  innobackupex: Finished backing up non-InnoDB tables and files

160120 15:57:58  innobackupex: Executing LOCK BINLOG FOR BACKUP...
160120 15:57:58  innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
160120 15:57:58  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '63564362'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (63564362)

xtrabackup: Creating suspend file '/mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/xtrabackup_log_copied' with pid '21280'
xtrabackup: Transaction log of lsn (63564362) to (63564362) was copied.
160120 15:57:59  innobackupex: Executing UNLOCK BINLOG
160120 15:57:59  innobackupex: Executing UNLOCK TABLES
160120 15:57:59  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54'
innobackupex: MySQL binlog position: filename 'mysql-bin-yt-votst2-3306.000009', position 3720
160120 15:57:59  innobackupex: Connection to database server closed
160120 15:57:59  innobackupex: completed OK!



4.删除数据库,模拟故障

mysql1@hdh-votst2:/mvnobss/users/mysql1/mysql/mydata>pwd
/mvnobss/users/mysql1/mysql/mydata
mysql1@hdh-votst2:/mvnobss/users/mysql1/mysql/mydata>rm -rf *

mysql1@hdh-votst2:/mvnobss/users/mysql1/mysql/mydata>mysql -uroot -p123
Warning: Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/mvnobss/users/mysql1/mysql/mydata/mysqld-yt-votst2-3306.sock' (2)


5.恢复备份
--恢复全备(注意redo-only这个参数,因为还有其他增量备份要处理,所以这个参数需要加上)
mysql1@hdh-votst2:/mvnobss/users/mysql1>innobackupex --defaults-file=mysql/bin/my.cnf --apply-log --redo-only bak/2016-01-20_15-45-54/

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

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

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

160120 16:52:15  innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".


160120 16:52:15  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/mvnobss/users/mysql1/bak/2016-01-20_15-45-54/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/mvnobss/users/mysql1/bak/2016-01-20_15-45-54 --apply-log-only

xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: cd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(63557721)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
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 buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 57998804 and 57998804 in ibdata files do not match the log sequence number 63557721 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 1865, file name mysql-bin-yt-votst2-3306.000009

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 1865, file name mysql-bin-yt-votst2-3306.000009

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 63557721
160120 16:52:16  innobackupex: completed OK!


--恢复增量备份(注意这里incremental-dir参数后面必须是绝对路径,否则恢复报错)

mysql1@hdh-votst2:/mvnobss/users/mysql1>innobackupex --defaults-file=mysql/bin/my.cnf --apply-log --incremental-dir=/mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54 bak/2016-01-20_15-45-54/


--再次执行一次恢复操作(为了创建innodb的专用日志文件)
mysql1@hdh-votst2:/mvnobss/users/mysql1>innobackupex --defaults-file=mysql/bin/my.cnf --apply-log bak/2016-01-20_15-45-54/

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

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

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

160120 17:03:55  innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".


160120 17:03:55  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/mvnobss/users/mysql1/bak/2016-01-20_15-45-54/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/mvnobss/users/mysql1/bak/2016-01-20_15-45-54

xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: cd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 268435456
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 268435456
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
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 buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 256 MB
InnoDB: Progress in MB: 100 200
InnoDB: Setting log file ./ib_logfile1 size to 256 MB
InnoDB: Progress in MB: 100 200
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=63565599
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.22 started; log sequence number 63565836

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 3720, file name mysql-bin-yt-votst2-3306.000009

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 63567481

160120 17:04:00  innobackupex: Restarting xtrabackup with command: xtrabackup  --defaults-file="/mvnobss/users/mysql1/bak/2016-01-20_15-45-54/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/mvnobss/users/mysql1/bak/2016-01-20_15-45-54
for creating ib_logfile*

xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: cd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 268435456
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 268435456
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
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 buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.22 started; log sequence number 63567481

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 3720, file name mysql-bin-yt-votst2-3306.000009

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 63569162
160120 17:04:02  innobackupex: completed OK!

--拷贝文件到datadir目录
mysql1@hdh-votst2:/mvnobss/users/mysql1>innobackupex --defaults-file=mysql/bin/my.cnf --copy-back bak/2016-01-20_15-45-54/

mysql1@hdh-votst2:/mvnobss/users/mysql1>ls -l mysql/mydata/
total 602132
-rw-rw-r-- 1 mysql1 mysql  79691776 2016-01-20 17:10 ibdata1
-rw-rw-r-- 1 mysql1 mysql 268435456 2016-01-20 17:10 ib_logfile0
-rw-rw-r-- 1 mysql1 mysql 268435456 2016-01-20 17:10 ib_logfile1
drwxrwxr-x 2 mysql1 mysql      4096 2016-01-20 17:10 mysql
drwxrwxr-x 2 mysql1 mysql      4096 2016-01-20 17:10 performance_schema
drwxrwxr-x 2 mysql1 mysql      4096 2016-01-20 17:10 test
-rw-rw-r-- 1 mysql1 mysql        37 2016-01-20 17:10 xtrabackup_binlog_pos_innodb
-rw-rw-r-- 1 mysql1 mysql       675 2016-01-20 17:10 xtrabackup_info


--启动mysql

mysql1@hdh-votst2:/mvnobss/users/mysql1>mysql/bin/Start_Mysql.sh


--验证数据是否恢复

mysql1@hdh-votst2:/mvnobss/users/mysql1>mysql -uroot -p123 test
Warning: Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.21-70.1-log Percona Server (GPL), Release 70.1, Revision 698

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
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> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
| t3             |
| t4             |
+----------------+
4 rows in set (0.00 sec)

mysql> select * from t4;
+------+------+
| id   | name |
+------+------+
|    1 | tl   |
|    2 | lj   |
|    3 | lx   |
+------+------+
3 rows in set (0.00 sec)

至此数据恢复成功。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20801486/viewspace-1979709/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-15

  • 博文量
    100
  • 访问量
    470104