MySQL: 6 ways to move data from huge ibdata file to .ibd files

ibdata file is used to store all tables of databases that use the innoDB engine, so if you delete it, you will lose all data. To keep each database files in a separate file add innodb_file_per_table in your my.cnf file under [mysqld] section.

Here are various ways to convert databases from InnoDB to myissam and move this ibdata file to seperate files.

Make sure you backup all databases before proceeding:

mysqldump --add-drop-table --all-databases | gzip > /home/alldatabases.sql.gz

Quick fix: move ibdata1 file and change the path

Step 1. First stop MySQL

service mysqld stop

Step 2. Move the ibdata1 file to /home

mv /var/lib/mysql/ibdata1 /home/

Step 3. Edit the my.cnf file and add the following line:

innodb_data_file_path = /home/ibdata1:988Mautoextend

Step 4. save the file and restart service:

service mysqld restart

Another Quick fix: move databases and symlink them

ibdata file is usually located on the /var partition and if it fills up, the quickest workaround is to symlink database files from /var to a partition with free space available, such as /home

Step 1. First stop MySQL

service mysqld stop

Step 2. Move database files to /home

mkdir /home/var && mv /var/lib/mysql /home/var/mysql

Step 3. Create a symlink from /var folder to /home

ln -s /home/var/mysql /var/lib/mysql

Step 4. Start MySQL

service mysqld start

Export and Import databases

The above fix will simply move databases to another partition and free disk space on /var, but to get rid of the huge ibdata file we ca export all databases, add innodb_file_per_table in my.cnf file, then import back the databases.

First add innodb_file_per_table in your my.cnf file under [mysqld] section, then run the following commands:

Step 1. Create a /temp dir to store our SQL dump

mkdir -p /temp

Step 2. Backup all databases

/usr/local/mysql/bin/mysqldump -R -q -p --all-databases > /temp/all.sql

Step 3. Stop MySQL to remove all database files from /var

/etc/init.d/mysql stop

Step 4. Remove all files

rm -fr /usr/local/mysql/var/*

Step 5. Install default DBs

/usr/local/mysql/bin/mysql_install_db

Step 6. Change ownership so MySQL user can read/write to/from files

chown -R mysql.mysql /usr/local/mysql/var/

Step 7. Start MySQL

/etc/init.d/mysql start

Step 8. Import databases

/usr/local/mysql/bin/mysql < /temp/all.sql

Step 9. Restart MySQL

/etc/init.d/mysql restart

ALTER TABLE

Another option is to alter tables to MyISAM and then back to InnoDB, this will recreate all tables from the huge InnoDB file.

Step 1. Convert all tables in all databases to MyISAM engine:

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=MyISAM;') 
FROM information_schema.TABLES WHERE ENGINE = 'InnoDB';

Step 2. Convert all tables from all databases back to innoDB:

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=InnoDB;') 
FROM information_schema.TABLES WHERE ENGINE = 'MyISAM';

mysqloptimize –all-databases

For Debian users another option is to add the innodb_file_per_table option in my.cnf file and then run mysqloptimize – a small python script to defragment MySQL database tables, it just does “OPTIMIZE TABLE X” for each table and database:

mysqloptimize –all-databases

This will create a seperate .idb files for each database, afterwards you can remove the huge indata file

rm /var/lib/mysql/ibdata1

skip-innodb

Another option (for a live server) is to move all tables to MyISSAM, disable InnoDB, delete files, then reenable InnoDB and convert tables.

Step 1. Convert all tables to MyISSAM

Step 2. Edit my.cnf file and add skip-innodb

nano /etc/my.cnf

Step 3. Restart mysqld

service mysqld restart

Step4. Remove all innodb files from /var

rm -r /var/lib/mysql/inbdata1 
rm -rf /var/lib/mysql/*.ibd

Step 5.Turn innodb back on by removing the line skip-innodb from my.cnf

nano /etc/my.cnf

Step 6. Restart mysqld

service mysqld restart

Step 7. Convert all tables back to InnoDB