Removing/overwriting sensitive data located in already deleted records in ibdata files As far as I know, using InnoDB tables with innodb_file_per_table = off does not prevent deleted data from being visible in the ibdata1 file.

I know this is by-design, and that I could switch the system setup to use innodb_file_per_table = on, which would allow the system to shred the ibdata* files containing the sensitive information, however there is "no server downtime allowed"

Could I simply overwrite the sensitive information in the ibdata1 file somehow, so that some erroneous this is sensitive reads this is xxxxxxxxx afterwards.

I'm not a DBA and neither know whether this would work out at all, or will destroy some checksums, crash the server, or whatever.

So my question is, what can I do to prevent leaking in the meantime without affecting the stability of the running server?
Switching to innodb_file_per_table is not enough because ibdata1 would still be the same size. 

Any tables that were inside ibdatat1 and you ran OPTIMIZE TABLE against, it would simply manifest the table and its indexes to a .ibd file. You would still have the footprint of the table inside ibdata1. You are much better off restructuring InnoDB so that no data and index pages reside in ibdata1

Think about it: With innodb_file_per_table off, what goes inside ibdata1 besides data and indexes?


Data dictionary
Double Write Buffer (Background write to prevent reliance on OS caching)
Insert Buffer (Managing changes to non-unique secondary indexes)
Rollback Segments
Undo Space
Pictorial Representation


Some of these may eventually overwrite old data and index pages, but how long is eventually? Would it not be better to completely compress ibdata1? Basically, it would entail:


doing a mysqldump of all databases
deleting ibdata1
making sure innodb_data_file_path is the default (ibdata1:10M:autoextend) in /etc/my.cnf
starting up mysqld to let it recreate ibdata1
reloading the mysqldump.


Please see my past links on how to do so...


Oct 29, 2010 : My Original Post in StackOverflow
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB
Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file &amp; The table user_analysis is full