MySQL入門學習之——實戰XtraBackup
1.使用xtrabckup對mysql進行全備
--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進行全備
--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:
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)
--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:
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.刪除資料庫，模擬故障
/mvnobss/users/mysql1/mysql/mydata
-rf *
-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這個引數，因為還有其他增量備份要處理，所以這個引數需要加上）
--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:
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引數後面必須是絕對路徑，否則恢復報錯）
--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的專用日誌檔案）
--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:
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目錄
--defaults-file=mysql/bin/my.cnf --copy-back bak/2016-01-20_15-45-54/
-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
--驗證資料是否恢復
-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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL入門學習之——實戰mysqldumpMySql
- MySQL入門學習之——innodb lockMySql
- 深度學習：TensorFlow入門實戰深度學習
- go實戰web入門學習筆記GoWeb筆記
- MySQL入門學習之——MySQL錯誤解決彙總MySql
- MySQL入門學習之——原始碼安裝mysql5.5MySql原始碼
- MySQL入門學習之——mysql與oracle死鎖對比MySqlOracle
- 【Mysql學習】mysql的使用入門MySql
- 【Mysql 學習】mysql 的使用入門MySql
- 【mybatis學習之入門（一）】MyBatis
- MySQL學習筆記---入門使用MySql筆記
- mysql 基礎命令入門學習MySql
- Python爬蟲入門學習實戰專案（一）Python爬蟲
- MySQL8.0之XtraBackupMySql
- shiro實戰系列(二)之入門實戰續
- Nacos入門學習&實踐
- MySQL 之XtraBackup全量增量熱備實踐MySql
- 快速入門——深度學習理論解析與實戰應用深度學習
- Scala學習 1.1 Scala基礎與語法入門實戰
- MySQL基礎入門學習教程：mysql資料庫的實現原理MySql資料庫
- MySQL運維實戰之備份和恢復（8.1）xtrabackup全量備份MySql運維
- JDBC+MySQL入門實戰(實現CURD的例子)JDBCMySql
- Mysql學習筆記2--使用入門MySql筆記
- MySQL-學習-快速入門/注入攻擊MySql
- Flutter學習之入門和體驗Flutter
- Java框架學習之Hibernate入門Java框架
- 《深度學習之TensorFlow：入門、原理與進階實戰》PDF+原始碼+李金洪深度學習原始碼
- .net 開源模板引擎jntemplate 實戰演習：基礎篇之入門
- 跟你談談MySQL資料庫入門學習之安裝篇(轉)MySql資料庫
- Kafka實戰－入門Kafka
- ElasticSearch實戰－入門Elasticsearch
- podman 入門實戰
- NGUI從入門到實戰第1章開啟NGUI學習之旅NGUI
- 深度學習之PyTorch實戰（4）——遷移學習深度學習PyTorch遷移學習
- Scala 學習筆記（1）之入門篇筆記
- 指令碼之美│VBS 入門互動實戰指令碼
- 重新學習Mysql資料庫1：無廢話MySQL入門MySql資料庫
- 初學者都能學會的ElasticSearch入門實戰Elasticsearch