Shulou(Shulou.com)05/31 Report--

This article introduces you how to carry out XtraBackup actual combat in MySQL, the content is very detailed, interested friends can refer to, hope to be helpful to you.

1. Use xtrabckup to fully prepare 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 (x86x64) (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.

two。 Use innobackupex to fully prepare 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 InnoDB Backup Utility 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 years 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, xtrabackupply suspendedcards 2'

Xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86x64) (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, xtrabackupply suspendedcards 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, xtrabackupply logically 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. Using innobackupex to make incremental backups of mysql

-- create test data

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), (2)), (3))

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 InnoDB Backup Utility 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 Compact'

Innobackupex: Waiting for ibbackup (pid=21280) to suspend

Innobackupex: Suspend file'/ mvnobss/users/mysql1/bak/incr/2016-01-20, 15-57-54, xtrabackupply suspendedcards 2'

Xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86x64) (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, xtrabackupply suspendedcards 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, xtrabackupply logically copided '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. Delete the database and simulate the failure

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. Restore backup

-- restore full (pay attention to the parameter redo-only, which needs to be added because there are other incremental backups to deal with)

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 InnoDB Backup Utility 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 (x86x64) (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!

-- restore incremental backup (note that the incremental-dir parameter must be followed by an absolute path, otherwise an error will be reported for recovery)

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 /

-- perform a restore operation again (to create a dedicated log file for 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 InnoDB Backup Utility 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 (x86x64) (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 logfile. / ib_logfile101 size to 256 MB

InnoDB: Progress in MB: 100 200

InnoDB: Setting logfile. / ib_logfile1 size to 256 MB

InnoDB: Progress in MB: 100 200

InnoDB: Renaming logfile. / ib_logfile101 to. / ib_logfile0

InnoDB: New log files created, LSN=63565599

InnoDB: Highest supported file format is Barracuda.

InnoDB: 128rollback 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 (x86x64) (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: 128rollback 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!

-- copy files to the datadir directory

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

-- start mysql

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

-- verify that the data is restored

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)

So far, the data has been restored successfully.

On how to carry out XtraBackup in MySQL to share the actual combat here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.