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

In this issue, the editor will bring you about what the incremental backup recovery and restore mysql 5.6 based on percona xtrabackup 2.4.14 is like. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

Back up and restore mysql 5.6 using the percona xtrabackup 2.4.14 backup tool

Https://mp.weixin.qq.com/s?__biz=MzIwMjU2MjI1OQ==&mid=2247484161&idx=1&sn=4c4d0f4a6f134505fa38c5cfbabc4989&chksm=96dd8cbda1aa05aba6b21505aecaa21bfeef16fb351ee77a3dd8f0b450dae092d6d2ecc6c867&token=274104940&lang=zh_CN#rd

Since there are continuous business change operations in the production system database, this article is introduced as follows:

1, data of the database

Mysql > use zxydb

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > select * from t_go

+-+ +

| | a | b | |

+-+ +

| | 1 | 1 |

| | 2 | 2 |

| | 3 | 3 |

| | 5 | 5 |

+-+ +

4 rows in set (0.00 sec)

2. First full backup

[root@standbygtid back_full_dir] # mkdir-p / backfup_full_dir

3. Change the database after the first full backup

Mysql > use zxydb

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > insert into t_go select 8

Query OK, 1 row affected (0.01sec)

Records: 1 Duplicates: 0 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from t_go

+-+ +

| | a | b | |

+-+ +

| | 1 | 1 |

| | 2 | 2 |

| | 3 | 3 |

| | 5 | 5 |

| | 8 | 8 |

+-+ +

5 rows in set (0.00 sec)

4. Perform the first incremental backup based on the first full backup

-- get the last LSN of the log file in full backup

[root@standbygtid back_full_dir] # cd / backup_full_dir/

[root@standbygtid backup_full_dir] # more xtrabackup_checkpoints

Backup_type = full-prepared

From_lsn = 0

To_lsn = 28517559

Last_lsn = 28517559

Compact = 0

Recover_binlog_info = 0

[root@standbygtid backup_full_dir] #

-- build a directory for incremental backups

[root@standbygtid backup_full_dir] # mkdir-p / backup_incre_dir

-- the value followed by the incremental option is the directory where incremental backups are stored

The incremental-basedir option is followed by a baseline backup for building an incremental backup (that is, this is the basis for the former backup)

[root@standbygtid backup_full_dir] # innobackupex-defaults-file=/usr/my.cnf-uroot-psystem-incremental / backup_incre_dir-incremental-basedir=/backup_full_dir

5. Continue to make database changes

Mysql > insert into zxydb.t_go select 10

Query OK, 1 row affected (0.02 sec)

Records: 1 Duplicates: 0 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from zxydb.t_go

+-+ +

| | a | b | |

+-+ +

| | 1 | 1 |

| | 2 | 2 |

| | 3 | 3 |

| | 5 | 5 |

| | 8 | 8 |

| | 10 | 10 |

+-+ +

6 rows in set (0.00 sec)

6. Perform the second incremental backup based on the first incremental backup mentioned above

-- same as above, get the LSN of the last log file of the last backup

[root@standbygtid backup_full_dir] # cd / backup_incre_dir/

[root@standbygtid backup_incre_dir] # ll

Total dosage 4

Drwxr-x--- 7 root root 4096 November 4 17:53 2019-11-04 October 17-53-05

[root@standbygtid backup_incre_dir] # cd 2019-11-04 / 17-53-05 /

[root@standbygtid 2019-11-04 / 17-53-05] # ll

Total dosage 156

-rw-r- 1 root root 418 November 4 17:53 backup-my.cnf

Drwxr-x--- 2 root root 4096 November 4 17:53 completedb

-rw-r- 1 root root 114688 November 4 17:53 ibdata1.delta

-rw-r- 1 root root 44 November 4 17:53 ibdata1.meta

Drwxr-x--- 2 root root 4096 November 4 17:53 mysql

Drwxr-x--- 2 root root 4096 November 4 17:53 performance_schema

Drwxr-x--- 2 root root 4096 November 4 17:53 test

-rw-r- 1 root root 18 November 4 17:53 xtrabackup_binlog_info

-rw-r- 1 root root 120 November 4 17:53 xtrabackup_checkpoints

-rw-r- 1 root root 579 November 4 17:53 xtrabackup_info

-rw-r- 1 root root 2560 November 4 17:53 xtrabackup_logfile

Drwxr-x--- 2 root root 4096 November 4 17:53 zxydb

[root@standbygtid 2019-11-04 / 17-53-05] # more xtrabackup_checkpoints

Backup_type = incremental

From_lsn = 28517559

To_lsn = 28518260

Last_lsn = 28518260

Compact = 0

Recover_binlog_info = 0

[root@standbygtid 2019-11-04 / 17-53-05] #

[root@standbygtid 2019-11-04 / 17-53-05] # innobackupex-- defaults-file=/usr/my.cnf-uroot-psystem-- incremental / backup_incre_dir-- incremental-basedir=/backup_incre_dir/2019-11-04 / 17-53-05

7, it can be seen that the second incremental backup is based on the first incremental backup

[root@standbygtid backup_incre_dir] # pwd

/ backup_incre_dir

[root@standbygtid backup_incre_dir] # ll

Total dosage 12

Drwxr-x--- 7 root root 4096 November 4 17:53 2019-11-04 October 17-53-05

Drwxr-x--- 7 root root 4096 November 4 17:59 2019-11-04 October 17-59-40

[root@standbygtid backup_incre_dir] # cd 2019-11-04 17-59-40

[root@standbygtid 2019-11-04 / 17-59-40] # ll

Total dosage 140

-rw-r- 1 root root 418 November 4 17:59 backup-my.cnf

Drwxr-x--- 2 root root 4096 November 4 17:59 completedb

-rw-r- 1 root root 98304 November 4 17:59 ibdata1.delta

-rw-r- 1 root root 44 November 4 17:59 ibdata1.meta

Drwxr-x--- 2 root root 4096 November 4 17:59 mysql

Drwxr-x--- 2 root root 4096 November 4 17:59 performance_schema

Drwxr-x--- 2 root root 4096 November 4 17:59 test

-rw-r- 1 root root 18 November 4 17:59 xtrabackup_binlog_info

-rw-r- 1 root root 120 November 4 17:59 xtrabackup_checkpoints

-rw-r- 1 root root 6 00 November 4 17:59 xtrabackup_info

-rw-r- 1 root root 2560 November 4 17:59 xtrabackup_logfile

Drwxr-x--- 2 root root 4096 November 4 17:59 zxydb

[root@standbygtid 2019-11-04 / 17-59-40] #

From below, we can see that the data from the first incremental backup and the second incremental backup are connected, because the LSN of the log file is connected.

[root@standbygtid 2019-11-04 / 17-59-40] # more xtrabackup_checkpoints

Backup_type = incremental

From_lsn = 28518260-- this is the last LSN of the last backup

To_lsn = 28518547

Last_lsn = 28518547

Compact = 0

Recover_binlog_info = 0

[root@standbygtid 2019-11-04 / 17-59-40] #

8. Let's test that the database is damaged and see if we can restore the complete data based on the above incremental backup.

The database is running. Delete all the contents in the data file directory directly.

[root@standbygtid 2019-11-04 / 17-59-40] # cd / var/lib/mysql

[root@standbygtid mysql] # ll

The total dosage is 188500

-rw-rw---- 1 mysql mysql 56 November 4 15:53 auto.cnf

-rw-rw---- 1 mysql mysql 143 November 4 15:53 binlog.000001

-rw-rw---- 1 mysql mysql 143 November 4 17:42 binlog.000002

-rw-rw---- 1 mysql mysql 143 November 4 17:43 binlog.000003

-rw-rw---- 1 mysql mysql 558 November 4 17:56 binlog.000004

-rw-rw---- 1 mysql mysql 64 November 4 17:45 binlog.index

Drwxr-x--- 2 mysql mysql 4096 November 4 15:53 completedb

-rw-r- 1 mysql mysql 79691776 November 4 17:56 ibdata1

-rw-r- 1 mysql mysql 50331648 November 4 17:56 ib_logfile0

-rw-r- 1 mysql mysql 50331648 November 4 15:53 ib_logfile1

-rw-r- 1 mysql mysql 12582912 November 4 15:53 ibtmp1

Drwxr-x--- 2 mysql mysql 4096 November 4 15:53 mysql

Srwxrwxrwx 1 mysql mysql 0 November 4 17:45 mysql.sock

-rw- 1 mysql mysql 649 November 4 17:45 nohup.out

Drwxr-x--- 2 mysql mysql 4096 November 4 15:53 performance_schema

-rw-r- 1 mysql mysql 1945 November 4 17:45 standbygtid.err

-rw-rw---- 1 mysql mysql 5 November 4 17:45 standbygtid.pid

Drwxr-x--- 2 mysql mysql 4096 November 4 15:53 test

Drwxr-x--- 2 mysql mysql 4096 November 4 15:53 xtrabackup_backupfiles

-rw-r- 1 mysql mysql 23 November 4 15:53 xtrabackup_binlog_pos_innodb

-rw-r- 1 mysql mysql 544 November 4 15:53 xtrabackup_info

Drwxr-x--- 2 mysql mysql 4096 November 4 15:53 zxydb

[root@standbygtid mysql] # rm-rf *

9, full recovery is carried out first, but note that option parameters must be added

-- apply-log only rolls forward committed transactions, not uncommitted transactions (Note: because the database is in an intermediate state at this time, there may be committed and uncommitted transactions)

[root@standbygtid mysql] # innobackupex-defaults--file=/usr/my.cnf-uroot-psystem-apply-log-redo-only / backup_full_dir

10, restore the first incremental backup again

Option parameters are the same as above

-- read-only followed by full backup directory

-- incremental-dir followed by the first incremental backup directory

[root@standbygtid mysql] # innobackupex-- defaults--file=/usr/my.cnf-uroot-- psystem-- apply-log-- redo-only / backup_full_dir-- incremental-dir=/backup_incre_dir/2019-11-04 July 17-53-05

11, continue to restore the second incremental backup

[root@standbygtid mysql] # innobackupex-- defaults--file=/usr/my.cnf-uroot-- psystem-- apply-log-- redo-only / backup_full_dir-- incremental-dir=/backup_incre_dir/2019-11-04 million 17-59-40

12, and finally restore the entire database

The last recovery of the entire database does not use the above option parameter-read-only, indicating that uncommitted transactions are rolled back

[root@standbygtid mysql] # innobackupex-defaults--file=/usr/my.cnf-uroot-psystem-apply-log / backup_full_dir

13, close the database

14. Physical replication has been restored to the database's data directory / var/lib/mysql

[root@standbygtid backup_full_dir] # pwd

/ backup_full_dir

[root@standbygtid backup_full_dir] #

[root@standbygtid backup_full_dir] # cp-Rf * / var/lib/mysql

[root@standbygtid backup_full_dir] # chown-Rf mysql:mysql / var/lib/mysql

-clear some temporary files related to xtrabackup in the data file directory / var/lib/mysql

[root@standbygtid backup_full_dir] # cd / var/lib/mysql

[root@standbygtid mysql] # rm-rf xtrabackup_*

[root@standbygtid mysql] # nohup mysqld_safe-- user=mysql&

15, it can be seen that the database based on incremental backup is restored to normal.

Mysql > select * from zxydb.t_go

+-+ +

| | a | b | |

+-+ +

| | 1 | 1 |

| | 2 | 2 |

| | 3 | 3 |

| | 5 | 5 |

| | 8 | 8 |

| | 10 | 10 |

+-+ +

6 rows in set (0.00 sec)

This is how the incremental backup recovery and restore mysql 5.6 based on percona xtrabackup 2.4.14 is shared by the editor. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.