MySQL InnoDB files may be added for different purposes.
Increase DB tablespace when running out of the tablespace.
Use another disk partition when running out of the disk space on the partition of the current InnoDB files resident.
Innodb_data_file_path allows adding another file next to the current file. For example,
innodb_data_file_path=ibdata1:2000M:ibdata2:2000M
In the above example, when database needs more space beyond the prescribed size for the first file (ibdata1, 2G), the information (insert) start being saved in the second file (ibdata2).
If you want the last added database file to be autoextended, please put “autoextended” option to the last.
innodb_data_file_path=ibdata1:2000M:ibdata2:2000M:autoextended
If the current configuration specifies one database file and it cannot be extended due to the disk space or max_file_size option, adding second database file in different location is possible.
For example, an original configuration is:
innodb_data_file_path=ibdata1:2000M
Then to add second database file and specify the relative path :
innodb_data_file_path=ibdata1:2000M:<new relative path to ibdata2>/ibdata2:2000M:autoextended
if autoextended option has been used in the first database file, it becomes complicated since we need to write the exact database file size before adding a new database file.
For example, an original configuration is:
innodb_data_file_path=ibdata1:2000M:autoextended
Then to add second database file and specify the relative path :
innodb_data_file_path=ibdata1:<current ibdata1 size>:<new relative path to ibdata2>/ibdata2:2000M:autoextended
The current file size of ibdata1 should be specified in units of MB.
The following steps explain adding procedures.
Dump or copy original database files to a safe location
Stop mysqld
service mysqld stop
Create new directory in new partition for the second database file
mkdir /new_mysql_dir
chown -R mysql:mysql new_mysql_dir
Edit my.cnf to use new directory for the second InnoDB database file location
Original my.cnf
innodb_data_file_path=ibdata1:2000M:autoextended
New my.cnf
innodb_data_file_path=ibdata1:<current ibdata1 size>:ibdata2:2000M:autoextended
It is possible to use innodb_data_home_dir with innodb_data_file_path. For example,
Innodb_data_home_dir= (this empty setting means top "/" directory)
Innodb_data_file_path=/var/lib/mysql/ibdata1:size;/new_mysql_dir/ibdata2:2G:autoextend
If you don’t want to use innodb_data_home_dir, you can use relative path as the following example.
If the current ibdata1 file size is 3G, then
Innodb_data_file_path=var/lib/mysql/ibdata1:3G;../../../new_mysql_dir/ibdata2:2000M:autoextend
One thing to be cautious is to set the file size for original file. Since the autoextend option was removed, mysql could not recognize the increased file size. So specify the current filesize of iddata1 (in this example, 3G)
Start mysqld
service mysqld start
If MySQL fails to start after moving, it would be one of three reasons:
Misconfiguration in my.cnf
Inappropriate permission to new directory
SELinux issue. For the SELinux issue, please refer appendix: SELinux setting for new MySQL directory.