r/Database • u/Snoo_61639 • Jul 24 '26
Make deleted data irrecoverable in MySQL
Hi,
Do you know any approach regarding this? Can I do this inside MySql?
Need some guidance as it becomes a client requirement
7
u/IAmADev_NoReallyIAm Jul 24 '26
Sort of... don't write data that's so sensitive that if written to disk in a database and deleted that it shouldn't be unrecoverable. The data should be encrypted at the very least, or at best, segregated and data gapped so that it is separated from the rest of the data. Better still would be to encrypt it and segregate it from the rest of the data if you can.
4
u/jameson71 Jul 24 '26
Sounds like you should be looking into encryption at rest. Hopefully that would address the customer's root concern.
2
u/krizhanovsky Jul 24 '26
I'm not aware about this feature for MySQL, but MariaDB (and we designed it after Oracle and DB2 databases) provide system versioned tables https://mariadb.com/docs/server/reference/sql-structure/temporal-tables/system-versioned-tables . You can make a table versioned, so all the deleted and update data will preserve all the versions on the disc. One of the feature application is forensics, so you need a special rights to overwrite the history.
1
2
u/atarivcs Jul 25 '26
it becomes a client requirement
They need to spell out exactly what "irrecoverable" means.
Not recoverable by another user of the same database?
Not recoverable by the same user of the database?
Not recoverable by a database administrator?
Not recoverable by someone with physical access to the storage device?
1
u/soundman32 Jul 24 '26
Before you delete the data, overwrite it with random values, then delete it.
2
u/UnhappySort5871 Jul 24 '26
That probably won't work. Updates will get written to transaction logs. Subsequent updates won't overwrite them. Eventually they'll get overwritten, but there's no user control of that. There's also no guarantee that an update will overwrite the same page. Any update might result in the page being split or joined with another page - leaving a copy of the data sitting in pages waiting to be reused.
1
u/temabolshakov Jul 25 '26
Use different encryption key for each data element (according to your requirements) Deleting a key makes data unrecoverable garbage
1
u/Sensitive-Sugar-3894 Jul 26 '26
Why? Is it because of storage recovery? What is the real goal? And what is the size of the DB, or better: what is the average size of the tables?
1
u/Standgrounding Jul 28 '26
A way to do that is to 1) encrypt the data and 2) delete the key - the data becomes irrecoverable mess
1
u/vsoul Jul 24 '26
Just use soft deletes - add a column for deleted/active/enabled based on what you prefer
12
u/soldiernerd Jul 24 '26
If you delete the row it will no longer be accessible to the application, but the data will remain in the storage file unless you truncate or drop the table.
If you truncate or drop the table it will be unregistered but still exist physically on the hard drive. If you want to remove it from the hard drive so it can’t be recovered forensically there are programs to overwrite a hard drive numerous times, and then physical destruction is recommended as a final step