Mysql – How to safe delete (shred) a MySQL database

MySQLSecurity

I want to store sensitive data in a MySQL database for a while, so I'd like to know a way to delete a MySQL database without having a chance of recovery. In Linux, we have a tool named shred which overwrites the content of the file repeatedly, so its content can never be recovered anymore. Problem is: I want to do it with a specific MySQL database. I know MySQL stores data in /var/lib/mysql, but there are several files there and it would destroy all databases and break MySQL. Any ideas?

Best Answer

InnoDB leaves user data in several places.

  1. ibdata1 if innodb_file_per_table=OFF or respective *.ibd file if innodb_file_per_table=ON. This is where InnoDB stores the data.

  2. ib_logfile0 and ib_logfile1 stores recent modifications.

  3. Double write buffer (which resides in ibdata1 no matter what innodb_file_per_table is) stores recent modifications.

To reliably delete user data you need to shred ibdata1 - because of the double write buffer. If you do that, MySQL instance will be destroyed. So, I would say it's impossible to "shred" a particular database/table.

In your case I would create a replica with mysqldump (or mydumper, not with xtrabackup or a file system snapshot) and destroy the original instance/disk/server.

Related Question