Preface

"Delete library and run" is a topic that programmers often talk about. Today, I will teach you how to delete!Library!Run!Road!

Just kidding, the topic of this article today is how to use Mysql's built-in Binlog log to recover mistakenly deleted data. After reading this article, you can see:

• What is MySQL's binlog log log?What is it usually used for?
• Simulate an operation that deletes data by mistake, and use binlog logs to recover the deleted data.

The idea of writing this article was that once I really nearly deleted a table from the test database, I was sweating cold.The reason is that in the configuration of Spring JPA, there is a spring.jpa.properties.hibernate.hbm2ddl.auto=create-drop for generating tables from the model class each time hibernate is loaded, but the tables are automatically deleted as soon as sessionFactory is closed.This can't be configured freely, just drop your existing table!

Okay, to get back to the point, this article is to reassure you that even if MySQL deletes by mistake, it will basically be able to rescue.Especially in large companies, data can't be deleted if you want to delete it. There are numerous privileges/backups blocking you.

text

Introduction to Binlog

Looking at the definition of binlog above, you should also be able to roughly infer the three main uses of binlog:

• Restore Data: Key Points to Say Today
• Database replication: A master-slave database is a database that passes binlog to a slave library. There are two threads from the library, one I/O thread, one SQL thread. The I/O thread reads the binlog content passed from the master library and writes it to the relay log. The SQL thread reads the content from the relay log and writes it to the slave database.
• Auditing: Users can audit the information in the binary log to see if there is an injection attack on the database.

So to be able to recover your data, you first have to open Mysql's binlog, which is not turned on by default in the single Mysql you normally install yourself.Let's step by step practice how to open Binlog logs on your server.

Open Binlog in MySQL

First enter the database console and run the command:

mysql> show variables like'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
+---------------------------------+-------+
5 rows in set (0.00 sec)

You can see that our binlog is off, all OFF.Next we need to modify the Mysql configuration file to execute the command:

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Add at the end of the file:

log-bin=/var/lib/mysql/mysql-bin

Save the file and restart the mysql service:

sudo service mysql restart

When the restart is complete, review the status of mysql:

systemctl status mysql.service

At this point, if your mysql version is 5.7 or higher, you will get an error:

Jan 06 15:49:58 VM-0-11-ubuntu mysqld[5930]: 2020-01-06T07:49:58.190791Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
Jan 06 15:49:58 VM-0-11-ubuntu mysqld[5930]: 2020-01-06T07:49:58.190839Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
Jan 06 15:49:58 VM-0-11-ubuntu mysqld[5930]: 2020-01-06T07:49:58.359713Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (se
Jan 06 15:49:58 VM-0-11-ubuntu mysqld[5930]: 2020-01-06T07:49:58.361395Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.28-0ubuntu0.16.04.2-log) starting as process 5930 ...
Jan 06 15:49:58 VM-0-11-ubuntu mysqld[5930]: 2020-01-06T07:49:58.363017Z 0 [ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server
Jan 06 15:49:58 VM-0-11-ubuntu mysqld[5930]: 2020-01-06T07:49:58.363747Z 0 [ERROR] Aborting
Jan 06 15:49:58 VM-0-11-ubuntu mysqld[5930]: 2020-01-06T07:49:58.363922Z 0 [Note] Binlog end
Jan 06 15:49:58 VM-0-11-ubuntu mysqld[5930]: 2020-01-06T07:49:58.364108Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
Jan 06 15:49:58 VM-0-11-ubuntu systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE

You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server

Previously, our configuration should be possible for versions below 5.7.For higher versions, however, we need to specify the server-id.

If you are not deploying Mysql distributed, this server-id can be given a random number.

server-id=123454

Simulate data deletion and recovery

1. Start by creating a new database, mytest, and a new table, table1. See the SQL code below for the structure

CREATE DATABASE `test` ;

USE `test`;

DROP TABLE IF EXISTS `table1`;

CREATE TABLE `table2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1. Insert two data, one for (1,'A','2,'B')

INSERT INTO `table1` VALUES (1,'A'),(2,'B');

1. Let's look at the status of the binlog log, using show master status

mysql> show master status
    -> ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      690 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set

Bilog log feature: Whenever we restart MySQL once, a binlog file is automatically generated. Of course, we can also refresh the binlog file manually and create a new binlog file by flush logs.In fact, the flush logs operation is also called when the server restarts.

As you can see from the code in the figure above, we are now using mysql-bin.0000001, and this file is now being logged to 690 lines.

1. Then use flush logs to actively refresh the binlog once

mysql> flush logs;
Query OK, 0 rows affected

mysql> show master status
    -> ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set

You can see that the log file is now in the mysql-bin.000002 file, at 154.That is, we actively refreshed the binlog once and generated a new 000002, while 000001 has been archived and will not be written to the new log.

1. Next we're inserting two pieces of data

insert into table1 values (3,'C');
insert into table1 values (4,'D');

mysql> select * from table1;
+----+----+
| id |name|
+----+----+
|  1 | A  |
|  2 | B  |
|  3 | C  |
|  4 | D  |
+----+----+

1. Now that we have four pieces of data, we flush the logs again, archive the mysql-bin.000002 log, and open the new mysql-bin.000003 log, so that each time we insert data, it is independent of each other.In practice, binlog s are more complex and simplified to make them easier to understand.

mysql> flush logs;
Query OK, 0 rows affected

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set

1. Then we delete the data with id 4 (4, D) and refresh the binlog again, so there is only one deletion in binlog.000003.

mysql> delete from table1 where id = 4;
Query OK, 1 row affected

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      423 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set

mysql> flush logs;
Query OK, 0 rows affected

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set

1. Let's take a closer look at two binlogs, mysql-bin.00002 and mysql-bin00003, using the command show binlog events in'mysql-bin.000003'

mysql> show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                   |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| mysql-bin.000003 |   4 | Format_desc    |    123456 |         123 | Server ver: 5.7.28-0ubuntu0.16.04.2-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids |    123456 |         154 |                                                        |
| mysql-bin.000003 | 154 | Anonymous_Gtid |    123456 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| mysql-bin.000003 | 219 | Query          |    123456 |         293 | BEGIN                                                  |
| mysql-bin.000003 | 293 | Table_map      |    123456 |         343 | table_id: 108 (test.table1)                              |
| mysql-bin.000003 | 343 | Delete_rows    |    123456 |         392 | table_id: 108 flags: STMT_END_F                        |
| mysql-bin.000003 | 392 | Xid            |    123456 |         423 | COMMIT /* xid=39 */                                    |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
7 rows in set

mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                   |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |    123456 |         123 | Server ver: 5.7.28-0ubuntu0.16.04.2-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids |    123456 |         154 |                                                        |
| mysql-bin.000002 | 154 | Anonymous_Gtid |    123456 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| mysql-bin.000002 | 219 | Query          |    123456 |         293 | BEGIN                                                  |
| mysql-bin.000002 | 293 | Table_map      |    123456 |         343 | table_id: 108 (test.table1)                              |
| mysql-bin.000002 | 343 | Write_rows     |    123456 |         390 | table_id: 108 flags: STMT_END_F                        |
| mysql-bin.000002 | 390 | Xid            |    123456 |         421 | COMMIT /* xid=34 */                                    |
| mysql-bin.000002 | 421 | Anonymous_Gtid |    123456 |         486 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| mysql-bin.000002 | 486 | Query          |    123456 |         560 | BEGIN                                                  |
| mysql-bin.000002 | 560 | Table_map      |    123456 |         610 | table_id: 108 (test.table1)                              |
| mysql-bin.000002 | 610 | Write_rows     |    123456 |         659 | table_id: 108 flags: STMT_END_F                        |
| mysql-bin.000002 | 659 | Xid            |    123456 |         690 | COMMIT /* xid=35 */                                    |
| mysql-bin.000002 | 690 | Rotate         |    123456 |         737 | mysql-bin.000003;pos=4                                 |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
13 rows in set

Although there are many seemingly complex instructions, it is not difficult to see that there are two write operations in 02 and one delete operation in 03.

The complete log of an insert operation is as follows:

| mysql-bin.000002 | 154 | Anonymous_Gtid |    123456 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| mysql-bin.000002 | 219 | Query          |    123456 |         293 | BEGIN                                                  |
| mysql-bin.000002 | 293 | Table_map      |    123456 |         343 | table_id: 108 (test.table1)                              |
| mysql-bin.000002 | 343 | Write_rows     |    123456 |         390 | table_id: 108 flags: STMT_END_F                        |
| mysql-bin.000002 | 390 | Xid            |    123456 |         421 | COMMIT /* xid=34 */                                    |

1. Our goal is to recover the mistakenly deleted data by replaying two inserted records of the binlog.000002 log without having to take into account the operation of binlog.000003 (because deletion is a mistake)

So now you can understand why we refresh binlog so often. Of course, in a real online environment, we must export binlog, carefully filter out the errors, eliminate them, and then run binlog.

In this article, we will only do one operation to restore two insertions, executing the statement:

sudo mysqlbinlog /var/lib/mysql/mysql-bin.000002 --start-position 154 --stop-position 690 | mysql -uroot -p mytest

Note: The path/var/lib/mysql/mysql-bin.000002 filled in here needs to be specific to your binlog directory. Most articles on the web are written only to mysql-bin.000002. If you are not in the directory, the MySQL binlog command does not automatically locate the path where the binlog is located.

Parameter description:

--start-datetime: Read from binary logs specifying a time stamp equal to or later than the local computer

--stop-datetime: Read from binary logs specifying a time value less than or equal to the time stamp of the local computer as described above

--start-position: Reads the specified position event location from the binary log as a starting point.

--stop-position: Reads the specified position event location from the binary log as an event to

After successful execution, look at table table1 again and you can see that two new IDS = 3 and 4 have been inserted.The recovery was successful.

mysql> select * from table1;
+----+----+
| id |name|
+----+----+
|  1 | A  |
|  2 | B  |
|  3 | C  | 
|  3 | C  |
|  4 | D  |
+----+----+
6 rows in set

Extended thinking

Under what circumstances can Binlog not recover data?

epilogue

Don't be afraid to delete the library and run. Other developers and maintenance are waiting to recover your data. What a good phone training would it be?

Of course, after reviewing the principle of binlog log data recovery, I hope you can add the command to refresh the binlog log in the script that backs up the database regularly in the future. If you lose data one day, you can take out the binlog data for the same day to restore independently, so that it can be clearly distinguished and recovery efficiency can be accelerated.

Reference resources

https://www.cnblogs.com/rjzheng/p/9721765.html

https://blog.csdn.net/king_kgh/article/details/74890381

https://www.jianshu.com/p/564fcc2b5e31

https://blog.csdn.net/king_kgh/article/details/74833539

Pay attention to me

I'm a back-end development engineer.

Mainly focus on back-end development, data security, crawling, Internet of Things, edge computing and other directions, welcome to communicate.

Platforms can find me

• WeChat Public Number: Backend Technology Talk
• Github: @qqxx6661
• CSDN: @Backend Technology Talk
• Knowing: @Backend Technology Talk
• Short Book: @Backend Technology Talk
• Nuggets: @Backend Technology Talk

Main content of original blog

• Articles related to back-end development
• Java Interview Points Review Manual
• Design Mode/Data Structure
• Leetcode/Swordfinger offer Algorithmic Question Analysis
• Getting Started with SpringBoot/SpringCloud Series
• Crawler related technical articles
• Gossip / Good Book Sharing / Personal Interest

Personal Public Number: Backend Technology Talk

If the article helps you, you might as well collect it, coin it, forward it, and look at it~