MySQL ibdata1 Disk Space Issue

  • Posted on : September 5, 2012
  • Modified: November 5, 2020

  • Author : SPEC INDIA
  • Category : Big Data & Database

The “ibdata” files are part of the InnoDB engine in MySQL. It stores InnoDB data. They are very important files to manage InnoDB engine along with “ib_logfile”. The default path for the files is “/var/lib/mysql”.

If you are not using innoDB engine then you can delete “ibdata” files from PATH as well  “ib_logfile*” files BUT please keep in mind if you are using innoDB and trying to deleted ibdata1 file then you will not be able start MySQL.

In InnoDB engine, whenever user performs any DML operation on innoDB table, it will increase size of file “ibdata”. so after a sometime ibdata will be occupy too much size issues in server due to “ibdata” file.

Perform following steps to move “ibdata” to another location, Let’s say for an example We want to change the path of “/var/lib/mysql” to “/appdata/mysql/mysql”

  • Stop mysql server

#  service mysql  stop

  • Uncomment the following lines in “my.cnf” (linux) / “my.ini” (windows) file.

# vi /etc/my.cnf

innodb_data_home_dir = “Path of ibdata file”

innodb_data_file_path = ibdata1:”Initial Size”:autoextend

innodb_log_group_home_dir = “Path of ibdata file”

Examples:

innodb_data_home_dir = /appdata/mysql/mysql

innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = /appdata/mysql/mysql

  • Move “ibdata” including “ib_logfile*” files to requested folder.

# cp ibdata*  /appdata/mysql/mysql/

  • Start mysql server.

#  service mysql start

Now you can remove the files under /var/lib/mysql,(take backup before remove).

Author: SPEC INDIA


less words, more information

Tech
IN 200
words

Read our microblogs

Subscribe Now For Fresh Content

Loading

Guest Contribution

We are looking for industry experts to contribute to our blog section through fresh and innovative content.

Write For Us

Our Portfolio

Proven Solutions Across Industries
Technology for Real-Life

Visit Our Portfolio

Scroll Up