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


MySQL Admin Cookbook

MySQL Admin Cookbook

99 great recipes for mastering MySQL configuration and administration

  • Set up MySQL to perform administrative tasks such as efficiently managing data and database schema, improving the performance of MySQL servers, and managing user credentials
  • Deal with typical performance bottlenecks and lock-contention problems
  • Restrict access sensibly and regain access to your database in case of loss of administrative user credentials
  • Part of Packt's Cookbook series: Each recipe is a carefully organized sequence of instructions to complete the task as efficiently as possible

Read more about this book

(For more resources on MySQL, see here.)

Configuring the InnoDB redo log

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.

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)

  3. Verify the setting like this:

    mysql> SHOW VARIABLES LIKE 'innodb_fast_shutdown';

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


  7. 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.
  8. Save the configuration file.
  9. 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.
  10. 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.
  11. Restart the MySQL server.
  12. 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.

Read more about this book

(For more resources on MySQL, see here.)

There's more...

Often when talking about transactions, the word rollback comes up. It means that if something goes wrong in the middle of a possibly complex data manipulation operation and it has to be aborted, the database server will safely restore everything back to the state it was in when that operation began, not leaving any data only partially deleted or modified.

The opposite term—rollforward or redoing—is less commonly used. It means that whenever the complex operation completes successfully, you are guaranteed that nothing short of actual hardware failure could lead to these changes being lost again.

This might appear obvious because one would expect the database server not to report anything as successful unless it was actually completely done. However, if that were so, write operations would become painfully slow, as the underlying I/O subsystem (generally meaning hard disks) is very often the bottleneck component.

To evade this potential performance problem, most transactional databases, InnoDB being no different, uses the concept of a transaction—or redo—log that allows it to more efficiently handle write operations without risking data integrity. The redo log works as a sort of scratch pad, containing information on what remains to be done to the data files. With its help, the server can optimize disk access to improve performance.

The ideal size for the redo log depends on the size and number of transactions the server has to process. Generally speaking, the log should be large enough to store any single transaction plus about 10 percent. As a rule of thumb, the total log size (the number of log files times their individual size) need not exceed about 50% of the InnoDB buffer pool size.

For more information on redo logs and how to determine a sensible size setting, visit for a detailed description of InnoDB log configuration.

Understanding and configuring important MySQL and InnoDB timeout options

MySQL's configuration file can contain a variety of different timeout settings, each responsible for a specific kind of operation or connection. In this recipe, we present a selection of these timeout settings and a suggested value to go along with each. The How it works... section has details on each value presented.

In general, the values suggested here should be appropriate for both MySQL versions 5.0 and 5.1. However, please note that any of these options may well vary for your environment, depending on what the requirements are; so please do not simply use these values verbatim.

Getting ready

To apply timeout configuration settings, you will need access to the MySQL configuration file—typically my.cnf or my.ini (on Windows)—and the rights to restart the server to have any changes made to the configuration take effect.

How to do it...

  1. Locate the MySQL configuration file and open it in a text editor.
  2. In the [mysqld] section, set up the following values. Some of the options may already be present; others will likely have to be added. Make sure each option only appears once.




  3. Save the file.
  4. Restart the MySQL server.

How it works...

By setting the values as described in the above section, you tell MySQL to use different values than the defaults for the options mentioned. The new settings take effect with the server restart.

Setting values for innodb_rollback_on_timeout / innodb_lock_wait_ timeout

These two settings are probably the most important as regards the locking behavior in any MySQL setup. Starting with version 5.0.13, MySQL changed the rollback behavior in case a timeout occurred because a transaction could not acquire a lock for a row. This usually happens when another transaction is still working on that row, and this is to be expected in normal database operations. The database server rolls back the entire transaction in this case. Applications should be designed to respond to such conditions by retrying the entire transaction.

Prior to 5.0.13, this was the default behavior, maintaining the rule that a transaction either succeeds or fails and is rolled back completely as an atomic entity. In 5.0.13 and newer versions, the default was modified to roll back only the very last statement of the failing transaction instead, keeping the transaction open. While there is a reason behind that change (for long transactions, it can be faster to just retry the very last statement than wait for the rollback and try again from the start), it requires special precautions taken on the application level to be able to handle this very MySQL-specific scenario.

Setting the innodb_rollback_on_timeout option in the configuration file restores the more standard way of rolling back the whole transaction in case of a lock wait timeout. We strongly recommend enabling this option unless you are perfectly sure your application is aware of the MySQL-specific behavior configured as the default.

innodb_lock_wait_timeout defines the number of seconds a transaction will wait to acquire a necessary lock when a second transaction is working concurrently on the same records. The default value is 50 seconds and if the lock could not be acquired by then, a timeout error will occur and the transaction will be rolled back. Depending on how long the transactions in your setup typically take, this value often needs to be adjusted. If you regularly have bulk data operations that affect a lot of rows, you will want to increase this value. If on the other hand your system normally uses very short transactions, reducing this value may help you find out about problems with lock contention earlier.

Setting values for interactive_timeout / wait_timeout

interactive_timeout defines how long an interactive client connection can be idle before the server closes it automatically. 'Idle' in this context refers to the time between two statements being executed with no activity in between.

We recommend reducing this from the default value of 28,800 seconds (8 hours) to a much lower value like 1,200 seconds (20 minutes). This allows the server to close idle connections and conserve some resources.

The counterpart variable for non-interactive sessions, such as those from an application server's connection pool, is called wait_timeout and has the same semantics. Depending on your application, you might want to leave this setting on a higher value as most connection pools can be configured to release connections automatically depending on current load conditions.

Setting values for net_read_timeout / net_write_timeout

The protocol MySQL uses to handle communication between server and clients is rather limited in design, allowing only one operation to be carried out at a time. A side effect of this is that once a data transfer in either direction has started, there is no way for it to be interrupted in a controlled manner.

The net_read_timeout controls how long a piece of information can be sent from the client to the server, before the connection is aborted. This is usually not a problem—the default setting is 30 seconds. Under no regular circumstances will a communication in that direction take so long.

The net_write_timeout is more problematic because for large result sets, the default value of 60 seconds might be too short. This is especially true for clients that fetch the result in streaming mode, potentially performing time-consuming operations on each row retrieved, thereby making the overall operation take longer than the timeout.

The exact value required for your setup depends on how clients fetch data and work with it; so you will need to experiment and find a suitable value.

One caveat to consider with net_write_timeout is that it may lead to seemingly random failures of mysqldump like this:

mysqldump: Error 2013: Lost connection to MySQL server during query
when dumping table `tablename` at row: 935578

This can happen if the following conditions apply:

  • net_write_timeout is set to a low value
  • max_allowed_packet is set to a large value

Depending on the speed of the network over which mysqldump has connected and the size of the rows being dumped, it may be necessary to increase net_write_timeout much higher, at least to as long as it takes to transmit max_allowed_packet bytes over the network and write it to the output.

mysqldump is a regular client program and subject to the net_write_timeout setting. When the server sends rows to be dumped to mysqldump in chunks of up to max_allowed_packet bytes, depending on the network connection in between, this might take longer than net_write_timeout allows, making the server cut the connection even though nothing is really wrong. Increasing net_write_timeout for the mysqldump tool's session would remedy this, but unfortunately as of the time of writing there is no such setting for mysqldump. A workaround, if you encounter this problem, is to temporarily increase the global server net_write_timeout value:

$ mysql -uroot -e "SELECT @@GLOBAL.net_write_timeout AS oldvalue;
SET GLOBAL net_write_timeout=600;"
$ mysqldump ...
$ mysql -e "SET GLOBAL net_write_timeout=oldvalue;"

The first command will display the current value for net_write_timeout and then set it to 10 minutes. After that the mysqldump can take place. Finally, the old value is reset (just make sure you fill in the correct old value).

See MySQL Bug #46103 at for more details.

Read more about this book

(For more resources on MySQL, see here.)

Adjusting table and database name letter case handling for better platform independence

MySQL is available for a variety of platforms—the major ones being Windows and Linux. Even though data files are compatible and can be transferred between platforms, and configuration mostly follows the same general principles, there is an important caveat to know about how different operating systems handle file names.

In this recipe, we will show you how to set up MySQL in a way such that it is much less likely to run into problems when moving data files between platforms. Because MySQL databases and tables correlate to file system objects (directories and files), differences in how the operating system (or rather the file system) handles file and directory names can lead to undesired effects, especially when working in heterogenous environments.

We generally recommend setting up all your MySQL servers as described in this recipe to prevent any problems.

Getting ready

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 name handling cannot be reconfigured on the fly.

Please note that for best results this setting should be applied before you start creating databases and tables on that server.

How to do it...

  1. Make sure MySQL is not running.
  2. Locate the MySQL configuration file, usually called my.cnf or my.ini (on Windows) and open it in a text editor.
  3. Locate the following parameter in the [mysqld] section. If it is not there, add it, otherwise edit it to match the value shown here:

    lower_case_table_names = 1

  4. Save the configuration file.
  5. Restart the MySQL server.

How it works...

MySQL table and database names are mapped to file system names. Most Unix-like platform file systems are case sensitive, meaning that the two files TableA and tablea are different from each other. On Windows, these two names will refer to the very same file.

Setting the lower_case_table_names configuration to 1 tells MySQL to always convert any database or table names to lowercase letters, both when creating and using them in SQL statements. This will ensure that no matter what casing any SQL statements use, it will always affect the same tables.

This is especially useful in replication scenarios where you replicate between master and slave machines using different operating systems.

Manually configuring this setting is highly recommended because depending on which platform MySQL is run on, the default setting will vary!

The only downside of setting up MySQL in this way is that the output of SHOW TABLES or SHOW DATABASES commands do not preserve the casing in which databases or tables were created, but this is merely a cosmetic issue.

Installing MySQL as a Windows service with custom options

While for development purposes it can be very handy to have MySQL run as a console application on Windows, for regular operations a background service is the option to go for. It has the advantage of starting up and shutting down automatically with Windows without the need for a user to log in to the machine.

In this recipe, we will show you how to install MySQL as a Windows service manually from the ZIP distribution available from the MySQL homepage and specify a custom configuration file.

Getting started

Naturally, this is a Windows-only recipe. You will need a Windows user account with administrative privileges to register a new Windows service. Moreover, we assume you have already downloaded the MySQL distribution called "Without installer (unzip in C:\)".

Make sure you choose the release matching your operating system (32 or 64 bit). In this recipe we will be using MySQL 5.1 from

Be advised that security software on your computer might interfere with the installation of a Windows service, as some malicious software may try to hook into the system that way. If you encounter problems, you may have to disable anti-virus programs and other security products for the duration of the process. Do not forget to re-enable them when you are finished with the MySQL service setup!

How to do it...

  1. Unpack the downloaded ZIP file. Put the contents in c:\mysql\5.1.xx\service(replacing xx with the actual release number you are using).
  2. On a command prompt (cmd.exe) enter the following commands to install the service. Make sure to enter the full path, instead of changing the working directory with the cd command:

    c:\> c:\mysql\5.1.xx\service\bin\mysqld.exe --install MySQL51

  3. Edit the my.ini configuration file specified in the command above to meet your requirements.
  4. Start the service and verify its status using these commands:

    c:\> sc start MySQL51

    c:\> sc query MySQL51

You should see STATE: 4 RUNNING in the status output of the second command.

How it works...

The MySQL server binary executable file mysqld.exe contains the necessary functionality to register itself with Windows as a background service. There are two options you should provide: --install and --defaults-file. The first one will specify the name of the new service to be created, MySQL51 in this case. The latter is used to define which configuration file the service will read its settings from.

Note that after the --install parameter, only a single parameter may follow. While this could be any parameter the MySQL server accepts, using the --default-file gives you the greatest flexibility, as you can put all other required settings there.

There's more...

Apart from being able to run without a user having to log in to the server machine, services can define dependencies; so, for example, you could make sure your application server only gets started when the database is ready. For details on how to do this, refer to Microsoft Knowledge Base article #193888 at

As services do not have access to a console or the graphical user interface in general, any problems encountered while starting the service will not tell you anything about the cause. On Windows, MySQL will report problems to the system event log, viewable from the control panel, and to the MySQL error log file, usually located in the data directory with a name composed from the machine name and a .err extension.

Should your service fail to start, inspect that log file to get an idea of what is wrong. To make sure your configuration file is OK, we recommend you to start the MySQL daemon once from the command line like this:

c:\> c:\mysql\5.1.xx\service\bin\mysqld.exe --defaults-file=c:\
mysql\5.1.xx\service\my.ini --console

This will allow you to see any potential problems right away before installing the service.


In the above article we have covered:

  • Configuring the InnoDB redo log
  • Understanding and configuring important MySQL and InnoDB timeout options
  • Adjusting table and database name letter case handling for better platform independence
  • Installing MySQL as a Windows service with custom options

Further resources on this subject:

You've been reading an excerpt of:

MySQL Admin Cookbook

Explore Title