MySQL中如何进行XtraBackup实战

这篇文章给大家介绍MySQL中如何进行XtraBackup实战，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

创新互联坚持“要么做到，要么别承诺”的工作理念，服务领域包括：成都网站制作、网站设计、外贸网站建设、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的临翔网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！

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/

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/

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/

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/

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

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

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)

至此数据恢复成功。

关于MySQL中如何进行XtraBackup实战就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

标题名称：MySQL中如何进行XtraBackup实战
当前URL：http://pwwzsj.com/article/ghpeop.html

平武建站

MySQL中如何进行XtraBackup实战

其他资讯