If you enable innodb_file_per_table variable to use multiple InnoDB tablespaces, InnodDB creates files with a suffix of .ibd to store their data. The innodb_open_files variable defines the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is 10 and the default is 300.

For most installations, the default value of 300 to this variable is sufficient for use. But you can still increase this value to meet you needs as it proves to have little impact on the system performance.

How to set innodb_open_files variable in MySQL with VPSrobots?

First, access your servers via VPSrobots Control Panel, with that done:

  1. Click on Breadcrumb Setting tab >> MySQL Setting Section >> Click More >> MySQL tab page.

    Breadcrumb Setting tab page and Mysql Setting section

  2. Use the search box to locate the variable setting line in blue in MySQL Config sub-tab.
  3. Click Edit in the line >> Set a value to this variable >> Click OK >> Click Apply.

How to set innodb_open_files in MySQL with command prompt?

Note that the commands below are valid for a Debian server with MySQL 5.5.59.

First of all, log in to your server via PuTTY.

Once done, type the command below to open MySQL configuration:

nano /etc/mysql/my.cnf

Next, add the following setting to any line between [mysqld] and [mysqldump] lines in the configuration:

innodb_open_files = 301

When you finish setting, press Ctrl + X, type Y to save changes and back to the terminal.

Finally, you’ll need to restart the MySQL service to make the change take effect:

service mysql restart

Now you’re all set to configure innodb_open_files variable for MySQL in your own project. You may also want to read: How to set low_priority_updates Variable in MySQL with VPSrobots.