Flush/Clean MySql ib_logfile0 ib_logfile1

Question

Is there a way to flush ib_logfile0 and ib_logfile1 without dumping the tables to a .sql file, deleting, then re-inserting? Somebody entered a couple plain text credit card numbers into the customer notes table. It came up in my PCI scan and I removed them from the table but they still exist in the log files :(

Answer 1

There are two ways

1. Stop MySQL gracefully. Check the error log and make sure it was a normal shutdown.

   Move ib_logfile0 and ib_logfile1 out of the datadir (Save them just in case you need to roll back)

Start MySQL. InnoDB will notice there are no REDO log files and will create empty ones.

2. Write to some InnoDB table some junk records until InnoDB rotates writes in ib_logfile* .

Answer 2

You could have InnoDB clean things up as follows:

• signal mysqld to have InnoDB flush everything out of the logs on shutdown
• shutdown mysqld
• delete the logs
• start mysqld

No crash recovery will happen because there will be nothing to recover.

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS} -h127.0.0.1 -P3306 --protocol=tcp"
SQL="SET GLOBAL innodb_fast_shutdown = 0"
mysql ${MYSQL_CONN} -ANe"${SQL}"
mysqladmin ${MYSQL_CONN} shutdown
cd /var/lib/mysql
rm -f ib_logfile*
service mysql start

If you want lingering data pages cleansed out of the Double Write Buffer, please see my old post Fill ibdata1 With Zeros/Nulls/etc Or Change A Table's Tablespace

GIVE IT A TRY !!!