Full backup recovery based on Innobackupex, innobackupex

Full backup recovery based on Innobackupex, innobackupex

For Hot Standby MySQL databases, xtrabackup is the choice of most DBA friends. Xtrabackup is embedded with an innobackupex for Hot Standby MySQL database. This article describes the full backup recovery based on innobackupex and provides a demonstration for your reference.
For full backup of Innobackupex, see Innobackupex full backup database.

 

1. Innobackupex restoration Principle
After creating a backup, the data is not ready to be restored. there might be uncommitted transactions to be undone or transactions in the logs to be replayed. doing those pending operations will make the data into les consistent and it is the purpose of the prepare stage. once this has been done, the data is ready to be used.
To prepare a backup with innobackupex you have to use the -- apply-log and the path to the backup directory as an argument:

Innobackupex replayed the committed transactions in the log records les (some transactions cocould have been done while the backup was being done) and rolled back the uncommitted ones. once this is done, all the information lay in the tablespace (the InnoDB tables les), and the log tables les are re-created.

During the backup period (when copying data), the transaction is inconsistent. That is, when the copy operation starts, some transactions have started, and some have just started, but the copy operation is committed or rolled back before or after the copy operation ends.
These uncertain transactions need to be recovered before determining whether the final commit or rollback is performed. Operations in this phase are called prepare stages.
This prepare stage depends on the xtrabackup log (from innodb logfile) during Backup and uses the -- apply-log parameter for consistency.
-- The apply-log parameter performs rollback or rollback Based on the xtrabackup log. After the rollback, the innodb logfile file is rebuilt.

The -- use-memory option The preparing process can be speed up by using more memory in it. it depends on the free or available RAM on your system, it defaults to 100 MB. in general, the more memory available to the process, the better. the amount of memory used in the process can be speci encoded ed by multiples of bytes:
During recovery, the -- use-memory option can accelerate the prepare process. If the available memory of the system is large enough, this value is set to 100 MB by default.

Innobackupex recovery
 

2. Demonstrate restoring full backup

A. The current environment robin @ localhost [(none)]> show variables like 'version '; + bytes + ------------ + | Variable_name | Value | + --------------- + ------------ + | version | 5.6.12-log | + --------------- + ------------ + robin @ localhost [(none)]> create database fullbakdb; query OK, 1 row affected (0.01 sec) robin @ localhost [(none)]> use fullbakdbrobin @ localhost [fullbakdb]> create table tb (id smallint, val varchar (20); robi N @ localhost [fullbakdb]> insert into tb values (1, 'Robin '), (2, 'lesham'); robin @ localhost [fullbakdb]> select * from tb; + ------ + --------- + | id | val | + ------ + --------- + | 1 | robin | 2 | leshami | + ------ + --------- + B, full backup database SHELL> innobackupex -- user = robin-password = xxx -- port = 3606 -- socket =/tmp/mysql3606.sock \> -- defaults-file =/data/inst3606/data3606/my3606.cnf/data/bak/hotbak -- below is SHELL> pw after the backup is complete D/data/bak/hotbakSHELL> lldrwxr-xr-x 7 root 4096 -- view the file generated by the backup SHELL> ll 77944-rw-r -- r -- 1 root 357 backup-my.cnfdrwx ------ 2 root 4096 fullbakdb-rw-r ----- 1 root 79691776 running ibdata1drwx ------ 2 root 4096 running mysqldrwxr-xr-x 2 root 4096 running 0 Am ------ 2 root 4096 recoverdrwx ------ 2 root 4096 sakila-rw-r -- 1 root 26 xtrabackup_binlog_info-rw-r ----- 1 root 93 xtrabackup_checkpoints-rw-r -- r -- 1 root 684 xtrabackup_info-rw-r ----- 1 root 2560 xtrabackup_logfilec, clear table tb for test recovery function robin @ l Ocalhost [fullbakdb]> truncate table tb; Query OK, 0 rows affected (0.01 sec) robin @ localhost [fullbakdb]> select * from tb; Empty set (0.00 sec) d. Restore full backup -- disable the original Instance SHELL> mysqldown-P3606SHELL> netstat-nltp | grep mysql | grep 3606 -- prepare the full backup file SHELL> innobackupex -- apply-log -- user = robin-password = xxx -- port = 3606 -- socket =/tmp/mysql3606.sock \> -- defaults-file =/data/inst3606/data3606/my3606.cnf/data/bak/hotbak/201 4-12-22_09-04-05InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003,200 9 Innobase Oy ...... ignore non-important information, the same below ...... xtrabackup: Starting InnoDB instance for recovery. -- start innodb instance recovery ........... innoDB: Starting crash recovery. -- start crash recovery ........... 141222 09:13:59 innobackupex: Restarting xtrabackup with command: xtrabackup -- defaults-file = "/data/inst3606/data3606/my3606.cnf" -- defaults-group =" Mysqld "-- prepare -- target-dir =/data/bak/hotbak/2014-12-22_09-04-05 -- tmpdir =/tmpfor creating ib_logfile * -- note that -- prepare is used and logfilextrabackup of innodb is created: notice: xtrabackup_logfile was already used to '-- prepare '. xtrabackup: using the following InnoDB configuration for recovery :............ -- Author: Leshami -- Blog: http://blog.csdn.net/leshami InnoDB: Shutdown completed; log sequence number 391275633141222 09:14:02 innobackupex: completed OK! -- Recover successfully -- View information about the recovered file SHELL> ll defaults 178404-rw-r -- r -- 1 root 357 backup-my.cnfdrwx ------ 2 root 4096 fullbakdb-rw-r ----- 1 root 79691776 ibdata1-rw-r -- r -- 1 root 50331648 ib_logfile0-rw-r -- r -- 1 root 50331648 ib_logfile1drwx ------ 2 root 4096 mysqld Rwxr-xr-x 2 root 4096 ------ 2 root 4096 recoverdrwx ------ 2 root 4096 sakila-rw-r -- 1 root 26 xtrabackup_binlog_info-rw-r -- r -- 1 root 24 xtrabackup_binlog_pos_innodb-rw-r ----- 1 root 93 xtrabackup_checkpoints-rw-r -- r -- 1 root 684 2014/12 /22 xtrabackup_info-rw-r ----- 1 root 2097152 xtrabackup_logfile -- observe the changes in the content of the folder above, you can see is a new or changed file, the system tablespace data file and innodb log file are generated. At the same time, Innodb checkpoint files are also updated. (Note: Hot Backup only involves the InnoDB engine, all InnoDB-related changes occur when applying-log-Rename the original folder to the new location, and create the original folder SHELL> mv/data/inst3606/data3606/data/inst3606/data3606bkSHELL> mkdir-p/data/inst3606/data3606 -- copy the recovered data file back to the original location SHELL> innobackupex -- user = robin-p Assword = xxx -- port = 3606 -- defaults-file =/data/inst3606/data3606bk/my3606.cnf \> -- copy-back/data/bak/hotbak/2014-12-22_09-04-05 ...... ignore non-important information, the same below ...... innobackupex: Starting to copy files in '/data/bak/hotbak/2014-12-22_09-04-05' -- Start copying the backup file back to the original path innobackupex: back to original data directory '/data/inst3606/data3606' -- original path location ............ copy all data files, index files, format files, etc ............ innobackupex: Starting to co Py InnoDB system tablespace -- Copy system tablespace innobackupex: in '/data/bak/hotbak/2014-12-22_09-04-05 'innobackupex: back to original InnoDB data directory'/data/inst3606/data3606 'innobackupex: copying '/data/bak/hotbak/users/ibdata1' to '/data/inst3606/data3606/ibdata1 'innobackupex: Starting to copy InnoDB undo tablespaces -- copy the undo tablespace innobackupex: in '/data/bak/hotbak/2014-12-22_09-04-05' inno Backupex: back to '/data/inst3606/data3606 'innobackupex: Starting to copy InnoDB log files -- copy the redo tablespace innobackupex: in'/data/bak/hotbak/comment 'innobackupex: back to original InnoDB log directory '/data/inst3606/data3606 'innobackupex: copying '/data/bak/hotbak/logs/ib_logfile1' to '/data/inst3606/data3606/ib_logfile1 'innobackupex: copying'/data/bak/hotbak/2014-12-22_09 -04-05/ib_logfile0 'to'/data/inst3606/data3606/ib_logfile0 'innobackupex: Finished copying back files.141222 09:34:47 innobackupex: completed OK! -- Permission modification SHELL> cp/data/inst3606/data3606bk/my3606.cnf/data/inst3606/data3606/my3606.cnfSHELL> chown-R mysql: mysql/data/inst3606/data3606 -- start the recovered instance SHELL> mysqld_safe -- defaults-file =/data/inst3606/data3606bk/my3606.cnf & -- verify the recovery SHELL> SQL-P3606robin @ localhost [(none)]> select * from fullbakdb. tb; + ------ + --------- + | id | val | + ------ + --------- + | 1 | robin | 2 | leshami | + ------ + --------- + robin @ localhost [(none)]> drop database fullbakdb; Query OK, 1 row affected( 0.01 sec)

3. Summary
A. Xtrabackup recovery aims to ensure transaction (data) Consistency. The Xtrabackup log records the status during the backup of these transactions.
B. The recovery process is divided into two phases: Prepare and copy back. The original Instance is closed before recovery.
C. In the Prepare stage, rollback or rollback is performed based on the information captured from the innodb logfile (recorded in the xtrabackup log ).
D. In the Prepare stage, a new innodb logfile (null) will be created after successful rollback or rollback)
E. In the copy back stage, all files that have been successfully recovered are copied back to the original or specified data directory (the directory should be an empty directory)
F. Disable the original Instance before copying back. If the instance is restored to a different one, no
G. After the copy back operation is complete, modify the corresponding permissions.
H. Start and verify the recovered instance