How to shrink or reduce MySQL ibdata size in Red Hat Linux server

MySQL database is widely use database server, but sometimes you are facing an ibdata size issue on the database server.

Normally this ibdata size error occur while you are trying to start the MySQL database services. So to resolve such type of error this post is very useful. Here, I would explain to you how to shrink MySQL ibdata size in Red Hat Linux operating system.

➦ Before doing anything in database I would suggest to you take a backup of existing database so in case of any issue, we will restore the previous backup.

Please find the below command using which you need to take a backup of MySQL database. You must remember you need to take full backup which consist triggers, routines and all tables.

[root@localhost]# mysqldump --lock-all-tables --triggers --routines -u user_name -p -h server_name database_name > /home/backup.sql

Using above command you can take a complete MySQL backup on your linux machine.

➦ Now you need to drop all database on the server except default MySQL schema.

➦ Once you drop all existing database, stop the MySQL service and make it disable permanent. Please find the below command to stop the MySQL services on server.

[root@localhost]# /etc/init.d/mysql stop
[root@localhost]# chkconfig mysql off

For RHEL7 you need to use "systemctl" command to stop the MySQL running service. The above example command are valid in lower version on RHEL7.

➦ You need to change the configuration setting in MySQL configuration file, which is generally located in /etc directory.

[root@localhost]# vi /etc/my.cnf

[mysqld]

Innodb_file_per_table

Innodb_flush_method=0_DIRECT

Innodb_log_file_size=1G

Note: Whatever you’re set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

➦ Please delete the ibdata1, ib_logfile0 and ib_logfile1 from the MySQL library location. At this point, there should only be the MySQL schema in /var/lib/mysql

Now take a restart of MySQL service again and recreated the ibdata1 and ib_logfile0 again and restore the MySQL backup again.