MySQL Program Option Files

MySQL Tutorials - Herong's Tutorial Examples

∟MySQL Program Option Files

This section provides a tutorial example on how find the default option files for MySQL programs. The default option file for MySQL programs is /etc/my.cnf for Linux and macOS computers

What Is a MySQL Program Option File? MySQL Program Option File, also called Configuration File, contains a set of options that can be loaded when starting MySQL programs.

There are two main advantages on using option files:

1. To save time when invoking MySQL programs, because options are loaded from tiles instead entered on command lines.
2. To ensure the same option is used among different MySQL programs.

Here is what I did to learn how to use MySQL program option files.

1. Find out the option files used by the running MySQL server program on a CentOS Linux computer. I see no option file specified at the command line for the running server.

herong$ sudo ps -ef | grep mysqld
mysql       1343      02:16:50 /usr/libexec/mysqld --basedir=/usr

2. Find out if the server program is loading any default option files. I see that it is reading the option file from /etc/my.cnf.

herong$ /usr/libexec/mysqld --help --verbose | more
  mysqld  Ver 8.0.21 for Linux on x86_64 (Source distribution)

  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: mysqld server mysqld-8.0
  The following options may be given as the first argument:
  --print-defaults        Print the program argument list and exit.
  --no-defaults           Don't read default options from any option file,
                          except for login file.
  --defaults-file=#       Only read default options from the given file #.
  --defaults-extra-file=# Read this file after the global files are read.
...

herong$ sudo ls -l /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
  ls: cannot access '/etc/mysql/my.cnf': No such file or directory
  ls: cannot access '/home/herong/.my.cnf': No such file or directory
  -rw-r--r--. 1 root root 202 Dec 18  2020 /etc/my.cnf

3. Review the default option file. I see that it reads more option files from the /etc/my.cnf.d directory.

herong$ sudo more /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

4. List option files included by /etc/my.cnf. I see 3 option files storing options separately for different programs.

herong$ sudo ls -l  /etc/my.cnf.d
  -rw-r--r--. 1 root root client.cnf
  -rw-r--r--. 1 root root mysql-default-authentication-plugin.cnf
  -rw-r--r--. 1 root root mysql-server.cnf

5. Review the default option file, /etc/my.cnf.d/mysql-server.cnf.

herong$ sudo more /etc/my.cnf.d/mysql-server.cnf
  # This group are read by MySQL server.
  # Use it for options that only the server (but not clients) should see

  [mysqld]
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  log-error=/var/log/mysql/mysqld.log
  pid-file=/run/mysqld/mysqld.pid

6. Add more options in /etc/my.cnf.d/mysql-server.cnf.

herong$ sudo vi /etc/my.cnf.d/mysql-server.cnf

[mysqld]
...
skip-log-bin

7. Restart the server with the updated option file.

herong$ sudo systemctl restart mysqld

8. Review the default option file of the MySQL server program on a macOS computer. The output shows that the there is only one default option file, /etc/my.cnf, on macOS computer.

herong$ sudo ps -ef | grep mysqld
   74 94827     1   0  9:35PM 0:01.73 /usr/local/mysql/bin/mysqld \
   --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data \
   --plugin-dir=/usr/local/mysql/lib/plugin --user=_mysql \
   --log-error=/usr/local/mysql/herong/mysqld.local.err \
   --pid-file=/usr/local/mysql/herong/mysqld.local.pid \
   --keyring-file-data=/usr/local/mysql/keyring/keyring \
   --early-plugin-load=keyring_file=keyring_file.so \
   --default_authentication_plugin=mysql_native_password

herong$ sudo /usr/local/mysql/bin/mysqld --help --verbose
  mysqld  Ver 8.0.17 for macos10.14 on x86_64
  Default options are read from the following files in the given order:
  /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

herong$ sudo ls -l /etc/my.cnf /etc/mysql/my.cnf \
  /usr/local/mysql/etc/my.cnf ~/.my.cnf

  ls: /Users/target/.my.cnf: No such file or directory
  ls: /etc/mysql/my.cnf: No such file or directory
  ls: /usr/local/mysql/etc/my.cnf: No such file or directory
  -rw-r--r--  1 root  wheel  199 Oct 18  2019 /etc/my.cnf

herong$ sudo more /etc/my.cnf
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
character-set-server = utf8
default_authentication_plugin = mysql_native_password