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

Description:

An incremental backup is a backup of files or contents that have been added or changed since the last backup. However, MySQL does not provide a direct incremental backup method, but incremental backup can be achieved indirectly through the binary log of MySQL. The following is about the operation method for testing.

.

.

Experimental environment: a CentOS7 virtual machine with MySQL 5.7database

.

.

The experimental steps are as follows:

.

.

1. Enter the configuration file to add a binary log

Vim / etc/my.cnf

[mysqld]

Insert a binary log into it

Log_bin=mysql-bin # binary log

.

.

2. Then restart the database and check the log file directory

.

.

3. Create a new database, create a new data table in it, and write data

.

.

4. Regenerate a log file so that the previous steps are saved in the first binary file

Mysqladmin-u root-p flush-logs# regenerates binaries

.

.

5. Delete the database you just created, and then use the restore command to restore the database

Mysqlbinlog-- no-defaults mysql-bin.000001 | mysql- u root-p # log file recovery

.

.

6. You can see that the database has been restored.

.

.

7. Next, test the recovery after the misoperation (two methods, one is to use the time point, the other is to use the location point) to perform the time point operation first.

Insert data into a table

Next, delete a piece of data in the table (misoperation)

And then insert another piece of data.

In this way, a command of misoperation has been overwritten.

.

.

8. Regenerate a binary log

Mysqladmin-u root-p flush-logs# create a new log

.

.

9. Mysqlbinlog-- no-defaults-- base64-output=decode-rows-v mysql-bin.000002#64 bit decoding to view log files, anti-clutter code

Two time points need to be found in it.

(1) the time point when the data is deleted

(2) the correct operation time point of the next step after deletion

.

.

10. After finding two time points, use these two time points for recovery

Mysqlbinlog-- no-defaults-- stop-datetime='2018-09-03 16 stop-datetime='2018 38 stop-datetime='2018 08' / usr/local/mysql/data/mysql-bin.000002 | mysql- u root-p# restore to the wrong time

Mysqlbinlog-- no-defaults-- start-datetime='2018-09-03 16 start-datetime='2018 39 start-datetime='2018 08' / usr/local/mysql/data/mysql-bin.000002 | mysql- u root-p # recover from the next correct point in time

.

.

11. In this way, the mistakenly deleted data can be restored.

.

.

12. Let's restore it according to the location point.

Also restore the data to erroneous deletion

.

.

13. Mysqlbinlog-- no-defaults-- base64-output=decode-rows-v mysql-bin.000002 # 64-bit decoding to view log files, anti-clutter code

After finding the command executed at the error point, copy the last location that could be executed correctly (at is followed by the location number)

Then find the next executable command, and copy the same location where the last one can be executed correctly

.

.

14. Using location points for recovery

Mysqlbinlog-- no-defaults-- stop-datetime='1950' / usr/local/mysql/data/mysql-bin.000002 | mysql- u root-p # restore to the wrong point in time

Mysqlbinlog-- no-defaults-- start-datetime='2223' / usr/local/mysql/data/mysql-bin.000002 | mysql- u root-p # recover from the next correct point in time

Finally, check the results and the test is complete.

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.