preface

Is engaged in the Internet development in recent years, participated in many projects of architecture analysis, database design, to turn the bug of countless and written SQL tens of thousands, never a major flaws, but often walk along the river bank, which have not wet shoes, on the first day of the May Day holiday, I did the most stupid in his career, is also the most exciting one thing: Select * from ‘where’ where ‘update’ where ‘where’;

Project Background

A brief introduction to the project background: We can understand it as an article retrieval system. The front end user can query the corresponding article content and view it by inputting date, keyword and article type:


The backend can maintain existing articles in multiple dimensions (those of you who have used the code generator I developed will be familiar with the following interface, and yes, 90 percent of the backend code (including the interface) is generated by me) :

Of the update

At that time, the development work of the whole system was all under pressure on me, and the construction period was very tight, only less than a week to go online, and I was ordered to shoulder the crisis. The foreground users of the system are mainly financial personnel of enterprises and their time of use is relatively scattered. The backstage users are internal administrators (only two); The number of new articles added every year is about two or three thousand. Overall consideration, there is basically no concurrency, the amount of data is not large, cache is unnecessary, search engine is overtalented, so in order to fast, we will use SpringBoot+MySql way for development. Finally, the project was completed and the final online deployment phase came around 3pm on April 30th. While checking the data, I found a date data in the wrong format, so I executed this SQL in Navicat, which was the beginning of the disaster:

update article set addtime = 'the 2019-07-26 00'
Copy the code

. NO, wait! After executing navicat, I realized something was wrong and tried to rollback, but it didn’t work. Navicat was automatically committed… Since the test environment is used, there is no backup of the database… Calm down, I want to calm down, now what I need to do is to restore the data to the previous, that is, to the most recent point in time before the execution of this SQL statement. I started to look up relevant information on the Internet and found that enabling binlog is the premise of data recovery. If it is not enabled, I am sorry that it cannot be recovered by conventional methods. I nervously connected to the remote server, CD to the mysql installation directory, and kept praying: There must be! There must be! Don’t make me!!!!!!! . When I saw the mysql-bin.000002 file under the var folder, I breathed a sigh of relief, there was still a save, god really did care for me. The rest of the operation is simple, according to the summary of information on the network, we can execute the following command to obtain the specified database within the specified period of all SQL statements:

/usr/local/mysql/bin/mysqlbinlog  
/usr/local/mysql/var/mysql-bin.000002
#Specify a database name, not a table
--database=xxx
#Starting time
--start-datetime='2020-04-26 00:00:00' 
#The end of time
--stop-datetime='2020-04-30 15:00:00' > backup.sql
Copy the code

To be on the safe side, I backed up all the files in the mysql directory and executed the above command. Unexpectedly, the backup. SQL file was generated in the current directory. Then I drop the database specified in the command, execute the backup. SQL script, and the line of OK flashes, which lasts about ten seconds, and suddenly stops. Refresh in Navicat, open the Article table, and thankfully the data is restored:


conclusion

Through this incident, LET me understand the importance of backup, as the saying goes: prepared is safe. Second, when executing update or delete statements, be sure to start transactions so that you can rollback!

Attached: like friends can pay attention to the public number “spiral programming geek” the first time to get the latest content updates!