sysdba.org

Information
innodb_data_home_dir: the default location of the datafiles.

innodb_data_file_path: default datafile names and size information.

note: If no values have been entered into innodb_data_home_dir, then they should be included with with the path, e.g. innodb_data_file_path = ibdata1:50M;ibdata2:50M;ibdata3:50M:autoextend:max:100M.

The previous line indicates that there are 3 datafiles that are 50MB large and that the maximum datafile size is 100MB.

Important: To add datafiles to MySQL the database should first be closed.

This is why it’s important for programmers/developers to know how much space is required beforehand and to make annual plans for the database.

Getting the total size of ibdata3 prior to adding the datafile

[box type=”info”]
[root@db01 ~]# ll /mysql/mysql/data/ibdata3
-rw-rw—- 1 mysql mysql 47185920 Oct 1 15:06 /mysql/mysql/data/ibdata3
[/box]

The size in MB can be calculated by dividing 52428800 by 1024 twice.

To add data files while the size is 45MB, use the following line:

[box type=”info”]
innodb_data_file_path = ibdata1:50M;ibdata2:50M;ibdata3:45M;ibdata4:200M;:autoextend:max:500M
[/box]

3 datafiles were written as 45mb and the 4th datafile was added as 200MB with a capacity of up to 500MB.
To place the datafiles in a different location, the path should be entered as /full/path/ibdata1:50M.