The table in TiDB was deleted, and it was found that the root account deleted it. Several people have access to the root account. How to identify who did it?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidb的表被删除了,查到是root账号删除的,root账号几个人都有,如何定位到是谁

| username: TiDBer_7Q5CQdQd

The table in TiDB was deleted, and it was found that the root account deleted it. Several people have access to the root account. How can we identify who did it?

| username: 裤衩儿飞上天 | Original post link

If the audit function is not enabled, it is basically impossible to locate it.

P.S. The community edition does not have the audit function, but the enterprise edition does.

| username: 路在何chu | Original post link

Take it as a lesson and revoke their drop privileges.

| username: 路在何chu | Original post link

However, you can just flash back directly.

| username: xingzhenxiang | Original post link

If the GC time is long enough, it can be recovered.

| username: TiDBer_7Q5CQdQd | Original post link

What does flash back do?

| username: forever | Original post link

Flashback mistakenly deleted tables and data

| username: TiDBer_7Q5CQdQd | Original post link

Why did I only recover partial data with FLASHBACK TABLE table_name?

| username: Jolyne | Original post link

Because this is also related to GC, it cannot be recovered after the GC time has passed.

| username: forever | Original post link

Before the table was deleted, data was deleted. The data before the deletion might not be recoverable. Try to check within the GC time frame to see if you can find it. If not, it means the GC time has passed.

If you encounter this issue, first extend the GC time. The GC might pass while you are hesitating. You can adjust the GC cleaning cycle by setting the tidb_gc_life_time variable. Don’t forget to reset this variable to its previous value after recovering historical data. The default value is 10m0s.

SET GLOBAL tidb_gc_life_time="60m"; -- 720h

To restore the default:

SET GLOBAL tidb_gc_life_time="10m0s";
| username: 胡杨树旁 | Original post link

Is there incremental backup? If both full and incremental backups are available, it can be restored, but it might be difficult to confirm the exact time of deletion.

| username: 小龙虾爱大龙虾 | Original post link

Ask them one by one.

| username: 随缘天空 | Original post link

Flashback has a time limit; it cannot be used beyond the GC time.

| username: 随缘天空 | Original post link

It’s almost unrealistic; you can only determine who was possibly operating based on the deletion time.

| username: zhaokede | Original post link

Without logs, it is probably impossible to trace.

| username: 春风十里 | Original post link

Full backups combined with log backups allow for point-in-time recovery. If you have an approximate time, there is still hope to recover accidentally deleted data.

| username: 这里介绍不了我 | Original post link

Almost unsolvable.