Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am running into an issue where sensitive data was not encrypted properly at the application level of our production system.

Even though the guilty records have been removed or corrected, scanning still reveal the records in the .ibd file, as well as the ibdata1 and ib_logfiles.

I have optimized the affected table and so the .ibd file is clean now; but I still have the issue of the ibdata1 and log files.

Doing a full mysqldump of all databases is a last resort as the data set if quite large. Since we are already using per table tablespaces, can I simply stop mysql, move the ib files and then restart and reset the files in that manner?

share|improve this question

You cannot (or, you can, but with pain & risk).

For the ib_logfile[01] - you needn't worry: these are cyclic files, so your info will eventually be overwritten (and if you want to rush it, why don't you invoke a gazillion UPDATE foo SET bar=1 WHERE id=1 on some dummy foo table?

But the ibdata file cannot just be dropped. Nevertheless you should (no guarantees held) be able to overwrite the file at those particular positions where you find your sensitive data. If you can identify all of them - overwrite them with spaces/whatever (say these are credit card numbers -- overwrite with "0"s).

I wrote on how you can use a replicate to convert all your tables to MyISAM (Assuming no foreign keys), restart, throw away your ibdata file, convert all back to innodb, get on with your life. It's a lengthy process as well. See: Getting rid of huge ibdata file, no dump required

share|improve this answer
+1 for the "OMG THAT'S SO BRILLIANT !!!" link to Getting rid of huge ibdata file, no dump required. – RolandoMySQLDBA Sep 22 '13 at 0:13

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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