Add InnoDB File

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.

1. Dump or copy original database files to a safe location

2. Stop mysqld

   service mysqld stop
   

3. Create new directory in new partition for the second database file

   mkdir /new_mysql_dir
   chown -R mysql:mysql new_mysql_dir
   

4. 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
   

5. 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
   

6. 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)

7. Start mysqld

   service mysqld start
   

If MySQL fails to start after moving, it would be one of three reasons:

1. Misconfiguration in my.cnf

2. Inappropriate permission to new directory

3. SELinux issue. For the SELinux issue, please refer appendix: SELinux setting for new MySQL directory.