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:

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

Table of Contents

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 PHP Programs and MySQL Server

 Perl Programs and MySQL Servers

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 Table Column Types for Different Types of Values

 Using DDL to Create Tables and Indexes

 Using DML to Insert, Update and Delete Records

 Using SELECT to Query Database

 Window Functions for Statistical Analysis

 Use Index for Better Performance

 Transaction Management and Isolation Levels

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 System, User-Defined and Stored Procedure Variables

MySQL Server Administration

MySQL Program Option Files

 "SHOW" - Show Server Information

 "SHOW STATUS ..." - Server Status Variables

 Capture Slow Queries

 Analyze Slow Query Log File

 Solutions for Slow Queries

 Server Performance Troubleshooting

 Storage Engines in MySQL Server

 InnoDB Storage Engine - Primary and Secondary Indexes

 Performance Tuning and Optimization

 Bulk Changes on Large Tables

 MySQL Server on macOS

 Installing MySQL Server on Linux

 Connection, Performance and Second Instance on Linux

 Archived Tutorials

 References

 Full Version in PDF/EPUB