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

This article shares with you the content of the sample analysis of mysql incremental backup and breakpoint recovery scripts. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Brief introduction

Incremental backup means that after a full backup or the last incremental backup, each subsequent backup only needs to back up the files that have been added or modified compared with the previous one. This means that the objects of the first incremental backup are the files added and modified after the full backup, the objects of the second incremental backup are the files added and modified after the first incremental backup, and so on.

Purpose

To solve the problem of long time and slow recovery in full backup, incremental backup is adopted.

Characteristics

Excellent: no duplicate data, small spare parts, short time

Missing: need the last full backup and the incremental backup after the full backup to restore, need to restore the incremental backup one by one, the operation is tedious

Mode of realization

Incremental backup is achieved indirectly through the binary log of mysql:

The binary log holds all updated or possible updated data.

Binary logs start to be recorded when mysql starts, and new log files are recreated

The flush logs method needs to be executed regularly to recreate the log and generate a sequence of binary files.

Experimental environment:

A centos7 virtual machine with mysql5.7 database installed

Operation procedure:

I. incremental backup

1. Add binary logs to the configuration file

Vim / etc/my.cnf

[mysqld] # add under this module

Log-bin=mysql-bin # binary log

2. Restart the service and view the binary log

Systemctl restart mysqld.service

Cd / usr/local/mysql/data/

Mysqlbinlog-no-defaults mysql-bin.000001

3. Create databases and tables freely in the database as experimental templates.

4. Make a full backup of school database

Mysqldump-uroot-pabc123 school > / opt/school.sql

5. Refresh the log and generate a new log. Note: the previous database operation is written to the log number 000001, and the newly generated log of 000002 is empty.

Mysqladmin-uroot-pabc123 flush-logs # refresh log

Mysqlbinlog-no-defaults-base64-output=decode-rows-v mysql-bin.000001 # View 000001 Chronicle

Mysqlbinlog-- no-defaults-- base64-output=decode-rows-v mysql-bin.000002 # View 000002 Journal

#-- base64-output=decode-rows-v: avoid garbled code in the file, and the previous view command can also be used

6. Add a new mysql database operation, and then refresh the log for incremental backup

Use school

Mysql > indert into info (id,name,score) values

Mysql > delete from info where name='tom'; # misoperation

Mysql > insert into info (id,name,score) values

Mysqladmin-uroot-pabc123 flush-logs # refresh the log and generate 000003

# this incremental backup has been recorded in the 000002 log file

7. Delete info table

Mysql-uroot-pabc123-e'use school;drop table info;'

8. Restore a full backup, then restore an incremental backup

Mysql-uroot-pabc123 school < / opt/school.sql # full backup restore

Mysqlbinlog-- no-defaults mysql-bin.000002 | mysql- uroot-p

Then, a problem is exposed here: if there is a misoperation, there will be an error in restoring the backup, which will not achieve the ideal backup and achieve the real goal. At this point, you need a chain-breaking restore (a special use for restoring incremental backups), that is, only the correct database operations are restored at the time of restore.

II. Chain breakage recovery

Before the operation is broken and restored, you need to restore to a state that has not been restored by incremental backup, as shown in the figure

Based on point in time

2018-09-04 16:43:52 error operation time (from the beginning to the end of this point in time)

2018-09-04 16:44:32 correct operation time (start loading again from this point in time) start loading again from this point in time

Mysqlbinlog-- no-defaults-- base64-output=decode-rows-v mysql-bin.000002 # View 000002 Journal

Mysqlbinlog-- no-defaults-- stop-datetime='2018-09-04 16 stop-datetime='2018 43 stop-datetime='2018 52'/ usr/local/mysql/data/mysql-bin.000002 | mysql- uroot-p

Mysqlbinlog-- no-defaults-- start-datetime='2018-09-04 16 usr/local/mysql/data/mysql-bin.000002 44bureau 32'/ usr/local/mysql/data/mysql-bin.000002 | mysql- uroot-p

Based on location point

At 565.Last correct execution location-stop-position

The next correct execution location of at 667is start-position.

Mysqlbinlog-- no-defaults-- base64-output=decode-rows-v mysql-bin.000002 # View 000002 Journal

Mysqlbinlog-- no-defaults-- stop-position='565' / usr/local/mysql/data/mysql-bin.000002 | mysql- uroot-p

Mysqlbinlog-- no-defaults-- start-position='667' / usr/local/mysql/data/mysql-bin.000002 | mysql- uroot-p

Thank you for reading! This is the end of this article on "sample analysis of mysql incremental backup and breakpoint recovery scripts". I hope the above content can be of some help to you, so that 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.