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

Editor to share with you an example of MySQL incremental backup and recovery, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Article catalogue

1. MySQL incremental backup

1.1 Why use incremental backup

1.2 Features of incremental backup

1. The concept of incremental backup

two。 Incremental backup exampl

Second, MySQL incremental recovery

4.1 Point-in-time recovery

4.1 location-based operation

1. Incremental recovery scenario

two。 Recovery steps for data changed after losing a full backup

3. Recovery steps for losing all data after a full backup

4. Recovery based on time and location

5. The idea of specifying Enterprise backup Strategy

1. MySQL incremental backup

Incremental backup can reduce the size of backup files on the basis of full backup, thus speeding up backup and recovery.

1. The concept of incremental backup

1.1 Why use incremental backup

The previous section mentioned that there are two ways to make a full backup, one is to package data files using tar, and the other is

Use mysqldump for a full backup

The problem of full backup is easy to see, every time all the data contents are backed up, there is a lot of duplicate data in the backup data, and the time of full backup and recovery is very long.

To solve the problem of full backup is to use incremental backup, which is to back up files or contents that have been added or changed since the last backup.

1.2 Features of incremental backup

The advantage of incremental backup is that there is no duplicate data, the backup quantity is small, and the time is short.

The disadvantage is also obvious that all incremental backups after the last full backup and full backup can be restored, and the operation is cumbersome to restore all incremental backups one by one.

MySQL does not provide a direct incremental backup method, but it can be done through MySQL's binary log (binary

Logs) indirect implementation of incremental backup

The significance of binary logs for backups is as follows:

The binary log holds all operations that update or possibly update the database.

The binary log starts to be recorded after the MySQL server is started, and a new log file is recreated after the file reaches the size set by max_binlog_size or after the flush logs command is received

You only need to execute the flush logs method regularly to recreate new logs, generate binary file sequences, and save these logs to a safe place in time to complete an incremental backup for a period of time.

two。 Incremental backup exampl

Enable binary logging function

Vim / etc/ my.cnf. [mysqld] log-bin=mysql-binbinlog_format = MIXED# specifies the recording format of binary log (binlog) for MIXEDsystemctl restart mysqld.service# restart service cd / usr/local/mysql/datals-l / usr/local/mysql/data/mysql-bin.*# View binary log # there are three different record formats: STATEMENT (based on SQL statement), ROW (based on line), MIXED (mixed mode) # default format is STATEMENT

Select a period of time with lighter server load every week, or a period of less user access for backup

Mysqldump-uroot-p123123 SCHOOL CLASS01 > / opt/SCHOOL_CLASS01_$ (date +% F) .sql # full backup of the table mysqldump-uroot-p123123-all-databases SCHOOL > / opt/SCHOOL_$ (date +% F). SQL # full backup of the library crontab-e# can also use planned tasks to execute 303 * * 3 mysqldump-uroot-p123123 SCHOOL CLASS01 > / opt/SCHOOL_CLASS01_$ (date +% F). Sql30 3 * * 3 Mysqldump-uroot-p123123-- all-databases SCHOOL > / opt/SCHOOL_$ (date +% F) .sql makes a full backup of the database and tables at 3:00 every Wednesday

Incremental backup operations can be performed every day to generate new binary log files, so that after inserting new data, the new binary files correspond to the changed contents of the database.

Ls / usr/local/mysql/datamysqladmin-uroot-p123123 flush-logs

Insert new data to simulate the addition or change of data

Use SCHOOL;insert into CLASS01 values (3); insert into CLASS01 values (4); select * from CLASS01

Generate a new binary file and view its contents

Cd / usr/local/mysql/data/lsmysqladmin-uroot-p123123 flush-logs

Cp mysql-bin.000002 / opt/# copies the binary file 02 that records the changes to / opt directory cd / opt/lsmysqlbinlog-- no-defaults-- base64-output=decode-rows-v / opt/mysql-bin.000002# uses 64-bit encoding mechanism to decode, and reads the details by line

Second, MySQL incremental recovery

Incremental recovery is more tedious than full recovery

Each incremental backup is a separate individual, the data is not duplicated, and needs to be controlled more accurately.

1. Incremental recovery scenario

When data is sent incorrectly, you should choose whether to use full backup recovery or incremental backup according to the actual situation.

The scenarios for incremental backups are:

Artificial SQL statement destroys the database

Sending a system failure before the next full set results in the loss of database data

In the master-slave architecture, the master database data sent a failure

According to the situation of data loss, there are two categories:

Only the data that was changed after the full backup was lost

Lose all data after full backup

two。 Recovery steps for data changed after losing a full backup

When the changed data is lost after a full backup, all incremental backup files after a full backup need to be restored one by one

The steps are as follows:

Mysql- uroot-p123123use SCHOOL;delete from CLASS1 where id=3;delete from CLASS1 where id=4;# deletes the inserted two pieces of data, simulates the failure of data loss after a full backup select * from CLASS01;# checks quitmysqlbinlog-- no-defaults / opt/mysql-bin.000002 | mysql- uroot-p1231212 uses binaries for recovery operation mysql- uroot-p123123-e "select * from SCHOOL.CLASS01;" # check whether the table contents are restored

3. Recovery steps for losing all data after a full backup

After the full backup and incremental backup, all the data is lost, and the full backup and all incremental backup files need to be restored one by one.

The steps are as follows:

Mysql-uroot-p123123use SCHOOL;drop table CLASS01;# deletes the entire table directly, assuming that all data is lost after a full backup-quitmysql-uroot-p123123 SCHOOL

< /opt/SCHOOL_CLASS01_2021-02-06.sqlmysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"#进行完全备份后查看一下mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p123123#增量备份mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"

4. Recovery based on time and location

Binary logs can be used to achieve time-and location-based recovery, for example, when a table is deleted due to misoperation, full recovery is useless.

Because there are misoperation statements in the log, what we need is to restore to the state before the misoperation, then skip the misoperation statement, and then restore the later operation statement.

4.1 Point-in-time recovery

Recovery based on a point in time is to import a binary file at a starting time into the database, thus skipping an error point in time to achieve data recovery.

Use mysqlbinlog plus the-- stop-datetime option to indicate at which time it ends, and subsequent misoperated statements will not be executed

The-start-datetime option indicates the execution of the following statement

If you use them together, you can skip misoperated statements and complete the recovery work.

It is important to note that the date format saved in the binary file needs to be adjusted to be split with "-"

# restore the data of the user "wangsan" instead of "wangsi" mysql- uroot-p123123-e "truncate table SCHOOL.CLASS01;" mysql- uroot-p123123-e "select * from SCHOOL.CLASS01;" mysqlbinlog-no-defaults-- stop-datetime='2021-02-06 15mysql- uroot 58 58 opt/mysql-bin.000002 | mysql- uroot-p123123mysql-uroot-p123123-e "select * from SCHOOL.CLASS01;"

# recover the data of "wangsi" mysqlbinlog-- no-defaults-- start-datetime='2021-02-06 15 no-defaults 58 opt/mysql-bin.000002 39'/ opt/mysql-bin.000002 | mysql- uroot-p

4.1 location-based operation

Location-based recovery is the use of point-in-time recovery

It may occur at a point in time that there are both correct and wrong operations. Location-based is a more accurate way to recover.

Mysqlbinlog-- no-defaults-- stop-position='609' / opt/mysql-bin.000002 | mysql- uroot-p # uses 64-bit encoding mechanism to decode and read the details of binary file 02 (incremental backup) by line. Slightly

# only restore the data before "1810", that is, do not restore the data of "wangsi" mysql- uroot-p123123-e "select * from SCHOOL.CLASS01;" mysql- uroot-p123123-e "truncate table SCHOOL.CLASS01;" mysql- uroot-p123123-e "select * from SCHOOL.CLASS01;" mysqlbinlog-no-defaults-- stop-position='1810' / opt/mysql-bin.000002 | mysql- uroot-pmysql-uroot-p123123-e "select * from SCHOOL.CLASS01;"

# restore only the data of "wangsi" and skip the data recovery of "wangsan", that is, there is only the fourth record mysql- uroot-p123123-e "select * from SCHOOL.CLASS01;" mysqlbinlog-no-defaults-- start-position='1810' / opt/mysql-bin.000002 | mysql- uroot-p123123mysql-uroot-p123123-e "select * from SCHOOL.CLASS01;"

5. The idea of specifying Enterprise backup Strategy

Specify that the enterprise backup strategy should be based on the actual frequency of reading and writing of the enterprise database and the importance of the data.

If the data is updated frequently, more frequent backups should be carried out.

If the data is more important, make a backup when there is an appropriate update

Complete the database during periods of low pressure, such as once a week, and then add it every day.

According to the size of the company, small and medium-sized companies can have it once a day, and large companies can have it once a week and add it once a day, and try their best to realize the master-slave replication structure for the enterprise.

The above is all the contents of the article "examples of MySQL incremental backup and recovery". 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.