Skip navigation links

Additional languages
MySQL 5.1 Reference Manual :: 13 Storage Engines :: 13.5 The InnoDB Storage Engine :: 13.5.7 Adding and Removing InnoDB Data and Log Files
13.5.8 Backing Up and Recovering an InnoDB Database »
Section Navigation      [Toggle]

13.5.7. Adding and Removing InnoDB Data and Log Files

This section describes what you can do when your InnoDB tablespace runs out of room or when you want to change the size of the log files.

The easiest way to increase the size of the InnoDB tablespace is to configure it from the beginning to be auto-extending. Specify the autoextend attribute for the last data file in the tablespace definition. Then InnoDB increases the size of that file automatically in 8MB increments when it runs out of space. The increment size can be changed by setting the value of the innodb_autoextend_increment system variable, which is measured in MB.

Alternatively, you can increase the size of your tablespace by adding another data file. To do this, you have to shut down the MySQL server, change the tablespace configuration to add a new data file to the end of innodb_data_file_path, and start the server again.

If your last data file was defined with the keyword autoextend, the procedure for reconfiguring the tablespace must take into account the size to which the last data file has grown. Obtain the size of the data file, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify the rounded size explicitly in innodb_data_file_path. Then you can add another data file. Remember that only the last data file in the innodb_data_file_path can be specified as auto-extending.

As an example, assume that the tablespace has just one auto-extending data file ibdata1: 

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

Suppose that this data file, over time, has grown to 988MB. Here is the configuration line after modifying the original data file to not be auto-extending and adding another auto-extending data file: 

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

When you add a new file to the tablespace configuration, make sure that it does not exist. InnoDB will create and initialize the file when you restart the server.

Currently, you cannot remove a data file from the tablespace. To decrease the size of your tablespace, use this procedure:

  1. Use mysqldump to dump all your InnoDB tables.

  2. Stop the server.

  3. Remove all the existing tablespace files, including the ibdata and ib_log files. If you want to keep a backup copy of the information, then copy all the ib* files to another location before the removing the files in your MySQL installation.

  4. Remove any .frm files for InnoDB tables.

  5. Configure a new tablespace.

  6. Restart the server.

  7. Import the dump files.

If you want to change the number or the size of your InnoDB log files, use the following instructions. The procedure to use depends on the value of innodb_fast_shutdown:

  • If innodb_fast_shutdown is not set to 2: You must stop the MySQL server and make sure that it shuts down without errors (to ensure that there is no information for outstanding transactions in the logs). Then copy the old log files into a safe place just in case something went wrong in the shutdown and you need them to recover the tablespace. Delete the old log files from the log file directory, edit my.cnf to change the log file configuration, and start the MySQL server again. mysqld sees that no log files exist at startup and tells you that it is creating new ones.

  • If innodb_fast_shutdown is set to 2: You should shut down the server, set innodb_fast_shutdown to 1, and restart the server. The server should be allowed to recover. Then you should shut down the server again and follow the procedure described in the preceding item to change InnoDB log file size. Set innodb_fast_shutdown back to 2 and restart the server.


User Comments

Posted by Nickolay Pelov on February 10 2006 1:02pm[Delete] [Edit]

If you need to add/remove or change InnoDB datafiles you could change all InnoDB tables type to myisam:
ALTER TABLE table_name TYPE = myisam;
Then restart mysql server with new configuration and then change all tables type back to InnoDB:
ALTER TABLE table_name TYPE = innodb;

I'm not quite familiar with innodb storage engine, but there may some differences between innodb and myisam. InnoDB don't support FULLTEXT indexes, but in our case this is not a problem.

Posted by Matthew Montgomery on September 15 2006 11:05pm[Delete] [Edit]

In the event you want to reduce the disk space allocated to the innodb you could follow the steps above. However, deviate from the instructions by setting innodb_file_per_table = 1 at some point before you re-import the tables. This will place all innodb tables into their own datafile. In the future you would be able to reclaim the excess table space on a per-table basis.

Simply run : ALTER TABLE `tbl_name` ENGINE=INNODB

Posted by Jiju Thomas Mathew on July 14 2006 6:03pm[Delete] [Edit]

I did follow the above instructions and made my time easy by making a shell script kit, see my docs at http://www.saturn.in/gpl/mysql.html

Add your own comment.