MySQL Admin: Configuring InnoDB and Installing MySQL as a Windows Service

In order to prevent the transactional nature of InnoDB from completely thwarting its performance, it implements what is called the redo log.

In this recipe, we will present the relevant settings to (re-)configure a database server’s redo log.

Getting ready

As the redo log setup is a part of the server configuration, you will need an operating system user account and sufficient rights to modify the server’s configuration file. You will also need rights to restart the MySQL service because the redo log cannot be reconfigured on the fly.

Moreover, an administrative MySQL user account is required to prepare the server for the shutdown, necessary as part of the procedure.

Caution:
As this recipe will modify the configuration of parameters critical to data integrity, you should make a backup copy of the configuration file before editing it!

How to do it…

1. Connect to the server using your administrative account.
2. Issue the following command:

   mysql> SET GLOBAL innodb_fast_shutdown=0;
   Query OK, 0 rows affected (0.00 sec)

Verify the setting like this:

mysql> SHOW VARIABLES LIKE 'innodb_fast_shutdown';

3. Log off from MySQL and stop the MySQL server.
4. Locate the MySQL configuration file, usually called my.cnf or my.ini (on Windows) and open it in a text editor.
5. Locate the following parameters in the [mysqld] section (you values will vary, of course):

   [mysqld]
   ...
   innodb_log_group_home_dir=/var/lib/mysql/redolog
   innodb_log_file_size=32M
   innodb_log_buffer_size=64M
   innodb_log_files_in_group=2
   ...

6. Edit the above configuration settings to their new values. If you require help on how to find suitable values, see the There’s more… section of this recipe.
7. Save the configuration file.
8. Navigate to the directory configured for innodb_log_group_home_dir. If there is no such setting in your configuration file, navigate to MySQL’s data directory that is then taken as the default.
9. Move the files whose names start with ib_logfile to a backup location. Do not copy them; they must be removed from their original location.
10. Restart the MySQL server.
11. Verify that new files are created as you configured them:

    $ ls -l /var/lib/mysqld/redolog

If you do not see the new files appear and the server does not start up correctly, check the MySQL error log for messages. Usually, the only thing that can go wrong here is that you either mistyped the directory name or did not actually remove the previous ib_logfile files. To restore everything back to the original configuration, restore your configuration file from the backup and restore the ib_logfile files you moved out to the backup to their original location.

What just happened…

By setting innodb_fast_shutdown to 0, you told the server to finish writing any pending changes to the disk before actually exiting. This makes sure there are no remaining transactions in the current redo logs that could get lost when these files are replaced. After that you could change the configuration to new values, possibly using a different number of files and different sizes.

Then, before restarting, you could move the old redo log files out of the way. This is important because otherwise MySQL would complain about a mismatch between the settings file and the actual situation on disk. When it comes up finding no redo log files, it will create new ones with the settings just configured.