今日は、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 --------------
サーバーシステム変数
MySQLサーバーは、その操作を構成する多くのシステム変数を維持します。各システム変数にはデフォルト値があります。システム変数は、サーバーの起動時にコマンドラインまたはオプションファイルのオプションを使用して設定できます。それらのほとんどは、
SETステートメントを使用して実行時に動的 に変更できます。これにより、サーバーを停止して再起動しなくても、サーバーの操作を変更できます。式でシステム変数値を使用することもできます。
GLOBAL
グローバルシステム変数に値を割り当てるには、変数名の前に
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
デフォルトは、オンになっていて勝手にトランザクションがコミットされるので、それだとトランザクションの意味がないのでオフにしてます。
コマンドライン形式 `--autocommit[={OFF ON}]` システム変数 autocommit範囲 グローバル、セッション 動的 はい SET_VARヒントが適用されます番号 タイプ ブール値 デフォルト値 ON自動コミットモード。1に設定すると、テーブルへのすべての変更がすぐに有効になります。0に設定されている場合
COMMIT、トランザクションを受け入れるかROLLBACKキャンセルするためにを使用する必要があります 。場合はautocommit0であり、あなたは1に変更し、MySQLは自動実行しCOMMIT任意の開いているトランザクションの。トランザクションを開始する別の方法は、START TRANSACTIONorBEGINステートメントを使用する ことです。セクション13.3.1「STARTTRANSACTION、COMMIT、およびROLLBACKステートメント」を参照してください。デフォルトでは、クライアント接続は
autocommit1に設定された状態で始まります。クライアントをデフォルトの0でautocommit開始させるには、--autocommit=0オプションを指定してサーバーを起動することにより、グローバル値を設定 し ます。オプションファイルを使用して変数を設定するには、次の行を含めます。
ini [mysqld] autocommit=0
general_log
実行したSQLのログとか確認したいときとかあるので、オンにするように設定します。
コマンドライン形式 `--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=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/
〆
他にも、照合順序とか文字コードとかの変数を設定したかったのですが、時間が足りない上に沼にはまりそうだったので今後の課題として基本的な設定は完成ということで。
ほかにも設定したほうがいい変数などを知りたいですね。
