Today I hit the no disk space available error in our demo server again. I had been hitting this issue for a while and being a non production database I was not bothering to find a permanent solution for a while. Today I decided to find the root cause and resolve it.
The steps to resolve the issue are
- Take a dump of your databases using mysqldump command. For example I did
- mysqldump --user username --password=password myschema > backup06292014.sql
- I did this for the schema that are used. Fortunately we had only one schema in the database.
- Drop the schema from the mysql workbench. I dropped one schema that was being used.
- Stop the mysqld service using command :- sudo service mysqld stop.
- Now delete the files ibdata1,ib_logfile0,ib_logfile1 from folder /var/lib/mysql
- Start the mysql service using command sudo service mysqld start
- Create the dropped schema first from mysql workbench
- Now run command mysql -uusername -ppasswored schemaname < dumpfile.sql
This recreated the schema and imported all the data. Now my diskspace utlization dropped to 29% from 100%
