14.5.7 Changing the Number or Size of InnoDB Log Files and Resizing the InnoDB Tablespace

This section describes how to change the number or size of InnoDB redo log files and how to increase or decrease InnoDB system tablespace size.

Changing the Number or Size of InnoDB Log Files

To change the number or size of InnoDB redo log files in MySQL 5.6.7 or earlier, perform the following steps:

  1. If innodb_fast_shutdown is set to 2, set innodb_fast_shutdown to 1:

    mysql> SET GLOBAL innodb_fast_shutdown = 1;
    
  2. After ensuring that innodb_fast_shutdown is not set to 2, 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 log).

  3. Copy the old log files into a safe place in case something went wrong during the shutdown and you need them to recover the tablespace.

  4. Delete the old log files from the log file directory.

  5. Edit my.cnf to change the log file configuration.

  6. Start the MySQL server again. mysqld sees that no InnoDB log files exist at startup and creates new ones.

As of MySQL 5.6.8, the innodb_fast_shutdown setting is no longer relevant when changing the number or the size of InnoDB log files. Additionally, you are no longer required remove old log files, although you may still want to copy the old log files to a safe place, as a backup. To change the number or size of InnoDB log files, perform the following steps:

  1. Stop the MySQL server and make sure that it shuts down without errors.

  2. Edit my.cnf to change the log file configuration. To change the log file size, configure innodb_log_file_size. To increase the number of log files, configure innodb_log_files_in_group.

  3. Start the MySQL server again.

If InnoDB detects that the innodb_log_file_size differs from the redo log file size, it will write a log checkpoint, close and remove the old log files, create new log files at the requested size, and open the new log files.

Increasing the Size of the InnoDB 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 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 megabytes.

You can expand the system tablespace by a defined amount by adding another data file:

  1. Shut down the MySQL server.

  2. 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 innodb_data_file_path.

  3. Add a new data file to the end of innodb_data_file_path, optionally making that file auto-extending. Only the last data file in the innodb_data_file_path can be specified as auto-extending.

  4. Start the MySQL server again.

For example, this 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 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 existing file. InnoDB creates and initializes the file when you restart the server.

Decreasing the Size of the InnoDB Tablespace

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

  1. Use mysqldump to dump all your InnoDB tables, including InnoDB tables located in the MySQL database. As of 5.6, there are five InnoDB tables included in the MySQL database:

    mysql> select table_name from information_schema.tables where table_schema='mysql' and engine='InnoDB';
    +----------------------+
    | table_name           |
    +----------------------+
    | innodb_index_stats   |
    | innodb_table_stats   |
    | slave_master_info    |
    | slave_relay_log_info |
    | slave_worker_info    |
    +----------------------+
    5 rows in set (0.00 sec)
          
  2. Stop the server.

  3. Remove all the existing tablespace files (*.ibd), including the ibdata and ib_log files. Do not forget to remove *.ibd files for tables located in the MySQL database.

  4. Remove any .frm files for InnoDB tables.

  5. Configure a new tablespace.

  6. Restart the server.

  7. Import the dump files.

Note

If your databases only use the InnoDB engine, 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.