'Howto: Clean a mysql InnoDB storage engine?

Is it possible to clean a mysql innodb storage engine so it is not storing data from deleted tables?

Or do I have to rebuild a fresh database every time?

mysqlinnodb


Solution 1:[1]

The InnoDB engine does not store deleted data. As you insert and delete rows, unused space is left allocated within the InnoDB storage files. Over time, the overall space will not decrease, but over time the 'deleted and freed' space will be automatically reused by the DB server.

You can further tune and manage the space used by the engine through an manual re-org of the tables. To do this, dump the data in the affected tables using mysqldump, drop the tables, restart the mysql service, and then recreate the tables from the dump files.

Solution 2:[2]

I follow this guide for a complete reset (as root):

mysqldump --all-databases --single-transaction | gzip -c > /tmp/mysql.all.sql.gz
service mysql stop
mv /var/lib/mysql /var/lib/mysql.old; mkdir -m700 /var/lib/mysql; chown mysql:mysql /var/lib/mysql

mysql_install_db                # mysql 5.5
mysqld --initialize-insecure    # mysql 5.7

service mysql start
zcat /tmp/mysql.all.sql.gz | mysql
service mysql restart

Solution 3:[3]

What nobody seems to mention is the impact innodb_undo_log_truncate setting can have.

Take a look at my answer at How to shrink/purge ibdata1 file in MySQL.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 bigjeff
Solution 2 ggrandes
Solution 3 Slam

Related Questions