Add and delete InnoDB data and log files

-- From mysql official documentation add and delete InnoDB data and log files
This section describes what you can do when InnoDB tablespace consumes space or when you want to change the log file size. The simplest way to increase the InnoDB tablespace size is to configure it to automatically expand from the beginning. Specify the autoextend attribute for the last data file in the tablespace definition. When the file is exhausted, InnoDB automatically increases the file size in 8 Mb increments. You can set the value of innodb_autoextend_increment to increase the size. The value is in MB and the default value is 8.
As an alternative, you can add another data file to increase the size of the tablespace. To do this, you must stop the MySQL server, edit the my. cnf file, add a new data file to the end of innodb_data_file_path, and then start the server again. If the last data file is defined with the keyword autoextend, the steps to edit the my. cnf file must consider the size of the last data file. Obtain the size of the data file, round it to the nearest product of 1024 × 1024 bytes (= 1 MB), and specify the approximate size in innodb_data_file_path. Then you can add another data file. Remember that only the last data in innodb_data_file_path can be specified as auto scaling.
As an example. Assume that the tablespace has an automatic extension file ibdata1: innodb_data_home_dir = innodb_data_file_path =/ibdata/ibdata1: 10 M: autoextend. Assume that the data file has grown to 988 MB after a while. The following is the configuration line after adding another total extended data file: innodb_data_home_dir = innodb_data_file_path =/ibdata/ibdata1: 988 M;/disk2/ibdata2: 50 M: autoextend when you add a new file to the tablespace, make sure it does not exist. When you restart the server, InnoDB creates and initializes this file.
Currently, You cannot delete a data file from a tablespace. To increase the tablespace size, perform the following steps: 1. Use mysqldump to dump all InnoDB tables. 2. Stop the server. 3. Delete all existing tablespace files. 4. Configure the new tablespace. 5. Restart the server. 6. Import the dump file.
If you want to change the number and size of your InnoDB log files, you must stop the MySQL server and make sure it is disabled without errors. Then copy the old log files to a safe place in case something goes wrong when it is closed and you need to use them to restore the tablespace. Delete all old log files from the log file directory, edit my. cnf to change the log file configuration, and start the MySQL server again. Mysqld finds no log file at startup and tells you that it is creating a new log file. Author alang85