(mysql.info.gz) Adding and removing
Info Catalog
(mysql.info.gz) Using InnoDB tables
(mysql.info.gz) InnoDB
(mysql.info.gz) Backing up
15.8 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.
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.0.24 and 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 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 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:
1. Use `mysqldump' to dump all your `InnoDB' tables.
2. Stop the server.
3. Remove all the existing tablespace files.
4. Configure a new tablespace.
5. Restart the server.
6. 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.
Info Catalog
(mysql.info.gz) Using InnoDB tables
(mysql.info.gz) InnoDB
(mysql.info.gz) Backing up
automatically generated byinfo2html