Sunday, June 21, 2015

Shrink/Purge ibdata1 file in MySQL

1) Do a mysqldump of all databases procedures, triggers etc except the mysql and performance_schema databases
mysql -u username -p --database databasename > databasename.sql

2) Drop all databases
drop database databasename

3) Stop mysql
service mysqld stop

4) Delete ibdata1 and ib_log files
rm -rf /mnt/data/ibdata1
rm -rf /mnt/data/ib_logfile*

5) Start mysql
service mysqld start

6) Restore from dump
mysql -u username -p
You will get a mysql prompt and fire the below 2 commands there. Fire the
command where you have put the sql backup
mysql> source databasename.sql


Your database is purge :)

No comments:

Post a Comment