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?

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
Jürgen Thelen
  • 111
  • 4

1 Answers1

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

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536