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
