Since I recently posted MariaDB client and sever options files, it’s only fitting that I post options files for MySQL. Since MariaDB and MySQL have diverged since version 5 when MariaDB was a drop-in replacement for MySQL, options files can no longer be shared between the two without modification.

MySQL has many options for both the client and server than can be adjusted (or tuned) to provide better performance. For most, the default settings are fine and never need to be adjusted. A few, however, should be adjusted for optimal performance. This is particular true for the InnoDB storage engine. The option innodb_buffer_pool_size, which sets the size of the in-memory buffer pool, is probably the most important option to boost performance. It can be set up to 80% of the total memory on the server, but the default is very inadequate 128 MB.

Another option is max_allowed_packet which is used by both client and the server. This sets the maximum size for a data packet or generated/intermediate string. It must be large enough to hold the largest record or BLOB. The client default is a very anemic 16 MB. The server default is better but only 64 MB.

The MySQL packages for Debian and Red Hat based distributions do install options files (also known as defaults files) for both the client and server, but they are largely placeholders setting very few or no options. And they contain no comments about any of the options. This makes it difficult to know which options should be adjusted for best performance. The options are documented on the MySQL reference manual site, but this means wading through a long list of options trying to find the ones of interest.

Rather than trying to piece together a new options file every time I installed MySQL server, I decided to create a template file with the option I might wish to adjust along with comments that describe each one and where to go for additional information. It also uses more optimized values for many options (for example, I increased max_allowed_packet to 128 MB). I then just replace the included options file with my template, adjust the options that make sense – almost always innodb_buffer_pool_size – and the server is ready to go with much better performance than if the defaults were use.

I also included comments for options that are handled differently on Debian verses Red Hat packages. Where the various files – socket, pid, log, and even the option file itself – are located are different for these distributions. And options that are ignored when systemd is used, which is becoming the default, are noted.

These are the options files I use when setting up MySQL for client servers, so they’ve been tested in real-world production environments.

Client File: mysql-client.cnf
Server File: mysql-server,cnf