Steps to complete incremental data backup and recovery using innobackupex

Steps to complete incremental data backup and recovery using innobackupex

1. Planning backup catalog and configuration and my.cnf

Mkdir / backup/mysql/full

Mkdir / backup/mysql/daily1

Mkdir / backup/mysql/daily2

two。 Complete:

Innobackupex-user=root-password=yhw0715/backup/mysql/full

View the files after the backup is complete:

Shell > ls-1 / backup/mysql/full

Drwxr-x---. 27 root root 4096 Jun 8 20:48 2017-06-08 October 20-48-23 # A folder named after the current date is generated under the full directory

Backed up files:

Shell > ls-1 / backup/mysql/full/2017-06-08 / 20-48-23

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

-rw-r-. 1 root root 722 Jun 8 20:48 ib_buffer_pool

-rw-r-. 1 root root 79691776 Jun 8 21:07 ibdata1

-rw-r-. 1 root root 50331648 Jun 8 21:07 ib_logfile0

-rw-r-. 1 root root 50331648 Jun 8 21:07 ib_logfile1

-rw-r-. 1 root root 12582912 Jun 8 21:07 ibtmp1

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

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

-rw-r-. 1 root root 412 Jun 8 20:48 backup-my.cnf

-rw-r-. 1 root root 24 Jun 8 20:48 xtrabackup_binlog_info

-rw-r--r--. 1 root root 24 Jun 8 21:07 xtrabackup_binlog_pos_innodb

-rw-r-. 1 root root 115 Jun 8 21:07 xtrabackup_checkpoints

-rw-r-. 1 root root 479 Jun 8 20:48 xtrabackup_info

-rw-r-. 1 root root 8388608 Jun 8 21:07 xtrabackup_logfile

Where:

Xtrabackup_binlog_info records the binlog file name and position at the end of the current backup, which can be used for future recovery based on binlog logs, such as mysqlbinlog-start-position=154 binlog.000001 | mysql-uroot-p.

Xtrabackup_binlog_pos_innodb: I don't quite understand the difference with xtrabackup_binlog_info.

Xtrabackup_checkpoints records the LSN number when the backup is completed, which is used for checking based on this LSN during incremental backups.

Xtrabackup_info (record some information about the backup process and binlog location information):

Uuid = 82aa52a6-4cc6-11e7-bf24-000c29373cd3

Name =

Tool_name = innobackupex

Tool_command =-- user=root-- password=.../backup/mysql/full/

Tool_version = 2.4.1

Ibbackup_version = 2.4.1

Server_version = 5.7.17-log

Start_time = 2017-06-08 20:48:24

End_time = 2017-06-08 20:48:33

Lock_time = 0

Binlog_pos = filename'slave-binlog.000001', position '154'

Innodb_from_lsn = 0

Innodb_to_lsn = 10490958

Partial = N

Incremental = N

Format = file

Compact = N

Compressed = N

Encrypted = N

Xtrabackup_logfile will first record the current LSN,xtrabackup after the backup starts. A monitoring thread will be opened to monitor the changes of the redo log (some incremental data may be generated because it is a hot backup). After the backup ends, the resulting redo log will be backed up to the xtrabackup_logfile. The xtrabackup_logfile logs are redone during recovery to achieve data backup consistency.

3. Incremental backup

First insert some test data insert into test values ('a6')

Increment 1:

Innobackupex-- user=root-- password=yhw0715--incremental-basedir=/backup/mysql/full/2017-06-07 23-04-50-- incremental/backup/mysql/daily1

Insert some test data insert into test values ('a6')

Increment 2:

Innobackupex-- user=root-- password=yhw0715--incremental-basedir=/backup/mysql/daily1/2017-06-07. 23-13-23 /-- incremental/backup/mysql/daily2

4. Restore full readiness

Innobackupex-- user=root-- password=yhw0715--apply-log-- redo-only / backup/mysql/full/2017-06-07 July 23-04-50

Restore increment 1:

Innobackupex-user=root-password=yhw0715--apply-log-redo-only / backup/mysql/full/2017-06-07-23-04-50--incremental-dir=/backup/mysql/daily1/2017-06-07-23-13-23 /

Recovery increment 2 (no need to add-- redo-only):

Innobackupex-- user=root-- password=yhw0715--apply-log / backup/mysql/full/2017-06-07 / 23-04-50--incremental-dir=/backup/mysql/daily2/2017-06-07 / 23-20-31

Apply-log the merged data as a whole

Innobackupex-- user=root-- password=yhw0715--apply-log / backup/mysql/full/2017-06-07-23-04-50

5. Copy the backup file to the data directory:

5.1.stop mysqld

/ etc/init.d/mysqld stop

5.2. Back up the original data directory

Mv / usr/local/mysql/data/usr/local/mysql/data_bak

5.3. Create a data directory

Mkdir / usr/local/mysql/data-p

5.4. Copy and backup to / usr/local/mysql/data

Innobackupex-- user=root-- password=yhw0715--copy-back / backup/mysql/full/2017-06-07-23-04-50

5.5. Set permissions

Chown-R mysql:mysql / usr/local/mysql/data

5.6. Start mysqld

/ etc/init.d/mysqld start

6. Test whether the recovery is successful

From the results of the following query, the data has been recovered successfully.

Mysql > select * from test where account='a5' or account='a6'

+-+

| | Account |

+-+

| | A5 |

| | a6 |

+-+

2 rows in set (0.00 sec)

7. Description of main parameters of innobackupex

-- user name of the user backup database

-- password backup user password

-- specify the my.cnf file path when default-file backup, if the default / etc/my.cnf is not specified

-- apply-log encapsulation of xtrabackup's-prepare parameter

-- redo-only is used with apply-log. In the backup recovery phase, when redoing logs, only committed logs are redone and rollback uncommitted logs are redone.

-- when copy-back does data recovery, copy the data file to the data directory of mysql at that time.

-- use-memory controls the amount of memory used by innodb instances when performing recovery operations.

-- slave-info is mainly used in scenarios where multiple slave libraries are added based on the existing slave library. With this parameter, there will be one more xtrabackup_slave_info file in the backup directory. The file content stores the master-slave information, binlog file and position of the existing slave library, which is used when the new slave library and the master library change master to.