14.2.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:
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 (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.
