投稿
  1. 云编程开发首页
  2. 数据库

MySQL中如何进行XtraBackup实战

373405967 • • 数据库 • 阅读 166

这篇文章给大家介绍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/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/p160120 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 operationIMPORTANT: 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-loginnobackupex: Created backup directory /mvnobss/users/mysql1/bak/2016-01-20_15-45-54160120 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 acquired160120 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 files160120 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 finishxtrabackup: 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 unlockedinnobackupex: 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 -ADatabase 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: 0mysql> 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/p160120 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 operationIMPORTANT: 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-loginnobackupex: Created backup directory /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54160120 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 acquired160120 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 files160120 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 finishxtrabackup: 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 unlockedinnobackupex: 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/p160120 16:52:15 innobackupex: Starting the apply-log operationIMPORTANT: 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-onlyxtrabackup 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.000009xtrabackup: 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/p160120 17:03:55 innobackupex: Starting the apply-log operationIMPORTANT: 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-54xtrabackup 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.000009xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown…
InnoDB: Shutdown completed; log sequence number 63567481160120 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.000009xtrabackup: 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 feat开发云主机域名ure to get a quicker startup with -AWelcome 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 698Copyright (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)至此数据恢复成功。关于MySQL中如何进行XtraBackup实战就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

相关推荐: mysql5.6.8源码安装的示例分析

小编给大家分享一下mysql5.6.8源码安装的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！内核：开始安装：创建mysql用户组：创建mysql安装目录和数据库文件存放目录…

免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。

赞 (0)
打赏 微信扫一扫 微信扫一扫
373405967373405967
0 0
上一篇 06/28 08:54
下一篇 06/28 08:54

相关推荐