1. Overview
In this tutorial, we’ll learn the different ways we can locate the my.cnf file from the shell in Linux.
2. The my.cnf File
The my.cnf file is the configuration file for MySQL databases. The file consists of parameters that configure the MySQL database server and the MySQL command-line clients.
Each of the parameters in the file takes up a single row. Then, the parameter either comes with just the key or in the form of key-value pair. Specifying the parameter with just the key has the same effect as if we’re passing the command-line argument –key. On the other hand, the key-value pair syntax is equivalent to –key=value on the command line.
Furthermore, the same configuration file can configure different related programs in the MySQL software suites. Specifically, using the my.cnf file, we can configure the programs like mysqld (the server binary) and mysql (the command-line client). To demarcates the parameters for different programs, we can use the square bracket syntax. For example, the parameters after the [mysqld**] line are only applicable to the mysqld program, until it reaches another square bracket directive.
Here’s how a typical my.cnf file looks like:
$ cat my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 128M
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
The my.cnf example above configures the mysqld with three parameters, namely innodb_buffer_pool_size, datadir, and socket. Within the file, we can write comments by starting a line with the hash character.
To configure the MySQL server or client, we’ll need to update the my.cnf file. However, locating the my.cnf file is not straightforward as the file can appear in multiple different locations. Let’s look at these different file paths in detail.
3. The Default my.cnf File Paths
Generally, there’re five locations that the MySQL instance will look at in order to locate the my.cnf files:
- /etc/my.cnf
- /etc/mysql/my.cnf
- SYSCONFDIR/my.cnf
- $MYSQL_HOME/my.cnf
- ~/.my.cnf
Where SYSCONFDIR is the value that’s specified during building time and it’s usually */*etc or /usr/etc. Additionally, the $MYSQL_HOME is a user-configurable value and has a default value of /var/lib/mysql.
Out of these five different locations, the options file inside the /etc/my.cnf, /etc/mysql/my.cnf and SYSCONFDIR/my.cnf is the global options file. In other words, the options file in these locations can be used to configure all the MySQL-related programs, such as mysqld, mysql, and mysqldump.
Then, the ~/.my.cnf is a user-specific configuration file that we can use to apply configurations that’s user-specific.
3.1. Getting the Default Locations List
We can get the locations the different binaries will look at when resolving the my.cnf file on their help page. For example, to get the list of locations where the mysqld will find and load the my.cnf file, we can run mysqld –help –verbose:
$ mysqld Ver 8.0.33 for Linux on x86_64 (MySQL Community Server - GPL)
...
Starts the MySQL database server.
Usage: mysqld [OPTIONS]
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf /opt/mysql/my.cnf ~/.my.cnf
The following groups are read: mysql_cluster mysqld server mysqld-8.0
...
In the help page, the mysqld command prints a line that tells us the list of directories from which it will read the options. In our example, the SYSCONFDIR is /usr/etc and the MYSQL_HOME is set to /opt/mysql. Note that if the environment variable MYSQL_HOME is not set, it will not show up in the list.
We can obtain the same thing from other MySQL binaries, such as mysql and mysqldump.
3.2. Configuration Conflict Resolution
When there’re multiple my.cnf files in all the locations, MySQL will resolve the option values such that the last appearing value of the same key will take effect.
For example, consider that the my.cnf file in /etc/my.cnf configures the parameter innodb_buffer_pool_size = 128M. Then, the same innodb_buffer_pool_size parameter appears in the /etc/mysql/my.cnf file again, with the value set to 256M. Since the my.cnf file in the /etc/mysql appears to be second to the /etc in the list, the final value for innodb_buffer_pool_size will be 256M.
4. Locating my.cnf File Through Script
Having known all the locations that MySQL will look at for the my.cnf files, it will be tedious for us to look at each directory one by one to find out the actual location of the my.cnf file. In this section, we’ll look at some tools or scripts we can use to effortlessly locate the my.cnf file.
4.1. Running ls on Each Possible Location
The idea is that we’ll first get a list of the possible location of the my.cnf file, and then we pipe each of the paths to the ls command using the xargs command-line tool. Specifically, here’s the oneliner script to achieve that:
$ mysql --help | grep /my.cnf | xargs ls
ls: cannot access '/etc/mysql/my.cnf': No such file or directory
ls: cannot access '/usr/etc/my.cnf': No such file or directory
ls: cannot access '/opt/mysql/my.cnf': No such file or directory
ls: cannot access '~/.my.cnf': No such file or directory
/etc/my.cnf
The script first prints the help page of the mysql CLI tool. Then, we select the line that contains the list of possible option file paths using the grep command. Finally, we run the ls command on each of the file paths using the xargs command.
From the output, we can see that the ls command reports that there’s no such file path for all the paths, except for the /etc/my.cnf. This shows that the my.cnf file that the MySQL instance is using is located at /etc/my.cnf.
4.2. The Inefficiency of the find Command
Typically, we’ll resort to the find command for locating files within the system. For our scenario, a common solution would be to run the find command on root, matching any files with the pattern my.cnf:
$ find / -name my.cnf
Although this command works, it’s not encouraged. This is because the find command will recursively go into each directory looking for a file that matches the pattern we specify using -name. The downside of this approach is that it will get slower the more files we have in the system.
In contrast, the approach we discussed in the previous subsections only looks at five different locations, which results in a much more efficient search operation.
5. Conclusion
In this tutorial, we’ve learned that the my.cnf is a configuration file for configuring the MySQL software suites binaries, such as mysqld and mysql. Then, we looked at the list of default locations from where the binaries will attempt to load the my.cnf file. In the event of conflicts, the last appearing value of the same key will take precedence.
Instead of manually checking each possible location for the existence of the my.cnf file, we show that we can efficiently look up the existence of the my.cnf file in all the possible paths using a combination of grep, xargs, and ls commands. Finally, we talked about how the find command can be very inefficient for locating the my.cnf file compared to the script.