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
[root@db01 ~]# ll /mysql/mysql/data/ibdata3
-rw-rw—- 1 mysql mysql 47185920 Oct 1 15:06 /mysql/mysql/data/ibdata3
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:
innodb_data_file_path = ibdata1:50M;ibdata2:50M;ibdata3:45M;ibdata4:200M;:autoextend:max:500M
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.