MySQLの設定ファイルについて

はじめに

昨年、初めてISUCONに挑戦しました。
その際に、MySQLのチューニングをしたかったら、「どこに」「何を」書けばいい感じになるのか、全然わからなかったので、調べてみました。

環境

% cat /etc/issue
Ubuntu 22.04.3 LTS \n \l
% mysql --version
mysql  Ver 8.0.35-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

MySQLの設定ファイルの位置

MySQLの設定ファイルがどこにあるのかは、helpコマンドから確認することができます(環境によって異なります)。

% mysql --help | less my.cnf
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

今回の場合、主な設定は/etc/mysql/my.cnfに書かれていました。

% cat /etc/mysql/my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

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

このファイルはエントリーポイントの役割をしていて、!includedirを使い、それぞれ/etc/mysql/conf.d/と/etc/mysql/mysql.conf.d/を読み込むようになっています。
/etc/mysql/mysql.conf.d/、/etc/mysql/conf.d/を見ていきます。

/etc/mysql/mysql.conf.d/

/etc/mysql/mysql.conf.d/には、mysqld.cnfと、mysql.cnfがあり、それぞれ、mysqld(mysql-server)とmysql(mysql-client)の設定をしています。

mysqldの設定

• server側の設定は、[mysqld]でグルーピングして設定します。
• mysqldに関する設定を変更した場合は、mysqldをrestartする必要があります。
  • % service mysql restart
  • % systemctl restart mysql
  • ...etc

/etc/mysql/mysql.conf.d/mysqld.cnfのデフォルト設定

#
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld]
#
# * Basic Settings
#
user		= mysql
# pid-file	= /var/run/mysqld/mysqld.pid
# socket	= /var/run/mysqld/mysqld.sock
# port		= 3306
# datadir	= /var/lib/mysql


# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir		= /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 127.0.0.1
mysqlx-bind-address	= 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size		= 16M
# max_allowed_packet	= 64M
# thread_stack		= 256K

# thread_cache_size       = -1

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP

# max_connections        = 151

# table_open_cache       = 4000

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
#
# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file        = /var/log/mysql/query.log
# general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
# slow_query_log		= 1
# slow_query_log_file	= /var/log/mysql/mysql-slow.log
# long_query_time = 2
# log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
# server-id		= 1
# log_bin			= /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds	= 2592000
max_binlog_size   = 100M
# binlog_do_db		= include_database_name
# binlog_ignore_db	= include_database_name

mysqldのオプション一覧

全部は多すぎるので、デフォルトで設定されているオプションについてみてみます。

• user
  • どのuserでmysqldを実行するかを設定します。
  • default値はmysqlになっていますが、このuserはmysqlをインストールしたときに作成されます。
    • % cat /etc/passwd
  • mysqlのデータが格納されるディレクトリのownerと一致している必要があります。
    • userを変更したい場合はownerを変更する必要があります。
    • https://dev.mysql.com/doc/refman/8.0/en/changing-mysql-user.html
• bind-address
  • どのアドレスからの接続を待ち受けするのかを指定します。
  • 127.0.0.1の場合は、localhostからの接続しか待ち受けしませんが、、0.0.0.0などに設定すると外部からの接続を許可するようになります。
• mysqlx-bind-address
  • X Pluginが、どのアドレスからの接続を待ち受けするのかを指定します。
• key_buffer_size
  • MyISAMにおいて、indexのキャッシュ管理をするbufferのサイズを指定します。
• myisam-recover-options
  • MyISAMのストレージエンジンのリカバリモードを指定します。
  • https://dev.mysql.com/doc/refman/8.0/ja/server-system-variables.html#sysvar_myisam_recover_options
  • InnoDBを使っている場合はおそらく関係がないです。
• log_error
  • mysqlのエラーログが吐かれるファイルを指定します。
• max_binlog_size
  • バイナリログの最大サイズを指定します。
  • https://dev.mysql.com/doc/refman/8.0/ja/binary-log.html

mysqlの設定

• client側の設定は、[client]もしくは[mysql]でグルーピングして設定します。
• mysqlコマンドを叩くときに読み込まれます。
  • [client]グループ内にuser = userを書いてあった場合、% mysql --user=userが% mysqlだけですみます。

/etc/mysql/mysql.conf.d/mysql.cnfのデフォルト設定

#
# The MySQL database client configuration file
#
# Ref to https://dev.mysql.com/doc/refman/en/mysql-command-options.html

[mysql]

mysqlのオプション一覧

/etc/mysql/conf.d/

/etc/mysql/conf.d/には/mysql.cnfとmysqldump.cnfがあり、mysqlとmysqldumpの設定をすることができます。

mysqldump(バックアップ)の設定

• バックアップの設定は[mysqldump]でグルーピングして設定します。
• mysqldumpコマンドを実行する(バックアップを取る)ときに読み込まれるファイルになります。

/etc/mysql/conf.d/mysqldump.cnfのデフォルト設定

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysqldump]で設定できるオプション

全部は多すぎるので、デフォルトで設定されているオプションについてみます。

• quick
  • テーブルのデータを1行ずつdumpするようになります。
  • skip-quickを指定した場合は、一行ずつではなく、バッファリングするをしてdumpを実行します。
    • テーブルのサイズが大きい場合は、メモリに乗り切らなくなることがあるので注意が必要です。
• quote-names
  • 吐き出されるバックアップ用のSQLファイルにおいて、データベース名や、テーブル名、カラム名をバッククォートで囲むようになります。
  • 予約後をエスケープすることができます。
• max_allowed_packet
  • クライアントとサーバ間で転送されるパケットのサイズの最大値を設定します。