ibdata1 mysql file

Posted by | Posted in Code, Command-Line, Linux, MySQL, Ubuntu | Posted on 31-07-2014

Recently I discovered that my ibdata1 file with in /var/lib/mysql was filling up my webserver hard drive. After reading on several forums, it was discovered that the ibdata1 file contains all of the InnoDB file structure as-well-as all of the data. You can not delete this file and have mysql recreate it. You will lose all of your MySQL InnoDB information.

Instead, you’ll have to backup all of your InnoDB databases, drop all of your InnoDB databases, stop the mysql server, remove the corresponding files, restart myssql, recreate your databases and then restore all your databases.

Below, I’ll demonstrate the steps that I took to accomplish this task.

Backing up your InnoDB MySQL Database

mysqldump -u USER --password=PASS DATABASE > DATABASE_DATE.sql

Drop your MySQL InnoDB Database

mysql -u USER --password=PASS
drop database DATABASE;
exit;

Stop MySQL Server

sudo service mysql stop

Remove the offending files

sudo rm /var/lib/mysql/ibdate1
sudo rm /var/lib/mysql/ib_logfile0
sudo rm /var/lib/mysql/ib_logfile1

Restart MySQL

sudo service mysql start

Recreate your InnoDB MySQL Database

mysql -u USER --password=PASS
create database DATABASE;
exit;

Restore your database from backup.

cat DATABASE_DATE.sql | mysql -u USER --password=PASS -D DATABASE

Now you should be all set and your ibdata1 file will be MUCH smaller.

Comments are closed.

Join the mailing list

Check your email and confirm the subscription