Part of the data in a certain table in Mysql has been deleted inexplicably. How can I find out the reason?

The database is Mysql and has a user table. There are currently 144059 user information in it. There has been feedback recently that some users have registered before, but then find that they cannot log in after a while and need to register again. The website is PHP. After getting the feedback, I checked the cron job and logs, etc., but couldn't find the reason. (The reason why you can't log in is that the user's information has been deleted)

So, I would like to ask you where to start.

++++++++++++++++++++++++++++++++++++++++++
Modified some permissions After that, the loss of users never happened (it has been 2 months). However, the reason for the previous loss of users has never been found.
Anyway, case closed.

Reply content:

The database has logs (not referring to the text logs that people can see). You can just find the transaction ID of the delete statement and roll back there. Arrest all the programmers who have access to the data, and then use the tiger bench and pepper water. Within an hour, someone will admit that they secretly deleted it. To find the reason, just analyze the log
If the log information is incomplete, then complete it if necessary
In addition, does anyone doubt sql inject? After MySQL turns on binlog, write operations will be recorded in the binary log. You can use mysqlbinlog to view/export/recover data.

Assume that you have previously performed a mysqldump full backup and a binlog incremental backup (use the parameter - during mysqldump full backup) -flush-logs clears the binlog before full backup).

First restore the previous full backup with mysqldump, and then use mysqlbinlog to perform point-in-time restore:

<code class="language-text">mysql -uroot -prootpwd db_name < db_name.sql 
mysqlbinlog --stop-datetime="2013-10-12 12:30:00" /var/log/mysql/mysql-bin.000001  |  mysql -uroot -prootpwd
</code>

Let’s check the code logic first. The inexplicable loss of several records is unlikely to be a problem with the database itself or the hard disk. Even if you get the data back, if the code logic is wrong, you will still lose data in the future. Hard drive data is lost! Take percona 5.6 as an example:
There are two methods:
1 Use general_log
2 Use audit plug-in

1 Use general_log
Open general_log

<code class="language-text">set global general_log=on;
 
 mysql> show variables  where value like '%table%'
    -> ;
+------------------------------+-----------------------+
| Variable_name                | Value                 |
+------------------------------+-----------------------+
| log_output                   | TABLE                 |
| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
| tx_isolation                 | REPEATABLE-READ       |
+------------------------------+-----------------------+
3 rows in set (0.00 sec)
</code>

You can use Datahekr database middleware, which has a SAAS version and can provide more detailed audit information than the LOG function that comes with the database. There are slow query logs, executed SQL logs, error logs, etc. in the database.

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn