“This is the 17th day of my participation in the First Challenge 2022. For details: First Challenge 2022.”

A few days ago, a guy in the next department executed a drop command on the database in the production environment. Well, a real example of deleting the database ran away actually happened around me. Fortunately, the operation and maintenance students helped me and recovered the data later. Later I heard that although this guy was not fired, but also eat a company warning.

Then, the operation and maintenance side reclaimed the permission of the drop command in all database environments, even including the development environment, which had no impact on us. Generally, we have no need to delete tables. A few days later, when I was renaming a table, I received this error:

A closer look at the error:

1142 - DROP command denied to user 'hydra'@'localhost' for table 't_orders'
Copy the code

What is the relationship between the rename table and the drop command? In a skeptical manner, we wanted to explore the impact of not having DROP permissions on our daily database operations, which led to a series of local tests.

You need to create a mysql user that does not have the drop permission. Log in to mysql as user root and cancel the drop permission for user hydra. As opposed to the grant command, you can revoke a user’s grant:

revoke drop on *.* from hydra@'localhost';
Copy the code

It’s time to show off

Modify the name of the table

SQL > alter table name; alter table name;

Mysql > ALTER table RENAME; ALTER table RENAME; ALTER table RENAME; As for why the renaming failed, take a look at the official documentation:

RENAME TABLE renames one or more tables. You must have ALTER and DROP privileges for the original table, and CREATE and INSERT privileges for the new table.

When renaming a table, you must have the ALTER and DROP permissions for the original table, as well as the CREATE and INSERT permissions for the new table.

truncate

The truncate command cannot be used when I want to clear a table and set the primary key of AUTO_INCREMENT to the initial value:

With that in mind, take a look at the official documentation:

Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways:

Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.

The document explains that although truncate and DELETE have similar functions, TRUNCate is classified as a DDL language, while DELETE is a DML language. Compared with delete, truncate deletes rows of data and then creates a new table. This operation is much faster than DELETE, especially for large tables.

Data Manipulation Language (DML), as a data manipulation language, mainly performs some operations on data, such as adding, deleting, modifying and querying commonly used data. Data Definition Language (DDL) is a data definition language, which is mainly used to define or change the structure of a table. This operation process is implicitly committed and cannot be rolled back.

When TRUNCate is not available, try the following delete:

While a DELETE statement without a WHERE condition is not recommended, it is functionally successful. So let’s look at another question, has the increment id in the table been reset?

As we know, if TRUNCate is executed, the value of the increment column ID is reset to 1. Let’s look at what happens after delete, insert a data and query:

From the above result, you can see that the value of the increment column is still incremented after the delete is used to clear the table. To reset this value, run the alter command on the table manually:

alter table t_orders auto_increment= 1;
Copy the code

Drop scope

Is there a drop instruction that can be executed successfully even without permission? Mysql > drop database; mysql > drop database; mysql > drop database

drop DATABASE mall;
> 1044 - Access denied for user 'hydra'@'localhost' to database 'mall'
>Time:0.005s

drop TABLE t_orders;
> 1142 - DROP command denied to user 'hydra'@'localhost' for table 't_orders'
>Time:0s

drop VIEW order_view;
> 1142 - DROP command denied to user 'hydra'@'localhost' for table 'order_view'
>Time:0.001s
Copy the code

Of course these commands did not execute successfully, but when you tried to drop a stored procedure using DROP, something unexpected happened. When you do not have the drop permission, the drop operation on the stored procedure can be successfully executed:

Turn to the licensing section of the official documentation and take a look at this picture:

As the table above explains, the drop command only applies to databases, tables, and views. The permission of a stored procedure is added to alter routine. Therefore, you can use the DROP command to drop a stored procedure even if you do not have the DROP permission.

How to restore data after delete

As can be seen from the previous experiment, although truncate cannot be used to clear the table after the DROP permission is reclaimed, we can still use delete statement to achieve the same effect. So why is DELETE not afraid of the risk of database deletion?

As mentioned earlier, the delete statement is a DDL language, in fact, the actual delete process is row by row, and the deletion log is recorded in the log, let’s see how to use binlog to restore the deleted data.

SQL > select binlog from database where binlog is enabled

show variables like '%log_bin%';
Copy the code

If the value is ON, binglog is enabled:

After making sure binlog is enabled, we use delete to delete all data in the table:

delete from t_orders;
Copy the code

Before recovering deleted data, you need to find the directory where the data files are stored:

In this directory, there are several files named mysql-bin.*****, and we need to find the adjacent binglog file based on the time when the deletion occurred:

After finding the target binlog file, copy it to D:\ TMP, then go to the bin directory of mysql installation directory and execute the following command:

mysqlbinlog --base64-output=decode-rows -v --database=mall --start-datetime="2021-09-17 20:50:00" --stop-datetime="2021-09-17 21:30:00" D:\tmp\mysql-bin.000001 > mysqllog.sql
Copy the code

The parameters are described below:

  • base64-output=decode-rows: Parses the data into SQL statements based on row events and converts the data to normal characters
  • database: database name
  • start-datetime: Starts reading from the first event in the binlog that is equal to or later than the timestamp
  • stop-datetime: Indicates the end time of data recovery
  • D:\tmp\mysql-bin.000001: Log file for data recovery
  • mysqllog.sql: Output file to restore data

SQL/mysqllog. SQL/mysqllog. SQL/mysqllog. SQL/mysqllog. SQL/mysqllog. SQL/mysqllog. SQL/mysqllog. SQL/mysqllog. SQL/mysqllog. SQL/mysqllog. SQL

The delete command can be used to retrieve the value of each row when the delete command was executed. If a large amount of data needs to be restored, you are advised to use scripts to convert DELETE statements into INSERT statements in batches to reduce data recovery workload.

Okay, if you insist on seeing this, promise me that in the future, before deleting libraries, you’ll check to see if binlog is enabled, okay?

The official document: dev.mysql.com/doc/refman/…

The last

If you feel helpful, you can click a “like” ah, thank you very much ~

Nongcanshang, an interesting, in-depth and direct public account that loves sharing, will talk to you about technology. Welcome to Hydra as a “like” friend.