今日はなにの日。

気になったこと勉強になったことのメモ。

今日は、MySQLのサーバシステム変数の設定記録の日。

目次

とある日

MySQLを新規で構築するたびに、サーバーシステム変数をいちいち変えるのがめんどくさいのでまとめた記事を書きたい。

あと、知らないサーバシステム変数とか調べつつまとめます。

環境

Docker Desktop

 Cloud integration: 1.0.12
 Version:           20.10.5
 API version:       1.41
 Go version:        go1.13.15
 Git commit:        55c4c88
 Built:             Tue Mar  2 20:14:53 2021
 OS/Arch:           windows/amd64
 Context:           default
 Experimental:      true

Server: Docker Engine - Community
 Engine:
  Version:          20.10.5
  API version:      1.41 (minimum version 1.12)
  Go version:       go1.13.15
  Git commit:       363e9a8
  Built:            Tue Mar  2 20:15:47 2021
  OS/Arch:          linux/amd64
  Experimental:     false
 containerd:
  Version:          1.4.4
  GitCommit:        05f951a3781f4f2c1911b05e61c160e9c30eaa8e
 runc:
  Version:          1.0.0-rc93
  GitCommit:        12644e614e25b05da6fd08a38ffa0cfe1903fdec
 docker-init:
  Version:          0.19.0
  GitCommit:        de40ad0

ゲストOS

root@6438500bc2d7:/# cat /etc/os-release
PRETTY_NAME="Debian GNU/Linux 10 (buster)"
NAME="Debian GNU/Linux"
VERSION_ID="10"
VERSION="10 (buster)"
VERSION_CODENAME=buster
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"

MySQL

mysql> status
--------------
mysql  Ver 8.0.24 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          9
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.24 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld.sock
Binary data as:         Hexadecimal
Uptime:                 30 min 8 sec

Threads: 2  Questions: 7  Slow queries: 0  Opens: 117  Flush tables: 3  Open tables: 36  Queries per second avg: 0.003
--------------

サーバーシステム変数

5.1.8 サーバーシステム変数

MySQLサーバーは、その操作を構成する多くのシステム変数を維持します。各システム変数にはデフォルト値があります。システム変数は、サーバーの起動時にコマンドラインまたはオプションファイルのオプションを使用して設定できます。それらのほとんどは、SET ステートメントを使用して実行時に動的 に変更できます。これにより、サーバーを停止して再起動しなくても、サーバーの操作を変更できます。式でシステム変数値を使用することもできます。

GLOBAL

13.7.6.1 変数代入の SET 構文

グローバルシステム変数に値を割り当てるには、変数名の前に GLOBAL キーワードまたは@@GLOBAL. 修飾子を付けます:

sql SET GLOBAL max_connections = 1000; SET @@GLOBAL.max_connections = 1000;

SESSION

セッションシステム変数に値を割り当てるには、変数名の前に SESSION または LOCAL キーワード、@@SESSION.@@LOCAL. または@@修飾子、あるいはキーワードなしまたは修飾子なしを付けます:

sql SET SESSION sql_mode = 'TRADITIONAL'; SET LOCAL sql_mode = 'TRADITIONAL'; SET @@SESSION.sql_mode = 'TRADITIONAL'; SET @@LOCAL.sql_mode = 'TRADITIONAL'; SET @@sql_mode = 'TRADITIONAL'; SET sql_mode = 'TRADITIONAL';

確認方法

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               151 |
+-------------------+
1 row in set (0.00 sec)

変更方法

mysql> SET GLOBAL max_connections = 1000;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|              1000 |
+-------------------+
1 row in set (0.00 sec)

設定値

3つほど設定します。

autocomit

デフォルトは、オンになっていて勝手にトランザクションがコミットされるので、それだとトランザクションの意味がないのでオフにしてます。

general_log

実行したSQLのログとか確認したいときとかあるので、オンにするように設定します。

  • general_log

    コマンドライン形式 `--general-log[={OFF ON}]`
    システム変数 general_log
    範囲 グローバル
    動的 はい
    SET_VAR ヒントが適用されます 番号
    タイプ ブール値
    デフォルト値 OFF

    一般的なクエリログが有効になっているかどうか。値はOFF、ログを無効にする場合は0(または)、ログを有効にする場合は1(または ON)にすることができます。ログ出力の宛先は、log_outputシステム変数によって制御され ます。その値がのNONE場合、ログが有効になっていてもログエントリは書き込まれません。

general_log_file

general_logをオン設定したらgeneral_log_fileを設定しておくとどこにファイルが生成されているか忘れない。

  • general_log_file

    コマンドライン形式 --general-log-file=file_name
    システム変数 general_log_file
    範囲 グローバル
    動的 はい
    SET_VAR ヒントが適用されます 番号
    タイプ ファイル名
    デフォルト値 host_name.log

    一般的なクエリログファイルの名前。デフォルト値は *host_name*.logですが、初期値は--general_log_fileオプションで変更でき ます。

my.cnf

場所

root@6438500bc2d7:/# mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

設定値

[mysqld]
autocommit=0
general_log=1
general_log_file=/var/log/mysql/general_query.log

準備

root@6438500bc2d7:/# mkdir /var/log/mysql
root@6438500bc2d7:/# chown -L mysql:mysql /var/log/mysql/
root@6438500bc2d7:/# ls -l /var/log/
total 364
-rw-r--r-- 1 root  root    2486 Apr 19 18:56 alternatives.log
drwxr-xr-x 1 root  root    4096 Apr 19 18:56 apt
-rw-rw---- 1 root  utmp       0 Apr  8 00:00 btmp
-rw-r--r-- 1 root  root   29763 Apr 19 18:56 dpkg.log
-rw-r--r-- 1 root  root   32000 Apr 10 07:21 faillog
-rw-rw-r-- 1 root  utmp  292000 Apr 10 07:21 lastlog
drwxr-xr-x 2 mysql mysql   4096 May 23 14:45 mysql
-rw-rw-r-- 1 root  utmp       0 Apr  8 00:00 wtmp
root@6438500bc2d7:/# apt-get update
Get:1 http://security.debian.org/debian-security buster/updates InRelease [65.4 kB]
Get:2 http://deb.debian.org/debian buster InRelease [121 kB]
Get:3 http://deb.debian.org/debian buster-updates InRelease [51.9 kB]
Get:4 http://security.debian.org/debian-security buster/updates/main amd64 Packages [289 kB]
Get:5 http://deb.debian.org/debian buster/main amd64 Packages [7907 kB]
Get:6 http://repo.mysql.com/apt/debian buster InRelease [21.5 kB]
Get:7 http://deb.debian.org/debian buster-updates/main amd64 Packages [10.9 kB]
Get:8 http://repo.mysql.com/apt/debian buster/mysql-8.0 amd64 Packages [6017 B]
Fetched 8473 kB in 2s (5629 kB/s)
Reading package lists... Done
root@6438500bc2d7:/# apt-get install vim
root@6438500bc2d7:/# vim /etc/mysql/my.cnf

docker 再起動

サーバシステム変数確認

root@6438500bc2d7:/# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.24 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@log_output;
+--------------+
| @@log_output |
+--------------+
| FILE         |
+--------------+
1 row in set (0.00 sec)

mysql> select @@general_log_file ;
+----------------------------------+
| @@general_log_file               |
+----------------------------------+
| /var/log/mysql/general_query.log |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select @@general_log ;
+---------------+
| @@general_log |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

root@6438500bc2d7:/# cat /etc/mysql/my.cnf
# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
autocommit = 0
general_log=1
general_log_file=/var/log/mysql/general_query.log
# Custom config should go here
!includedir /etc/mysql/conf.d/

他にも、照合順序とか文字コードとかの変数を設定したかったのですが、時間が足りない上に沼にはまりそうだったので今後の課題として基本的な設定は完成ということで。

ほかにも設定したほうがいい変数などを知りたいですね。