This section describes how to increase or decrease the size of the
InnoDB system tablespace.
The easiest way to increase the size of the
InnoDB system 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
increases the size of that file automatically in 64MB increments
when it runs out of space. The increment size can be changed by
setting the value of the
system variable, which is measured in megabytes.
You can expand the system tablespace by a defined amount by adding another data file:
Shut down the MySQL server.
If the previous last data file is defined with the keyword
autoextend, change its definition to use a fixed size, based on how large it has actually grown. Check the size of the data file, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify this rounded size explicitly in
Start the MySQL server again.
For example, this tablespace has just one auto-extending data file
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 use a fixed size and adding a new auto-extending data file:
innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
When you add a new data file to the system tablespace
configuration, make sure that the filename does not refer to an
InnoDB creates and initializes
the file when you restart the server.
You cannot remove a data file from the system tablespace. To decrease the system tablespace size, use this procedure:
Use mysqldump to dump all your
InnoDBtables located in the MySQL database.
mysql> SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB'; +---------------------------+ | TABLE_NAME | +---------------------------+ | columns_priv | | component | | db | | default_roles | | engine_cost | | func | | global_grants | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_dynamic_metadata | | innodb_index_stats | | innodb_table_stats | | plugin | | procs_priv | | proxies_priv | | role_edges | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+
Stop the server.
Remove all the existing tablespace files (
*.ibd), including the
ib_logfiles. Do not forget to remove
*.ibdfiles for tables located in the MySQL database.
Configure a new tablespace.
Restart the server.
Import the dump files.
If your databases only use the
it may be simpler to dump all
databases, stop the server, remove all databases and
InnoDB log files, restart the server, and
import the dump files.