How to dump all MySQL databases to reduce size of ibdata1 file

The /var/lib/mysql/ibdata1 file can grow gigantic, here is the procedure to reduce the size of it :

1) Dump all databases as one file

mysqldump -u root -p --all-databases --routines --verbose --skip-lock-tables --result-file=alldb.sql

2) drop all databases

3) Stop MySQL, delete the ibdata1 file, restart MySQL

/etc/init.d/mysqld stop

cd /var/lib/mysql
rm ibdata1 file

/etc/init.d/mysqld start

5) Reimport all databases

mysql -u root -p < alldb.sql

Leave a Reply