Innobackupex-based full Backup Recovery

Innobackupex-based full Backup Recovery

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. Current Environment
Robin @ localhost [(none)]> show variables like 'version ';
+ --------------- + ------------ +
| 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 fullbakdb

Robin @ localhost [fullbakdb]> create table tb (id smallint, val varchar (20 ));

Robin @ 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

-- The following content after the backup is completed
SHELL> pwd
/Data/bak/hotbak
SHELL> ll
Drwxr-xr-x 7 root 4096

-- View the files generated by the backup
SHELL> ll 2014-12-22_09-04-05
Total 77944
-Rw-r -- 1 root 357 backup-my.cnf
Drwx ------ 2 root 4096 fullbakdb
-Rw-r ----- 1 root 79691776 ibdata1
Drwx ------ 2 root 4096 mysql
Drwxr-xr-x 2 root 4096 performance_schema
Drwx ------ 2 root 4096 recover
Drwx ------ 2 root 4096 sakila
-Rw-r -- 1 root 26 2014/12/22 09:04 xtrabackup_binlog_info
-Rw-r ----- 1 root 93 xtrabackup_checkpoints
-Rw-r -- 1 root 684 xtrabackup_info
-Rw-r ----- 1 root 2560 xtrabackup_logfile

C. Clear the table tb to test the restoration function.
Robin @ localhost [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-P3606
SHELL> 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/2014-12-22_09-04-05

InnoDB 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 =/tmp
For creating ib_logfile * -- note that -- prepare is used and the logfile of innodb is created.

Xtrabackup: notice: xtrabackup_logfile was already used to '-- prepare '.
Xtrabackup: using the following InnoDB configuration for recovery:
............
-- Author: Leshami
-- Blog: