The file ibdata1 is the system tablespace for InnoDB infrastructure, and it contains several informations about InnoDB such as table data pages, index pages, data dictionary, rollback segments, etc. This file is always growing, then, it’s easy to reach to giant sizes.
The size of this file commonly affects development and test machines, because many drops and imports with databases inflate this one. Then, to fix this problem, I have some steps to ends this problem.
Backup all your databases
Do a full dump of your databases.
[bash]mysqldump -u root -p –routines –all-databases > dump.sql[/bash]
Drop all your databases
Now, you’ll need remove all your databases, except for mysql and information_schema databases, because they are used by MySQL to control users and data information about managed databases.
[bash]mysql -u root -p -A[/bash]
[sql]DROP DATABASE my_databases;[/sql]
Stop MySQL service
For the next step, you will need stop the MySQL service.
[bash]sudo service mysql stop[/bash]
Configurations
Now, you’ll need change in MySQL how it’s manage InnoDB data, in this case using one file per table. Locate the my.cnf file on your server and putt this parameter at [mysqld] section:
[bash]sudo vim /etc/mysql/my.cnf[/bash]
[bash]
[mysqld]
innodb_file_per_table
[/bash]
Save with ‘Esc + :wq!’, then go to MySQL data folder:
[bash]cd /var/lib/mysql[/bash]
The ls -alh command in this folder will print something like this:
[bash]-rw-rw—- 1 mysql mysql 35GB Jul 15 11:49 ibdata1[/bash]
Delete this file:
[bash]sudo /bin/rm -f ibdata1[/bash]
Finally, start MySQL service and import your databases again:
[bash]sudo service mysql start[/bash]