To change the number or size of
InnoDB redo log
files in MySQL 5.6.7 or earlier, perform the following steps:
SET GLOBAL innodb_fast_shutdown = 1;
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).
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.
Delete the old log files from the log file directory.
my.cnf to change the log file
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
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.
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
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
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
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 existing file.
InnoDB creates and initializes the file when you
restart the server.
Currently, 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
InnoDB tables, including
InnoDB tables located in the MySQL database.
As of 5.6, there are five
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)
Stop the server.
Remove all the existing tablespace files
*.ibd), including the
files. Do not forget to remove
for tables located in the MySQL database.
.frm files for
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.