Shulou(Shulou.com)06/01 Report--

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.

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.