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?
4 Answers
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
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.
Related