Bootstrap FreeKB - mySQL / MariaDB - InnoDB ibdata file
mySQL / MariaDB - InnoDB ibdata file

Updated:   |  mySQL / MariaDB articles

Let's say you have a table named table001 that is using the InnoDB engine. The show table status command can be used to see that the engine is InnoDB.

mysql -e "show table status \G"


Something like this should be returned.

  Name: table001
Engine: InnoDB


By default, a file named ibdata1 is used to store InnoDB data. Typically, this file is located in the /var/lib/mysql directory. Whatever you do, do not delete this file. Deleting this file will put you into a world of hurt.

This file is shared. If you have multiple InnoDB tables, each table will append events to the ibdata1 file.

~]# ls -l /var/lib/mysql
-rw-rw---- 1 mysql mysql 56140759040 Apr  8 21:40 ibdata1


The ibdata1 file can grow to be very large.

~]# du -hs /var/lib/mysql/*
53G     /var/lib/mysql/ibdata


Individual files for each table (.ibd)

If you want to have individual data files for each table, follow these steps.

  1. Stop mySQL or MariaDB
  2. Backup each database
  3. Delete the "ib" files
rm -f /var/lib/mysql/ib*
  1. Add the following directive to /etc/my.cnf
  1. Start mySQL or MariaDB
  2. Import the databases

Now you should have individual ".ibd" files for each table, something like this.



Increasing the size of the ibdata file(s)

By default, the innodb_data_file_path directive in /etc/my.cnf defines the name of the file (ibdata1). In this example, the 10M:autoextend options means that the InnoDB tablespace will be extended by 10 MB, as needed, if the /var/lib/mysql has enough available space.



Or, multple ibdata files can be used.



Optionally, max can be used to specify the maximum size of the ibdata1 file.



Did you find this article helpful?

If so, consider buying me a coffee over at Buy Me A Coffee


Add a Comment

Please enter 4f37dd in the box below so that we can be sure you are a human.