At work, if we delete data or databases by mistake, do we have to run away? I don’t think so, programmers must learn to save themselves, undetected data recovery.

In mysql, we know that binlog logs record all our operations to the database, so binlog logs are a powerful tool to save ourselves.

The next step is to open the way for programmers to save themselves.

If you want to save yourself, you have to make sure you have a binlog. Here’s how to make sure you have a binlog.

1. Check whether binlog is enabled for the database

show variables like 'log_%';
Copy the code

Check the area circled in red. If it is ON, it is enabled. Otherwise, it is disabled. If it is not enabled, you need to do the following.

2. Enable binlog

If binlog is not enabled, you need to enable binlog, which is set in the MySQL configuration file. If you are running a WindowNS computer, find the my.ini file, and on Unix, find the my.cnf file. Modify or configure the following parameters in the file:

Log-bin =D:\ mysql-binlog \mysql-bin =mixedCopy the code

Binlog-format has three options:

  • STATMENT: Each SQL statement that modifies data is recorded in a binlog.
  • ROW: Does not record the context information of each SQL statement, but only records which records are modified.
  • MIXED: a MIXED use of the above two modes. Generally, STATEMENT mode is used to store binlogs for replication. For operations that cannot be copied in STATEMENT mode, ROW mode is used to store binlogs.

3. Restart the MySQL server

4. Confirm that the binlog function is enabled successfully.

Ok, after the above steps, we can learn how to save ourselves. We use three scenarios to demonstrate how to save ourselves in the case of mistakenly deleting data, mistakenly deleting tables and mistakenly deleting libraries.

Before we dive into the actual scenario, let’s prepare the database, tables, and data for the demo.

1. Create a databasepingtouge

create database pingtouge;
Copy the code

Create table student

create table student(
   id INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(100) NOT NULL.num VARCHAR(40) NOT NULL,
   PRIMARY KEY (id))ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

3. Insert data

insert into student(name.num)values('Joe'.'1234');
insert into student(name.num)values('bill'.'1235');
Copy the code

After you have the data, you can officially enter the scene. Are you ready?

Scenario 1: A piece of data is deleted

Select * from student where id=1; What to do?

Insert this data manually from memory? The only people who can remember are geniuses. Binlog is king.

Run the show master status command to view the latest binlog file.

Run show binlog events in ‘mysql-bin.000043’; Command to view the binlog file, as shown in the following figure:

The binlog log records all of our operations to the database, including the offsets before and after the statement commit, which will be used for data recovery.

Before we dive into data recovery, let’s take a look at a tool provided by MySQL: mysqlbinlog. Mysqlbinlog is used to operate on the binlog file, which is used for data recovery.

Use mysqlbinlog to restore binary log files in the following format:

Mysqlbinlog Log file- start - the position of the deviation of the initial position, the offset of the end of the stop - position location | mysql -u root -p database name;
Copy the code

In the binlog file, we find the start and end offsets of the last operation on student (id=1) before deleting. In the figure above, I have indicated that the start offsets are 710 and the end offsets are 996. With these two parameters, we can use the mysqlbinlog command to restore the data.

Perform:

 mysqlbinlog d:\Mysql-binlog\mysql-bin.000043 --start-position 710 --stop-position 996 | mysql -u root -p pingtouge;
Copy the code

If the command is executed successfully, run the select * from student command again to check whether the data is recovered.

Scenario 2: Data tables are deleted by mistake

Delete table student by mistake.

If a table is deleted by mistake, you can also use the binlog log to restore the table. After all, the binlog log records all the operations on MySQL. Just like restoring a single piece of data, we also need to find the start offset and end offset in the binlog file.

Unlike a single piece of data, for table offsets, the start offset is the start offset before the table is created, and the end offset is the last end offset before the database is deleted. As shown below:

 mysqlbinlog d:\Mysql-binlog\mysql-bin.000043 --start-position 393 --stop-position 1997 | mysql -u root -p pingtouge
Copy the code

Run the select * from student command again. You’ll see that our data is coming back.

Scenario 3: The database is deleted by mistake

So you deleted Pingtouge’s database and regretted it later. What can you do? Online, etc.

Do not panic, the almighty binlog can save you, as in the previous two scenarios, the start and end offsets to restore the database are queried in the binlog. I’m not going to take screenshots here.

By viewing the binlog, you can find that the starting offset of pingtouge is 219 and the last offset of Pingtouge is 3861.

mysqlbinlog d:\Mysql-binlog\mysql-bin.000043 --start-position 219 --stop-position 3861 | mysql -u root -p
Copy the code

Note that there is no need to bring the database. After the execution, all the data related to Pingtouge is returned.

We don’t have to run away. We have to learn to save ourselves, boy.

The above is the content to share today, I hope to help you in your study or work, if you think the article is good, welcome to like and forward, thank you.

The last

MySQL data recovery is an important part of MySQL data recovery. The original is not easy, the code word is not easy, but also hope you support. If there are mistakes in the article, please also put forward, thank you.

Welcome to scan the code to pay attention to wechat public number: “Internet flathead brother”, brother Peace study together, progress together.