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.
rm -f /var/lib/mysql/ib*
- Add the following directive to /etc/my.cnf
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.