1

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?

Improve this question
6
  • I think your only way is to avoid ibdata1 from being accessed by anyone apart from mysql processes. This you can achieve on filesystem level without restarting MySQL (but still have to be careful not to harm your system).
    – dezso
    Aug 29, 2012 at 16:01
  • Or, probably, you can devise a solution based on OPTIMIZE TABLE: dev.mysql.com/doc/refman/5.5/en/optimize-table.html
    – dezso
    Aug 29, 2012 at 16:13
  • @dezso - access to var/lib/mysql already was/is restricted to the mysql user.
    – Jürgen Thelen
    Aug 29, 2012 at 17:48
  • Then how do you expect ibdata contents leaking?
    – dezso
    Aug 29, 2012 at 17:50
  • @dezso - Doesn't OPTIMIZE TABLE lock the table for the time optimizing? Because if so, then it unfortunately will not help.
    – Jürgen Thelen
    Aug 29, 2012 at 17:51

1 Answer 1

Reset to default
2

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...

Improve this answer
1
  • I will go the innodb_file_per_table = on way, that's for sure. Not sure though, what "completely compress ibdata1" does mean exactly in this context, but I'll definitely gonna check your links, thank you. Still, my main problem is another one: what to do until I even get the chance to change the InnoDB setup? Like mentioned, we won't get any time slot the next few days, where we could change the server setup. What to do to prevent leaking in the meantime?
    – Jürgen Thelen
    Aug 29, 2012 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.