Purge old records from mysql ibdata1 file Is it possible to purge old records from an ibdata1 file without taking down the database attached to it?

Records that have been deleted from the database are still present when I scan ibdata1 for strings.

I am somewhat aware of how the ibdata1 file works and realize that those portions of the file will probably be overwritten in time, but I need to zero out or otherwise remove the old data so it is no longer stored on the server in any form.

Is there anyway to do this without taking down the database?
What you are asking for has never been documented or attempted.

However, I have answered two posts like this in the past:


Aug 29, 2012 : Removing/overwriting sensitive data located in already deleted records in ibdata files
Dec 17, 2012 : Fill ibdata1 With Zeros/Nulls/etc Or Change A Table&#39;s Tablespace


I have an additional suggestion for data you are going to delete, going forward. You will have to put some good developer effort in this one:

Before dropping a table, you should


go to every INT and FLOAT value and make them 0
go to every CHAR, VARCHAR and TEXT field


get the length of the field
update the column with the same number of bytes with all Xs or all 0s



You will have to examine the entry in INFORMATION_SCHEMA.COLUMNS for every column to determine which is numeric and which is character. Once you have manually overwritten all data in the old table, then you are free to drop it, knowing you  masked the data beforehand.

CAVEAT

If all your InnoDB tables have no constraints/foreign keys, here is something you can do during some down time.


Convert all InnoDB tables it to MyISAM
Shutdown mysql
delete ibdata1
Startup MySQL
Convert those MyISAM tables back to InnoDB


I can't take credit for that idea. Shlomi Noach suggested it back on September 19, 2013.

If the DB is not too big, here is how you can script it.

MYSQL_USER=root
MYSQL_PASS=password
MYSL_CONN="-u${MYSQL_CONN} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -ANe"${SQL}" &gt; Convert_To_MyISAM.sql
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -ANe"${SQL}" &gt; Convert_To_InnoDB.sql
mysql ${MYSQL_CONN} -ANe"SET GLOBAL innodb_fast_shutdown = 0"


At this point, look at the two scripts in vi or less.

If you are satisfied with the code, then you can do this:

mysql ${MYSQL_CONN} &lt; Convert_To_MyISAM.sql
service mysql stop
rm -f /var/lib/mysql/ibdata1
service mysql start
mysql ${MYSQL_CONN} &lt; Convert_To_InnoDB.sql


Give it a Try !!!