Purge old records from mysql ibdata1 file

Question

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?

Answer 1

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'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}" > 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}" > 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} < Convert_To_MyISAM.sql
service mysql stop
rm -f /var/lib/mysql/ibdata1
service mysql start
mysql ${MYSQL_CONN} < Convert_To_InnoDB.sql

Give it a Try !!!

Answer 2

Note to all, there is never a time you can safely delete the ibdata file unless you plan on restoring your database from scratch. This also applies to after you have enabled innodb_file_per_table. That is due to there being quite a bit of other data in the ibdata1 file. You can minimize the impact by enabling undo* files to move the undo data to separate files. If you are interested in knowning what is in the ibdata1 file, you can use the following command line:

innochecksum -S /var/lib/mysql/ibdata1

Answer 3

You can try this method:

1) Take a dump of all InnoDB tables using MySQLDump or any other backup tool

2) Now edit my.cnf and specify

innodb_file_per_table=1

3) Restart MySQL server

4) Now import the dump which has InnoDB tables

5) Now delete old Ibdata1 file

*Innodb_file_per_table* is supported only for MySQL server v5.6.6 or higher

Historically, all InnoDB tables and indexes were stored in the system tablespace. This monolithic approach was targeted at machines dedicated entirely to database processing, with carefully planned data growth, where any disk storage allocated to MySQL would never be needed for other purposes. InnoDB's file-per-table mode is a more flexible alternative, where you store each InnoDB table and its indexes in a separate file

Advantages of using Innodb_file_per_table option

1. You can reclaim operating system disk space when truncating or dropping a table

2. The TRUNCATE TABLE operation is faster when run on individual .ibd files.

3. You can store specific tables on separate storage devices, for I/O optimization, space management, or backup purposes. In previous releases, you had to move entire database directories to other drives and create symbolic links in the MySQL data directory

4. You can move individual InnoDB tables rather than entire databases.

5. Using innodb_file_per_table may improve chances for a successful recovery and save time if a corruption occurs, a server cannot be restarted, or backup and binary logs are unavailable.

Reference: http://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html

Answer 4

Your best bet is to create a replica with a fresh innodb subsystem without legacy data in the ibdata1 file. This is trivial and should be done with a logical backup tool such as mydumper or mysqldump. Xtrabackup will make a binary copy of ibdata1 file and as such your phantom strings will exist in that file too. Once your replica is built, restored and up-to-date you can repoint your application to use the new instance. Out with the old, in with the new, minimal downtime.