Causes and solutions for MySQL data loss

Preface

Recently, we occasionally receive feedback from users about missing data or data loss. From a phenotypic perspective, this type of problem is the most urgent one at the database level. Putting aside objective conditions, the only recovery methods for this type of problem are backup recovery + Binlog playback, which usually takes a long time and has a great impact on the business.

However, as a software that focuses on stability, the probability of data loss is actually very low, so are these feedback issues really about "data loss"?

Problem Description

One day at noon, we received feedback from a user that after logging into the database with a business account, the business database disappeared.

Cause Analysis

When I received this question, the atmosphere was still very tense. On the one hand, I contacted the user to authorize login to the database for investigation, and on the other hand, I was communicating with the user to see what changes had been made recently.

After logging in to the database, I found that the business database existed. Combined with the user's feedback: "The business database is gone", I initially determined that the business account did not have permissions. After checking with show grants, I found that the business account only had USAGE permissions, similar to the following effect:

mysql> show grants;
+----------------------------------+
| Grants for test@% |
+----------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' |
+----------------------------------+
1 row in set (0.00 sec)

Since this account only has the minimum permissions, it is obvious that it “cannot see business data”, so after re-authorization, the problem was solved. After investigation, it was found that the initial authorization operation occurred on another account with the same name, similar to:

mysql> show grants;
+-------------------------------------------------------------+
| Grants for [email protected].% |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON prd_name.* TO 'test'@'10.120.117.%' |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Expand

Regarding the phenomenon of "data loss", if the data at the entire database level is "lost", but the database itself is normal, there is a high possibility that the problem is the same as in this case: permission error. There are generally two possibilities that may cause this problem: 1. The logged-in account matches another account with the same name; 2. There is a problem with authorization, resulting in the business account having no permissions. Of course, the worst case is definitely the drop database operation. Only by parsing the binlog can the time of executing this operation be located.

The other category is "partial data loss", such as a table is missing, or some data in the table is missing, etc. Strictly speaking, this type of problem may also be caused by permission errors, because MySQL's permission control can indeed be done at the table and column level, but it is generally not used in reality. Most of the time it is due to an incorrect operation, such as not having a where condition when updating or deleting. In this case, you can only use historical backup and then restore using binlog. This operation is encapsulated as a "rollback" function on Tencent Cloud.

To sum up

When you encounter this type of problem, you can take a moment to observe the problem. It may only take a few seconds to reauthorize to solve this very urgent and serious "data loss" problem.

The above are the detailed reasons and solutions for MySQL data loss. For more information about MySQL data loss, please pay attention to other related articles on 123WORDPRESS.COM!