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.
From MySQL 3.23.50 and 4.0.2, 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 will increase the size of that file automatically in 8MB increments when it runs out of space. Starting with MySQL 4.1.5, the increment size can be configured with the option innodb_autoextend_increment, in megabytes. The default value is 8.
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, edit the my.cnf file to add a new data file to the end of innodb_data_file_path, and start the server again.
If your last data file already was defined with the keyword autoextend, the procedure to edit my.cnf must take into account the size to which the last data file has grown. You have to look at the size of the data file, round the size downward 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 that this data file, over time, has grown to 988MB. Below is the configuration line after 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, make sure that it does not exist. InnoDB will create and initialize it 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:
Use mysqldump to dump all your InnoDB tables.
Stop the server.
Remove all the existing tablespace files.
Configure a new tablespace.
Restart the server.
Import the dump files.
If you want to change the number or the size of your InnoDB log files, you have to stop the MySQL server and make sure that it shuts down without errors. Then copy the old log files into a safe place just in case something went wrong in the shutdown and you will 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 will see that the no log files exist at startup and tell you that it is creating new ones.