Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the method of adding and deleting InnoDB data and log files". In the daily operation, I believe that many people have doubts about the methods of adding and deleting InnoDB data and log files. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "adding and deleting InnoDB data and log files". Next, please follow the editor to study!

-- from official documentation

Add and delete InnoDB data and log files

This section describes some of the things you can do when the InnoDB tablespace runs out of space, or when you want to change the log file size.

The simplest way to increase the size of the InnoDB table space is to configure it to extend automatically from the beginning. Specify the autoextend attribute for the last data file in the tablespace definition. Then when the file runs out of space, InnoDB automatically increases the size of the file in 8MB increments. The increased size can be configured by setting the innodb_autoextend_increment value, which is measured in MB, which defaults to 8.

Instead, you can increase the size of the table space by adding another data file. To do this, you must stop the MySQL server, edit the my.cnf file, add a new data file to the end of the innodb_data_file_path, and then start the server again.

If the last data file is defined with the keyword autoextend, the step of editing the my.cnf file must take into account how big the last data file has grown. Get the size of the data file, round it to the nearest product 1024 × 1024bytes (= 1MB), and then specify the approximate size explicitly 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 automatic extension.

As an example. Suppose the tablespace happens to have an auto-extension file, ibdata1:

Innodb_data_home_dir = innodb_data_file_path = / ibdata/ibdata1:10M:autoextend

Suppose the data file has grown to 988MB over time. The following is the configuration line after adding another total extension 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 table space, make sure it doesn't 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 size of the tablespace, use the following steps:

1. Use mysqldump to dump all InnoDB tables.

two。 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 shut down without error. Then copy the old log files to a safe place in case something goes wrong when shutting down 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 notices that there is no log file when it starts and tells you that it is creating a new log file.

At this point, the study on "how to add and delete InnoDB data and log files" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.