MySQL Configuration File

Summary: in this tutorial, you will explore the MySQL configuration file, discover its location, and understand its structure.

Introduction to MySQL configuration file

MySQL programs such as mysqld, mysqladmin, mysqldump, and so on offer a convenient way to configure and manage commonly used options through option files, also known as configuration files.

The configuration files allow you to avoid entering command line options every time you execute a program.

Checking if a MySQL program reads the option files

To determine whether a MySQL program reads the configuration file, you follow these steps:

First, open the Terminal.

Second, execute the following command:

program --verbose --help

For example, you can use the following command to check which configuration file the mysqld program uses:

mysqld --verbose --help

If the mysqld reads configuration files, the help message will indicate which files it looks for and which option groups it recognizes.

Since the command returns a very long output, on Linux and macOS, you can use the less command to show the first page:

mysqld --verbose --help | less

On Windows, you can use the more command:

mysqld --verbose --help | more

Note that to escape the output, you press the letter q.

Here’s the extracted output that is relevant to the configuration file and section groups:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysql clientCode language: PHP (php)

MySQL configuration file processing order

The output shows that the mysqld program reads the configuration file in the following order:

/etc/my.cnf
/etc/mysql/my.cnf
~/.my.cnf

If multiple files exist, the mysqld will read all of them in the order. The order of reading option files is important because options specified later can override options specified earlier.

Also, the output mentioned that the mysqld will read two sections: mysql and client. In other words, it will read the parameters in the [mysq] and [client] sections.

On Windows

MySQL programs read startup options from the following files in the specified order:

• %WINDIR%\my.ini, %WINDIR%\my.cnf: Global options
• C:\my.ini, C:\my.cnf: Global options
• BASEDIR\my.ini, BASEDIR\my.cnf: Global options
• defaults-extra-file: File specified with --defaults-extra-file, if any
• %APPDATA%\MySQL\.mylogin.cnf: Login path options (clients only)
• DATADIR\mysqld-auto.cnf: System variables persisted with SET PERSIST or SET PERSIST_ONLY (server only)

On Unix and Unix-like Systems

MySQL programs read startup options from the following files in the specified order:

• /etc/my.cnf: Global options
• /etc/mysql/my.cnf: Global options
• SYSCONFDIR/my.cnf: Global options
• $MYSQL_HOME/my.cnf: Server-specific options (server only)
• defaults-extra-file: File specified with --defaults-extra-file, if any
• ~/.my.cnf: User-specific options
• ~/.mylogin.cnf: User-specific login path options (clients only)
• DATADIR/mysqld-auto.cnf: System variables persisted with SET PERSIST or SET PERSIST_ONLY (server only)

MySQL configuration file syntax

The configuration file consists of one or more sections. Each section starts with a square bracket ([]) and followed by one or more parameters.

For example:

[mysqld]
datadir=/var/lib/mysql
port=3306Code language: JavaScript (javascript)

In this example:

• [mysqld] is the section for the mysqld program.
• datadir specifies the data directory where MySQL stores its data.
• port defines the port on which MySQL listens for connections.

The syntax for specifying parameters in the configuration is similar to command-line syntax. However, you omit the leading two dashes (--option_name) from the option name and specify only one option per line.

For example, --port=3306 on the command line should be specified as port=3306 on a separate line in the configuration file.

The configuration file may contain the !include directives to include other configuration files or !includedir directives to search specific directories for configuration files.

For example, the following shows a MySQL configuration file on Ubuntu:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

Note that MySQL programs do not guarantee the order in which they read the configuration files.

To add a note to the configuration file, you start the note with the # sign followed by the comments. When reading the configuration file, MySQL programs ignore the comments.

For example, the following adds the comment to each parameter in the configuration file to make it more clear:

[mysqld]
# The directory where MySQL stores its data files.
datadir=/var/lib/mysql

# The port on which the MySQL server listens for incoming connections.
port=3306Code language: PHP (php)

Also, you can use the # to remove an option like this:

[mysqld]
# The directory where MySQL stores its data files.
# datadir=/var/lib/mysql

# The port on which the MySQL server listens for incoming connections.
port=3306Code language: PHP (php)

In this example, we remove the datadir option by making the line a comment.

Modifying MySQL configuration file

It’s a good practice to back up the configuration file to avoid data loss if you make accidental changes. Additionally, you should maintain clear documentation for any changes that you make to the configuration file.

To edit the configuration file, you can use a text editor like nano or vim with the following command:

sudo nano /etc/mysql/my.cnf

or

sudo vi /etc/mysql/my.cnf

On Windows, you can use a plain text editor like Notepad.

Before you apply the changes to production, you should always check the syntax of the configuration file and test it in the test server.

To check the syntax of the configuration file, you use the following command:

mysqld --validate-config

If you don’t see any output, it means that the configuration files are valid.

After saving the changes, restart the MySQL service to apply them to the server.

Summary

• MySQL programs manage their options via command line and configuration files.
• Use MySQL configuration files to avoid entering command line options every time you execute a program.