Initial use of Xtrabackup

Here is a reference to master Jeanron's article: http://chuansong.me/n/1696438151334
After the disgusting installation of the front, next try a xtrabackup backup function.
Xtrabackup is mainly used for hot backup innodb, or as a tool for data in xtradb tables, can not back up other types of tables, and can not back up the data table structure;
Innobackupex is a Perl script that encapsulates Xtrabackup to back up and restore MyISAM tables and data table structures.
* * 1, backup of the resolution

**
You can see a lot of innobackupex parameters from the Innobackupex–help command, and try to make a full preparation below.

[Root@cxqtest ~]# cd/data/backup/0408/[root@cxqtest 0408]# innobackupex-s/data/3306/mysql.sock/data/backup/0408/--  No-timestamp--no-lock--throttle=100 170304 11:00:41 innobackupex:starting The backup operation important:please Check
           That's the backup run completes successfully.

At the end of a successful backup run Innobackupex prints "completed ok!". 170304 11:00:42 Version_check connecting to MySQL server with DSN ' dbi:mysql:;mysql_read_default_group=xtrabackup;port=
3306;mysql_socket=/data/3306/mysql.sock ' (using Password:no). Failed to connect to MySQL server:dbi connect ('; mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/data/ 3306/mysql.sock ', ',...) Failed:access denied for user ' root ' @ ' localhost ' (using password:no) at-line 1314 170304 11:00:43 connecting to MySQL Server Host:localhost, User:not set, Password:not set, port:3306, Socket:/data/3306/mysql.sock Failed to connect to M Ysql server:access denied for user ' root ' @ ' localHost ' (using Password:no). [Root@cxqtest 0408]# innobackupex-s/data/3306/mysql.sock/data/backup/0408/--no-timestamp--no-lock--throttle=100- Uroot-p Innobackupex: [ERROR] innobackupex:option '-P ' requires an argument

The error was made because the user name password was not written

[Root@cxqtest 0408]# innobackupex-s/data/3306/mysql.sock/data/backup/0408/--no-timestamp--no-lock--throttle=100- uroot-pmysql123 170304 11:01:24 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!". 170304 11:01:24 Version_check connecting to MySQL server with DSN ' dbi:mysql:;mysql_read_default_group=xtrabackup;port=
3306;mysql_socket=/data/3306/mysql.sock ' as ' root ' (using Password:yes). 170304 11:01:24 Version_check Connected to MySQL server 170304 11:01:24 the Version_check a version check executing
The server ...
170304 11:01:24 Version_check done. 170304 11:01:24 connecting to MySQL server Host:localhost, User:root, Password:set, port:3306, Socket:/data/3306/mysq L.sock Using Server version 5.6.30-log Innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision ID : 8EC05B7) Xtrabackup:usEs posix_fadvise (). XTRABACKUP:CD to/data/3306/data xtrabackup:open files limit requested 65535, set to 65535 xtrabackup:using the FOLLOWI
ng InnoDB Configuration:xtrabackup:innodb_data_home_dir =. Xtrabackup:innodb_data_file_path = Ibdata1:1g:autoextend Xtrabackup:innodb_log_group_home_dir =./Xtrabackup:inn Odb_log_files_in_group = 3 Xtrabackup:innodb_log_file_size = 524288000 xtrabackup:using O_DIRECT innodb:number of Poo Ls:1 170304 11:01:25 >> Log scanned up to (1626008) xtrabackup:generating a list of tablespaces innodb:allocated Tablespace ID 1 for mysql/innodb_table_stats, old maximum is 0 170304 11:01:25 [?] copying./ibdata1
8/ibdata1 170304 11:01:26 >> Log scanned up to (1626008) 170304 11:01:27 >> Log scanned up to (1626008) ...
Xtrabackup:the Latest Check Point (for incremental): ' 1626008 ' xtrabackup:stopping log copying thread. .170304 11:02:00 >> Log scanned up to (1626008) 170304 11:02:01 Backup Created in directory '/data/backup/0408/' MySQL binlog position:filename ' mybinlog.000006 ', Position ' 191 ', Gtid of the LA        St change ' 82f3c6ed-007a-11e7-9b50-000c298ee31c:1-7 ' 170304 11:02:01 [] Writing backup-my.cnf 170304 11:02:01 [00] ... done 170304 11:02:01 [] Writing xtrabackup_info 170304 11:02:01 [+] ... done xtrabackup:transaction log of
LSN (1626008) to (1626008) is copied. 170304 11:02:01 completed ok!

After a period of backup, you can see that the backup is complete
Let's look at the backup file as follows:

[Root@cxqtest 0408]# du-sh 
1.1G    .
[Root@cxqtest 0408]# du-sh. *
4.0K    ./backup-my.cnf
1.1G    ./ibdata1
1.7M    ./mysql
636K    ./performance_schema
4.0K    ./test
4.0K    /xtrabackup_binlog_info
4.0K    ./ Xtrabackup_checkpoints
4.0K    ./xtrabackup_info
4.0K    /xtrabackup_logfile

You can see back up the My.cnf,ibdata1, the various databases, and at this point a few more xtrabackup files
Check out these files in the following sections:
The –xtrabackup_binlog_info file records the information that should be binlog

[Root@cxqtest 0408]# more Xtrabackup_binlog_info
xtrabackup_binlog_info
mybinlog.000006 191 82f3c6ed-007a-11e7-9b50-000c298ee31c:1-7

–xtrabackup_checkpoints records that the type of backup is fully prepared and is available from 0 to 1626008 of the latest full

[Root@cxqtest 0408]# more xtrabackup_checkpoints
backup_type = Full-backupedf Rom_lsn =
0
to_lsn = 1626008
last_lsn = 1626008
Compact = 0
recover_binlog_info = 0

–xtrabackup_info Records information about the command.

[Root@cxqtest 0408]# more xtrabackup_info
uuid = f0083067-0086-11e7-8435-000c298ee31c
name = Innobackupextool
command =-s/data/3306/mysql.sock/data/backup/0408/--no-timestamp--no-lock--throttle=100-uroot-pmysql123
Version = 2.4.6
ibbackup_version = 2.4.6
server_version = 5.6.30 logstart_time
= 2017-03-04 11:01:24< C7/>end_time = 2017-03-04 11:02:01
lock_time = 1488596521 binlog_pos
= filename ' mybinlog.000006 ', position ' 191 ', Gtid of the last change 
' 82f3c6ed-007a-11e7-9b50-000c298ee31c:1-7 '
innodb_from_lsn = 0
innodb_to _LSN = 1626008
partial = n
incremental = n
format = file
compact = n
compressed = n
encrypte D = N

–xtrabackup_logfile See logfile cannot use more view, is a binary log file format, so use strings to view the following:
The time of the backup is recorded

[Root@cxqtest 0408]# more Xtrabackup_logfile
--more--(88%)

? ??? 庰 ??? 掟 ??? PO ??? Dongjiu ??? 烉 ??? ??? ︷??? ??? ??
[Root@cxqtest 0408]# Xshellxshellxshellxshellxshellxshellxshellxshellxshellxshell-bash: Xshellxshellxshellxshellxshellxshellxshellxshellxshellxshell:command not found
[root@cxqtest 0408]#
 [ Root@cxqtest 0408]# strings Xtrabackup_log
 filextrabkup 170304 11:01:25

2. Full-standby recovery test

Data recovery or using the Innobackupex tool
Here the data recovery is divided into two steps, prepare and restore recovery, prepare meaning is that if we back up the data, there are uncommitted transactions, but the data exists in the backup, which is a state of inconsistent data, in the start of the database need to go a roll forward, Then it is a rollback operation. This embodiment mainly lies in logfile and Ibdata. is implemented using the Apply-log option.
The premise needs to first delete the previous directory, to stop the MySQL service.

[Root@cxqtest 0408]#/etc/init.d/mysqld stop
shutting down MySQL                                     ... [  OK  ]
[root@cxqtest 0408]# 
[root@cxqtest 0408]# cd 
[root@cxqtest ~]# 
] [root@cxqtest 3306]# LS
data
[root@cxqtest 3306]# MV Data data.bak
[root@cxqtest 3306]# ls
data.bak
[root@cxqtest 3306]# mkdir Data

You can use the following command for recovery operations:
Step One: Execute innobackupex–defaults-file=/data/backup/0408/backup-my.cnf-uroot-pmysql123–apply-log/data/backup/0408/

[Root@cxqtest 0408]# Innobackupex--defaults-file=/data/backup/0408/backup-my.cnf-uroot-pmysql123--apply-log/data /backup/0408/170304 12:31:01 innobackupex:starting The apply-log operation Important:please Check that the Apply-log R
           UN completes successfully.

At the end of a successful apply-log run Innobackupex prints "completed ok!". Innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id:8ec05b7) XTRABACKUP:CD
Up/0408/xtrabackup:this target seems to is not prepared yet. Innodb:number of Pools:1 Xtrabackup:xtrabackup_logfile detected:size=8388608, start_lsn= (1626008) xtrabackup:using th
E following InnoDB configuration for recovery:xtrabackup:innodb_data_home_dir =.
Xtrabackup:innodb_data_file_path = Ibdata1:1g:autoextend Xtrabackup:innodb_log_group_home_dir =. Xtrabackup:innodb_log_files_in_group = 1 Xtrabackup:innodb_log_file_size = 8388608 xtrabackup:using The following I Nnodb Configuration for recovery:xtrabackup:innodb_data_home_dir =.
Xtrabackup:innodb_data_file_path = Ibdata1:1g:autoextend Xtrabackup:innodb_log_group_home_dir =. Xtrabackup:innodb_log_files_in_group = 1 xtrabackup:innodb_log_file_size = 8388608 xtrabackup:starting InnoDB insta
NCE for recovery. Xtrabackup:using 104857600 bytes for buffer pool (set by--use-memory parameter) innodb:punch HOLE support available INN Odb:mutexes and Rw_locks use GCC atomic builtins innodb:uses event mutexes INNODB:GCC the Builtin () is U Sed for memory barrier innodb:compressed tables use zlib 1.2.3 Innodb:number of pools:1 innodb:using CPU CRC32 instruc tions innodb:initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M innodb:completed Initializati
On the buffer pool Innodb:page_cleaner Coordinator priority: -20 Innodb:highest Supported file format is barracuda. Innodb:the log sequence number 1625998 in the system tablespace does not match the log sequence number 1626008 in the ib_logfiles!
Innodb:database is not shutdown normally!
Innodb:starting crash recovery.
Innodb:doing recovery:scanned up to log sequence number 1626008 (0%) Innodb:creating shared tablespace for temporary tables innodb:setting file './ibtmp1 ' size to MB. Physically writing the file full;
Please wait ...
Innodb:file './ibtmp1 ' size is now MB. innodb:96 Redo rollback segment (s) found.
1 Redo rollback Segment (s) are active.
INNODB:32 Non-redo rollback segment (s) are active. Innodb:waiting for purge to start innodb:5.7.13 started; Log sequence number 1626008 xtrabackup:starting shutdown with Innodb_fast_shutdown = 1 innodb:fts optimize thread Exiti
Ng.
Innodb:starting shutdown ... Innodb:shutdown completed;
Log sequence number 1626027 Innodb:number of pools:1 xtrabackup:using the following InnoDB for configuration:
Xtrabackup:innodb_data_home_dir =. Xtrabackup:innodb_data_file_path = Ibdata1:1g:autoextend Xtrabackup:innodB_log_group_home_dir =. Xtrabackup:innodb_log_files_in_group = 3 Xtrabackup:innodb_log_file_size = 524288000 innodb:punch HOLE support Avai lable innodb:mutexes and Rw_locks use GCC atomic builtins innodb:uses event mutexes INNODB:GCC Builtin __sync_synchroni Ze () is used to memory barrier innodb:compressed tables use zlib 1.2.3 Innodb:number of pools:1 innodb:using CPU CRC3 2 Instructions innodb:initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M innodb:completed ini  Tialization of buffer pool Innodb:page_cleaner Coordinator priority: -20 innodb:setting log file./ib_logfile101 size to MB innodb:progress in mb:100 innodb:setting log file/ib_logfile1 size to MB innodb:progres s in mb:100 innodb:setting log file/ib_logfile2 size to MB innodb:progress in mb:100 200 300 4 of innodb:renaming log file./ib_logfile101 to./ib_logfile0 innodb:new log files created, lsn=1626027 Innodb:highEST supported file format is barracuda. Innodb:log scan progressed past the checkpoint LSN 1626124 innodb:doing recovery:scanned up to Log sequence number 1626
133 (0%)
Innodb:doing recovery:scanned up to log sequence number 1626133 (0%)
Innodb:database is not shutdown normally!
Innodb:starting crash recovery. Innodb:removed temporary tablespace data file: "IBTMP1" innodb:creating shared tablespace for temporary tables innodb:s etting file './ibtmp1 ' size to MB. Physically writing the file full;
Please wait ...
Innodb:file './ibtmp1 ' size is now MB. innodb:96 Redo rollback segment (s) found.
1 Redo rollback Segment (s) are active.
INNODB:32 Non-redo rollback segment (s) are active. Innodb:waiting for purge to start innodb:page_cleaner:1000ms intended loop took 54592ms. The settings might not is optimal.
(Flushed=0 and Evicted=0, during the time.) Innodb:5.7.13 started; Log sequence number 1626133 xtrabackup:starting shutdown with Innodb_fast_shutdown = 1 innodb:fts Optimize thread exiting.
Innodb:starting shutdown ... Innodb:shutdown completed; Log sequence number 1626152 170304 12:32:00 completed ok!

Then do the following:

[Root@cxqtest 0408]# Innobackupex--defaults-file=/data/backup/0408/backup-my.cnf-uroot-pmysql123--copy-back/data /backup/0408/
170304 12:32:42 innobackupex:starting The copy-back operation Important:please

check that cop Y-back run completes successfully.
           At the end of a successful copy-back run Innobackupex
           prints "completed ok!".

Innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id:8ec05b7)
Error:datadir must is s Pecified.

The error is to execute datadir because the parameter file that I specified is a backup backup-my.cnf content as follows:

[Root@cxqtest 0408]# cat backup-my.cnf 

[mysqld]