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

Editor to share with you the mysql innobackupex backup and binlog log full recovery example analysis, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

Foreword:

With the full recovery of MySQL, we can restore the database to the point of failure with the help of full backup + binlog.

Backup can be hot backup and logical backup (mysqldump), as long as the backup and binlog are complete, you can achieve full recovery.

1. Prepare the experimental environment

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.6.25-log |

+-+

1 row in set (0.00 sec)

Mysql > create database com_rec

Query OK, 1 row affected (0.00 sec)

Mysql > use inc_rec

Database changed

Mysql > create table andy (id int)

Query OK, 0 rows affected (0.08 sec)

Mysql > insert into andy values (1), (2)

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

two。 Fully prepared

[root@mysql02 full] # innobackupex-defaults-file=/etc/my.cnf-user=root-password=oracle-port=3606 / xtrabackup/full/

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

170609 17:34:34 completed OK!

3. View the full build file

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

Total 4

Drwxr-x---. 6 root root 4096 Jun 9 17:34 2017-06-09 September 17-34-30

4. New data for simulating business

Mysql > insert into andy values (3), (4)

Query OK, 2 rows affected (0.14 sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

5. Incremental backup

-- create an incremental backup directory and empower it

[root@mysql02 full] # mkdir-p / xtrabackup/incr/

[root@mysql02 full] # chown-R mysql:mysql / xtrabackup/incr/

[root@mysql02 full] # ll / xtrabackup/

Total 8

Drwxr-xr-x. 3 mysql mysql 4096 Jun 9 03:53 full

Drwxr-xr-x. 2 mysql mysql 4096 Jun 9 04:00 incre

-- officially start incremental backup

[root@mysql02 full] # innobackupex-defaults-file=/etc/my.cnf-user=root-password=oracle-incremental\

-- incremental-basedir=/xtrabackup/full/2017-06-09 / 17-34-30 / / xtrabackup/incr/

# # the following is incremental backup output

. . . Omit

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

170609 17:36:46 completed OK!

6. Then simulate the new business, the record is saved in binlog, but will not exist in any backup, this record is used to verify full recovery

Mysql > insert into andy values (5)

Query OK, 1 row affected (0.00 sec)

7. Write down the position points after the operation

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | binlog.000005 | 1104 | |

+-+

1 row in set (0.00 sec)

8. Toggle binlog Log

Mysql > flush logs

Query OK, 0 rows affected (0.00 sec)

9. Use the binlog events command to view one of our last insert records

Mysql > show binlog events in 'binlog.000005'

+-+

| | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | |

+-+

| | binlog.000005 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.25-log, Binlog ver: 4 |

| | binlog.000005 | 120 | Query | 1 | 221 | drop database inc_rec |

| | binlog.000005 | 221 | Query | 1 | 324 | create database com_rec |

| | binlog.000005 | 324 | Query | 1 | 430 | use `com_ rec`; create table andy (id int) |

| | binlog.000005 | 430 | Query | 1 | 515 | BEGIN |

| | binlog.000005 | 515 | Query | 1 | 625 | use `com_ rec`; insert into andy values (1), (2) |

| | binlog.000005 | 625 | Xid | 1 | 656 | COMMIT / * xid=67 * / | |

| | binlog.000005 | 656 | Query | 1 | 741 | BEGIN |

| | binlog.000005 | 741 | Query | 1 | 851 | use `com_ rec`; insert into andy values (3), (4) |

| | binlog.000005 | 851 | Xid | 1 | 882 | COMMIT / * xid=81 * / | |

| | binlog.000005 | 882 | Query | 1 | 967 | BEGIN |

| | binlog.000005 | 967 | Query | 1 | 1073 | use `com_ rec`; insert into andy values (5) |

| | binlog.000005 | 1073 | Xid | 1 | 1104 | COMMIT / * xid=96 * / | |

| | binlog.000005 | 1104 | Rotate | 1 | 1148 | binlog.000006;pos=4 |

+-+

14 rows in set (0.00 sec)

10. Check the location of binlog and datadir to prevent misoperation of binlog during mv datadir and affect recovery (binlog and datadir must be placed separately)

Mysql > show variables like'% log_bin%'

+-+

| | Variable_name | Value |

+-+

| | log_bin | ON |

| | log_bin_basename | / data/mysql/binarylog/binlog |

| | sql_log_bin | ON |

+-+

6 rows in set (0.00 sec)

Mysql > show variables like'% datadir%'

+-+ +

| | Variable_name | Value |

+-+ +

| | datadir | / data/mysql/ |

+-+ +

1 row in set (0.00 sec)

11. Restore

11.1 first do a complete apply. Note that-- redo-only is used at this time.

[root@mysql02 full] # innobackupex-- defaults-file=/etc/my.cnf-- user=root-- apply-log-- redo-only / xtrabackup/full/2017-06-09 / 17-34-30 /

170609 04:19:30 completed OK!

11.2 when restoring incremental backup sets:-- there is no-- redo-only at this time. If there are multiple additions, only the last one does not need to be specified-- redo-only.

[root@mysql02 full] # innobackupex-- defaults-file=/etc/my.cnf-- user=root-- apply-log / xtrabackup/full/2017-06-09 / 17-34-30 /-- incremental-dir=/xtrabackup/incr/2017-06-09 / 17-36-39 /

170609 04:24:45 completed OK! # the result shows that completed OK is a complete success

Description: / xtrabackup/full/2017-06-09 / 17-34-30 / is a full base directory and incremental-dir is an incremental backup directory

twelve。 Close the instance to be restored

[root@mysql02 data] # / etc/init.d/mysql stop-p3306

Netstat-nltp | grep mysql | grep 3606

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

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

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

14. Copy the restored file to the original data location

[root@mysql02 full] # innobackupex-- defaults-file=/etc/my.cnf-- user=root-- copy-back / xtrabackup/full/2017-06-09 / 17-34-30 /

170609 04:33:06 completed OK! # the result shows that completed OK is a complete success

Description: / xtrabackup/full/2017-06-09 / 17-34-30 / is a full base directory

15. 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)

-- return binlog log mv to its original location (no action is required if binlog is not under datadir)

[root@mysql02 xtrabackup] # ll / data/mysqlbak/binarylog/

Total 28

-rw-rw----. 1 mysql mysql 164 Jun 9 06:12 binlog.000001

-rw-rw----. 1 mysql mysql 386 Jun 9 06:14 binlog.000002

-rw-rw----. 1 mysql mysql 143 Jun 9 06:53 binlog.000003

-rw-rw----. 1 mysql mysql 143 Jun 9 07:35 binlog.000004

-rw-rw----. 1 mysql mysql 1148 Jun 9 17:45 binlog.000005

-rw-rw----. 1 mysql mysql 143 Jun 9 17:48 binlog.000006

-rw-rw----. 1 mysql mysql 216 Jun 9 17:45 binlog.index

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

[root@mysql02 xtrabackup] # ll / data/mysql/binarylog/

Total 28

-rw-rw----. 1 mysql mysql 164 Jun 9 06:12 binlog.000001

-rw-rw----. 1 mysql mysql 386 Jun 9 06:14 binlog.000002

-rw-rw----. 1 mysql mysql 143 Jun 9 06:53 binlog.000003

-rw-rw----. 1 mysql mysql 143 Jun 9 07:35 binlog.000004

-rw-rw----. 1 mysql mysql 1148 Jun 9 17:45 binlog.000005

-rw-rw----. 1 mysql mysql 143 Jun 9 17:48 binlog.000006

-rw-rw----. 1 mysql mysql 216 Jun 9 17:45 binlog.index

[root@mysql02 data] # chown-R mysql:mysql / data/mysql

16. Start the restored instance

Mysqld_safe-- defaults-file=/etc/my.cnf &

17. Login check

[root@mysql02] # mysql-uroot-poracle

Mysql > use com_rec

Mysql > select * from andy

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

| 4 | > the recovery was successful, but it was not restored to the latest, id=5 is missing, and the 'Inbinlog' record has not been restored.

+-+

18. Use binlog for full recovery

[root@mysql02] # cd / xtrabackup/incr/2017-06-09 / 17-36-39 /

-- get the location of the binlog from innobackupex

[root@mysql02 2017-06-09 / 17-36-39] # more xtrabackup_binlog_info

Binlog.000005 882

-- the latest appended using mysqlbinlog

[root@mysql02 2017-06-09 / 17-36-39] # mysqlbinlog-- start-position=882-- stop-position=1104 / data/mysql/binarylog/binlog.000005 | mysql-uroot-poracle

Add:

This is true, and binlog in row mode can also be implemented in this way. But this has several drawbacks.

a. What if the parsed binlog reports an error during execution? Is it enforced directly by adding-f?

b. How to stop in the middle of the execution and keep running next time? For example, I want to adjust the parameters of MySQL (need to restart) and continue to run?

c. Can only be executed in a single thread. And mysqlbinlog parsing is performed through the pipeline, which has a relatively high performance overhead.

19. Verify that you can see the last record and be restored

Mysql > select * from andy

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

| | 4 |

| | 5 |

+-+

5 rows in set (0.00 sec)

The above is all the contents of the article "sample Analysis of innobackupex backup and binlog Log full recovery in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, 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.