MySQL Troubleshooting

Chapter 3. Effects of Server Options

The MySQL server provides an enormous number of options that you can set in various ways: in the my.cnf configuration file, on the command line that starts the server, or by setting variables at runtime. Most MySQL variables can be set dynamically, and there is generally one variable corresponding to each configuration option.

Some of the options are global, some apply to specific storage engines, some, called session, apply to connection, and some apply to particular activities, such as replication. This chapter is not a general guide to MySQL server options, but covers the problems that some can create or the ways in which changing an option can help you troubleshoot MySQL.

Note

Before embarking on this chapter, we need to agree on certain terms.

I will use both option and variable to denote a server option. MySQL uses a separate syntax for options and variables: options are usually spelled with hyphens (variable-name), whereas the corresponding variables are spelled with underscores (variable_name). Usually the MySQL server supports both types of syntax for the command line and configuration file, but supports only the variable_name syntax for variables. Therefore, we will use the latter syntax in this book whenever it is supported.

Variables can be split into different groups depending on their purpose: to point the server to directories, to limit hardware resources, to change how mysqld treats one or another situation, and so on. They can also ...