How to reclaim or shrink unused space in MySql Ibdata1 file?

One of my customers calls me, telling that he has problems in his database server, it was giving a lot of errors. In this situations one of the first things to check is free space in hard disk, and bingo disk was full,we found that the biggest usage was in MySql folder, but databases folders does not have a big files inside

searching a little more there was file in /var/lib/mysql called ibdata1 using around 44 gigabytes.

When you are using Mysql before version 5.6.5 the option innodb_file_per_table is set to OFF, in this case innodb uses the system table space.Basically system table space are the ibdata files located in /var/lib/mysql   the problem of store all information in system table space is that ibdata files newer shrinks. When ibdata files occupy all hard disk we have two options the quick and simplest one was adding more storage space, but What performance can expect when your database  needs to read a 40 gigabytes to find a record?

Then we decided to use the second option that was reduce system table space.

Step 1 Enable file per table in mysql configuration

you should edit /etc/mysql/my.cnf and add a line like this

innodb-file-per-table = 1

Step 2 Backup your databases

First we made a backup of each databases except information_schema , performance_schema and mysql


mysqldump --single-transaction -uroot -p database > database.sql

Step 3 Delete  databases

login in your mysql as root


mysql -uroot -p

drop all databases except mysql information_schema and performance_schema if exists


drop database databasename;

Step 4 stop mysql service

At this point you should stop your mysql service


/etc/init.d/mysql stop

Step 5 delete ibdata and ib_log files


rm /var/lib/mysql/ibdata*

rm /var/lib/mysql/ib_logfile*

Step 6 Start MySql Service

This will create a new set of ibdata files


/etc/init.d/mysql start

 

Step 7 restore backups

At this point we will restore  each database one per one


mysql -uroot -p  < database_backup_file.sql

Because you have enabled file per table option, when restoring databases each table will have his own tablespace instead of use ibdata1 file if you access to a database folder you should view some idb files

At this point you can check your innodb  ibdata1 file it should be smaller and you can reduce table ibd files with mysqlcheck –optimize

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.