Shulou(Shulou.com)05/31 Report--

This article introduces you how to carry out the full recovery of Innobackupex in mysql, the content is very detailed, interested friends can refer to, I hope it can be helpful to you.

I. the principle of Innobackupex recovery

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? 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? les (some transactions could 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? les), and the log? les are re-created.

There is an inconsistency in transactions during the backup (when copy data), that is, when copy starts, some transactions have already started, some transactions have just started, and they are committed or rolled back before or after the end of copy.

These uncertain transactions need to be determined whether they are finally committed or rolled back before recovery. The operation at this stage is called the prepare phase.

This prepare phase relies on the xtrabackup log (from innodb logfile) at the time of backup and uses the-- apply-log parameter to achieve consistency.

-- the apply-log parameter will roll forward or rollback accordingly according to xtrabackup log, and the innodb logfile file will be rebuilt after completion.

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 100MB. In general, the more memory available to the process,the better. The amount of memory used in the process can be speci?ed by multiples of bytes:

During recovery, the-- use-memory option accelerates the prepare process, which is set to 100MB by default if the system's available memory is large enough.

Innobackupex recovery schematic diagram

2. Full recovery process

1. Current environment

Mysql > show variables like 'version'

+-+ +

| | Variable_name | Value |

+-+ +

| | version | 5.6.25-log |

+-+ +

1 row in set (0.00 sec)

two。 Prepare experimental data

Mysql > create database hot_recover

Query OK, 1 row affected (0.04 sec)

Mysql > use hot_recover

Database changed

Mysql > create table test (id int)

Query OK, 0 rows affected (0.02 sec)

Mysql > insert into test values (1)

Query OK, 1 row affected (0.01sec)

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from test

+-+

| | id |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

3. Complete database

[root@mysql01] # innobackupex-- user=root-- password=oracle-- port=3606 / xtrabackup/full/

Xtrabackup: Transaction log of lsn (1631855) to (1631855) was copied.

170608 20:54:21 completed OK!

4. View the content generated by the backup

[root@mysql02 mysql] # ll/ xtrabackup/full/

Drwxr-x---. 6 root root 4096 Jun 8 20:54 2017-06-08 20-54-14

[root@mysql02 mysql] # ll/ xtrabackup/full/2017-06-08 20-54-14

Total 12324

-rw-r-. 1 root root 418 Jun 8 20:54 backup-my.cnf

Drwxr-x---. 2 root root 4096 Jun 8 20:54 hot_recover

-rw-r-. 1 root root 12582912 Jun 8 20:54 ibdata1

Drwxr-x---. 2 root root 4096 Jun 8 20:54 mysql

Drwxr-x---. 2 root root 4096 Jun 8 20:54 performance_schema

Drwxr-x---. 2 root root 4096 Jun 8 20:54 test

-rw-r-. 1 root root 18 Jun 8 20:54 xtrabackup_binlog_info

-rw-r-. 1 root root 113 Jun 8 20:54 xtrabackup_checkpoints

-rw-r-. 1 root root 482 Jun 8 20:54 xtrabackup_info

-rw-r-. 1 root root 2560 Jun 8 20:54 xtrabackup_logfile

5. Delete data online for recovery comparison

Mysql > drop table test

Query OK, 0 rows affected (0.24 sec)

Mysql > select * from test

ERROR 1146 (42S02): Table 'hot_recover.test' doesn't exist

6. Stop the mysql database

[root@mysql01 ~] # service mysql stop

Shutting down MySQL.... [OK]

[root@mysql01 ~] # netstat-nltp | grep mysql | grep 3606

7. Apply-log, prepare complete documents

[root@mysql02] # innobackupex-- apply-log-- user=oracle-- password=oracle-- port=3606 / xtrabackup/full/2017-06-08 20-54-14

170608 20:56:38 innobackupex: Starting the apply-log operation # # start apply-log

IMPORTANT: Please check that the apply-log run completes successfully.

At the end of a successful apply-log run innobackupex

Prints "completed OK!".

Innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: 6f7a799)

Xtrabackup: cd to / xtrabackup/full/2017-06-08 / 20-54-14 /

Xtrabackup: This target seems to be not prepared yet.

InnoDB: Number of pools: 1

Xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn= (1631855)

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 = 8388608

Xtrabackup: Using 104857600 bytes for buffer pool (set by-use-memory parameter)

. Omit

InnoDB: Initializing buffer pool, total size = 100m, instances = 1, chunk size = 100m

InnoDB: Completed initialization of buffer pool

InnoDB: page_cleaner coordinator priority:-20

InnoDB: Highest supported file format is Barracuda.

InnoDB: The log sequence number 1626143 in the system tablespace does not match the log sequence number 1631855 in the ib_logfiles!

InnoDB: Starting crash recovery. # # start innodb instance recovery

InnoDB: xtrabackup: Last MySQL binlog file position 586, file name binlog.000008

InnoDB: Removed temporary tablespace data file: "ibtmp1"

InnoDB: Creating shared tablespace for temporary tables

InnoDB: Setting file'. / ibtmp1' size to 12 MB. Physically writing the file full; Please wait...

InnoDB: File'. / ibtmp1' size is now 12 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 1632277

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 1632296

170608 20:56:44 completed OK! # # successful recovery

8. View information about files after recovery

(the file at 20:56 has just been updated or generated, mainly the system tablespace data file and innodb log file, and the Innodb checkpoint file updated.)

[root@mysql02 mysql] # ll/ xtrabackup/full/2017-06-08 / 20-54-14 /

Total 131108

-rw-r-. 1 root root 418 Jun 8 20:54 backup-my.cnf

Drwxr-x---. 2 root root 4096 Jun 8 20:54 hot_recover

-rw-r-. 1 root root 12582912 Jun 8 20:56 ibdata1

-rw-r-. 1 root root 50331648 Jun 8 20:56 ib_logfile0

-rw-r-. 1 root root 50331648 Jun 8 20:56 ib_logfile1

-rw-r-. 1 root root 12582912 Jun 8 20:56 ibtmp1

Drwxr-x---. 2 root root 4096 Jun 8 20:54 mysql

Drwxr-x---. 2 root root 4096 Jun 8 20:54 performance_schema

Drwxr-x---. 2 root root 4096 Jun 8 20:54 test

-rw-r-. 1 root root 18 Jun 8 20:54 xtrabackup_binlog_info

-rw-r--r--. 1 root root 18 Jun 8 20:56 xtrabackup_binlog_pos_innodb

-rw-r-. 1 root root 113 Jun 8 20:56 xtrabackup_checkpoints

-rw-r-. 1 root root 482 Jun 8 20:54 xtrabackup_info

-rw-r-. 1 root root 8388608 Jun 8 20:56 xtrabackup_logfile

9. Rename the original folder to a new location and create the original folder

[root@mysql02 mysql] # mv / data/mysql / data/mysqlbak

[root@mysql02 mysql] # mkdir-p / data/mysql

10. Copy the recovered data files back to the original location

[root@mysql02 mysql] # innobackupex-- defaults-file=/etc/my.cnf-- user=root-- password=oracle-- port=3606-- copy-back / xtrabackup/full/2017-06-08 20-54-14 /

170608 21:01:02 innobackupex: Starting the copy-back operation # # start copying the backed up files back to the original path

Innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: 6f7a799)

170608 21:01:02 [01] Copying ib_logfile0 to / data/mysql/ib_logfile0

170608 21:01:03 [01]... done

170608 21:01:03 [01] Copying ib_logfile1 to / data/mysql/ib_logfile1

. Omit

170608 21:01:07 [01] Copying. / performance_schema/table_io_waits_summary_by_table.frm to / data/mysql/performance_schema/table_io_waits_summary_by_table.frm

170608 21:01:07 [01]... done

170608 21:01:07 [01] Copying. / performance_schema/events_stages_history_long.frm to / data/mysql/performance_schema/events_stages_history_long.frm

170608 21:01:07 [01]... done

170608 21:01:07 completed OK! # # copy ends

11. Permission modification

[root@mysql02 ~] # mkdir-p / data/mysql/binarylog (note: here I binlog is under the path of datadir, so I want to create a directory for binlog separately)

Chown-R mysql:mysql / data/mysql

twelve。 Start the restored instance

[root@mysql02 mysql] # mysqld_safe-- defaults-file=/etc/my.cnf &

13. Login, authentication

[root@mysql02] # mysql-uroot-poracle

Mysql > select * from test

| | id |

| 1 | > restore successfully!

On how to carry out the full recovery of Innobackupex in mysql to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.