MySQL 5.7 セットアップ手順と関連情報 #CentOS

MySQL Community Edition

文字コードなどの初期設定を含む MySQL Community Edition の MySQL Community Server 5.7.21 ¹ のセットアップとそれに関わる情報をまとめた。

なお、他の記事と比べて特徴的な情報として以下のような内容を記載している。

root アカウントは空パスワードでインストールできる。
mysql_secure_installation コマンドは実行しない。
MySQL クライアントのパスワードは暗号化して保存して省略できる。
文字コードは utf8 ではなく utfmb4 を指定する。
照合順序は utf8mb4_general ではなく utf8mb4_bin を指定する。
データベース作成時に文字コードだけを指定すると照合順序のサーバー設定は無視される。

MySQL Yum Repository のインストール

yum install https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

CentOS 7 (RHEL 7) は他の Linux ディストリビューションに倣って MySQL を MariaDB に置き換えた ² ³ ため、標準リポジトリから MySQL Community Server をインストールすることができない。そのため Yum でインストールするには公式リポジトリを別途導入する必要がある。 ⁴

上記コマンドを実行すると公式リポジトリの .repo ファイルが mysql57-community-release パッケージとしてインストールされる。

MySQL Community Server のインストール

yum install mysql-community-server

mysql-community-server パッケージをインストールする。mysql-server でもエイリアスが設定されている。

依存関係で mysql-community-libs mysql-community-libs-compat mysql-community-client mysql-community-common がインストールされる。

また、標準でインストールされている mariadb-libs パッケージが mysql-community-libs パッケージに置き換えられる。
mariadb-libs パッケージに含まれるファイルはパッケージごと削除されるので事前にパッケージを削除する必要はない。

文字コード

デフォルトの文字コードと照合順序の設定

/etc/my.cnf

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_bin

character-set-server

character-set-server のデフォルト値は latin1 ⁵ のため UTF-8 に変更する。
MySQL の utf8 は utf8mb3 を参照していて絵文字などの4バイト文字を扱えない ⁶ ので utf8mb4 を指定する。 ⁷ ⁸

collation-server

collation-server のデフォルト値は latin1_swedish_ci ⁹ のため UTF-8 に変更する。
utf8mb4 のデフォルトの照合順序は utf8mb4_general_ci になるが、寿司ビール問題 ¹⁰ などが生じるので utf8mb4_bin を指定する。

ただし、データベース作成時に CHARACTER SET だけを指定して COLLATE を指定しない場合、CHARACTER SET で指定された文字コードのデフォルト照合順序が利用される ¹¹ ため、collation-server で utf8mb4_bin を指定していても utf8mb4_general_ci が設定される ¹² ので注意。未指定か両方指定するかのいずれかの必要がある。

照合順序で生じる主な問題

問題	内容	例
大文字小文字問題	ローマ字の大文字と小文字の区別	「A」と「a」
全角半角問題	全角と半角の区別	「ハハ」と「ﾊﾊ」
ひらがなカタカナ問題	ひらがなとカタカナの区別	「はは」と「ハハ」
寿司ビール問題	絵文字などの4バイト文字の区別	「」と「」
ハハパパ問題	清音と濁音と半濁音の区別	「ハハ」と「パパ」と「ババ」
病院美容院問題	直音と拗促音の区別	「びょういん」と「びよういん」

日々の覚書: MySQL Bug #79977 "utf8mb4_unicode_520_ci don't make sense for Japanese FTS" で言いたいこと

照合順序の違い

照合順序 (Collation)	A ≠ a	≠	ハ ≠ パ	は ≠ ハ	よ≠ょ	全角 ≠ 半角	UCA
`utf8mb4_general_ci`	×	×	○	○	○	○
`utf8mb4_unicode_ci`	×	×	×	×	×	×	4.0.0
`utf8mb4_unicode_520_ci`	×	○	×	×	×	×	5.2.0
`utf8mb4_bin`	○	○	○	○	○	○
(`utf8mb4_ja_0900_as_cs`)							9.0.0

utf8mb4_ja_0900_as_cs は MySQL 8.0.1 から導入された。

[db tech showcase 2017 Tokyo] D31 - MySQL 8.0の日本語キャラクタ・セットと文字照合

MySQLの文字コードとCollation - Qiita

mysqlのcollateを使って大文字-小文字や全角-半角を無視した検索 - end0tknrのkipple - web写経開発

MySQL 5.5 の unicode collation で同一視される文字 - @tmtms のメモ

MySQL 5.5.11 unicode_ci で同一視される文字

文字コード設定の確認方法

SHOW GLOBAL VARIABLES LIKE 'character_set\_%';

GLOBAL を省略するとデフォルト値の LOCAL として指定される。
character-set-server に utf8mb4 が設定されていると、 character_set_filesystem は binary に、 character_set_system は utf8 になり、それ以外は utf8mb4 となる。

各データベースの文字コードの確認方法

SELECT * FROM information_schema.SCHEMATA;

MySQL 5.7.6 以前で必要な設定

/etc/my.cnf

[mysqld]
innodb_large_prefix = ON           # 5.7.6以前
innodb_file_format = Barracuda     # 5.7.6以前
innodb_file_format_max = Barracuda # 5.7.8以前

innodb_large_prefix

MySQL 5.7.6 以前は innodb_large_prefix のデフォルト値は OFF のため ON を指定する必要があった。またテーブル作成時に ROW_FORMAT=DYNAMIC を指定する必要があった。
MySQL 5.7.7 以降はデフォルト値が ON に変更されたので設定は不要。
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix
MySQL(InnoDB) で charset を utf8mb4 にする注意点の現在

innodb_file_format

MySQL 5.7.6 以前は innodb_file_format のデフォルト値は古い Antelope のため Barracuda を指定する必要があった。
MySQL 5.7.7 以降はデフォルト値が Barracuda に変更されたので設定は不要。
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_file_format

innodb_file_format_max

MySQL 5.7.8 以前は innodb_file_format_max のデフォルト値は古い Antelope のため Barracuda を指定する必要があった。
MySQL 5.7.9 以降はデフォルト値が Barracuda に変更されたので設定は不要。
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_file_format_max

ログ

スロークエリーログの設定

/etc/my.cnf

[mysqld]
log_output = TABLE,FILE
slow_query_log = ON
long_query_time = 1
min_examined_row_limit = 3
log_queries_not_using_indexes = ON
log_slow_admin_statements = ON

ln -s /var/lib/mysql/localhost-slow.log /var/log/mysqld-slow.log

または

touch /var/log/mysqld-slow.log
chown mysql. /var/log/mysqld-slow.log
chcon -t mysqld_log_t /var/log/mysqld-slow.log # または restorecon /var/log/mysqld-slow.log

log_output

ログエントリーの出力先を指定する。値には TABLE FILE NONE のいずれか1つ以上がカンマ区切りで指定できる。デフォルト値は FILE になる。¹³
TABLE を指定した場合、出力先となるテーブル名は mysql.general_log および mysql.slow_log となり、 CSV ストレージエンジンを使用する。¹⁴
パスは /var/lib/mysql/mysql/slow_log.CSV で直接参照できる。

slow_query_log

スロークエリーログが有効かどうかの指定。デフォルト値は OFF のため 1 か ON を指定する必要がある。¹⁵

slow_query_log_file

スロークエリーログが出力されるログファイルの名前。デフォルト値は host_name-slow.log になる。¹⁶
絶対パス名が指定されていない場合はデータディレクトリにファイルが作成される。¹⁷
実際のデフォルト値を参照すると /var/lib/mysql/localhost-slow.log となっている。

host_name

SELECT HOST FROM information_schema.PROCESSLIST;

mysqladmin processlist | awk 'NR==4 {print $6}'

host_name は PROCESSLIST で確認できる。

long_query_time

スロークエリと見なされる閾値。この設定値よりクエリに時間がかかる場合にサーバーは Slow_queries ステータス変数をインクリメントする。デフォルト値は 10 のため短くする必要がある。¹⁸
マイクロ秒の精度まで指定できる。実際のデフォルト値を参照すると 10.000000 となっている。

min_examined_row_limit

この数より少ない行を調べるクエリはスロークエリーログに記録されない。デフォルト値は 0 になる。¹⁹

log_queries_not_using_indexes

²⁰

log_slow_admin_statements

²¹

データディレクトリの手動初期化

mysqld --initialize-insecure --user=mysql

サービスの初回起動時にデータディレクトリの初期化は自動で実行されるが、root アカウントにランダムパスワードが設定されてしまう。
root アカウントに空パスワードを指定したい場合は、初回サービス起動前に mysqld コマンドに --initialize-insecure オプションを指定して実行することで対応できる。²²
--initialize-insecure オプションは MySQL 5.7.6 から使用できる。²³

サービスの起動と自動起動設定

systemctl start mysqld
systemctl enable mysqld

サービスの初回起動時にデータディレクトリの初期化が自動で実行される。
https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/#repo-qg-starting-server

タイムゾーン

タイムゾーンの確認

SELECT @@system_time_zone, @@global.time_zone, @@session.time_zone;

サーバー起動時にホストマシンのタイムゾーンで system_time_zone システム変数で設定しようとし、その後この値は変更しない。²⁴
通常、タイムゾーンは TZ 環境変数で指定される。²⁵

time_zone 変数は接続する各クライアントのタイムゾーンを初期化するために使用される。デフォルト値は SYSTEM で system_time_zone の値を使用することを意味する。 ²⁶

タイムゾーンテーブルのロード

SELECT COUNT(1) FROM mysql.time_zone;

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql

デフォルトでは mysql.time_zone テーブルは空でタイムゾーンがロードされておらず、CONVERT_TZ() 関数などが機能しない。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 4.4.6 mysql_tzinfo_to_sql — タイムゾーンテーブルのロード
 MySQLのタイムゾーン - @tmtms のメモ

パスワード

初期パスワード

grep 'temporary password' /var/log/mysqld.log | awk '{print $11}'

mysqld --initialize-insecure でデータディレクトリの初期化を行わなかった場合、 root パスワードが自動で生成され、エラーログに記録される。 ²⁷

パスワードに関する変遷

MySQL 5.7.4 から default_password_lifetime が新設され、デフォルト値が 360 になった。 ²⁸
MySQL 5.7.4 から root@localhost に自動生成されたパスワードが割り当てられるようになった。 ²⁹
MySQL 5.7.6 から自動生成されたパスワードの出力先が ~/.mysql_secret からエラーログ (/var/log/mysqld.log) に変更された。 ³⁰
MySQL 5.7.11 から default_password_lifetime のデフォルト値が 0 に変更された。 ³¹ ³²

validate_password プラグイン

SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE 'validate%';

INSTALL PLUGIN validate_password SONAME 'validate_password.so';

SHOW GLOBAL VARIABLES LIKE 'validate_password_%';

MySQL 5.7.8 から validate_password プラグインがデフォルトで有効になった ³³ が、mysqld --initialize-insecure でデータディレクトリの初期化を行うと有効化されないので注意。

validate_password

validate_password_policy

validate_password_length

root パスワードの変更

mysqladmin での変更

mysqladmin --user=root password 'P@ssw0rd'

SQL 文での変更

MySQL5.7.6以降

ALTER USER 'root'@'localhost' IDENTIFIED BY 'P@ssw0rd';

MySQL5.7.5以前

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('P@ssw0rd');

MySQL :: MySQL 5.7 Reference Manual :: B.5.3.2 How to Reset the Root Password :: B.5.3.2.2 Resetting the Root Password: Unix and Unix-Like Systems

mysql_config_editor

mysql_config_editor set --host=localhost --user=root --password

mysql_config_editor コマンドは MySQL 5.6.6 以降で利用可能。 ³⁴

.mylogin.cnf ファイルに暗号化されたパスワードを保存して mysql や mysqladmin などの MySQL クライアントのパスワード入力を省略することができる。

他の MySQL クライアントと異なり --password のあとにパスワード値を指定できない。 ³⁵

mysql_secure_installation

mysql_secure_installation --use-default --password=P@ssw0rd

mysql_secure_installation コマンドは以下の機能を有している ³⁶ が、このプログラムの機能はすでに Yum リポジトリのインストールによって実行されているので MySQL 5.7 のインストール後には実行するなとリファレンスに記載 ³⁷ されており、もはや root アカウントのパスワード変更以外に意味はない。

root アカウントのパスワード設定
ローカルホストの外部からアクセス可能な root アカウントの削除
匿名ユーザーアカウントの削除
test データベース（デフォルトでは匿名ユーザーを含むすべてのユーザーがアクセス可能）、および test_ で始まるデータベースに誰でもアクセスできる権限の削除

なお、 --password オプションは 5.7.2 以降、 --user-default オプションは 5.7.4 以降に実装されている。 ³⁸

MySQL クライアントの設定

~/.my.cnf

[mysql]
default-character-set = utf8mb4
pager = less -SFXin

オプションファイル ~/.my.cnf に MySQL クライアントのオプションをユーザー固有に設定できる。

オプションファイル

Unix および Unix ライクなシステムで読み込まれるオプションファイル ³⁹

ファイル名	説明
`/etc/my.cnf`	全体オプション
`/etc/mysql/my.cnf`	全体オプション
`SYSCONFDIR/my.cnf`	全体オプション
`$MYSQL_HOME/my.cnf`	サーバー固有オプション (サーバーのみ)
`defaults-extra-file`	`--defaults-extra-file` で指定されたファイル (存在する場合)
`~/.my.cnf`	ユーザー固有オプション
`~/.mylogin.cnf`	ユーザー固有ログインパスオプション (クライアントのみ)

オプショングループ

第33回　MySQLのオプションファイル my.cnfの豆知識［その2］：MySQL道普請便り｜gihyo.jp … 技術評論社

プログラム	オプショングループ
mysqlcheck	[mysqlcheck], [client]
mysqlpump	[mysql_dump], [client]
mysql	[mysql], [client]
mysql_secure_installation	[mysql_secure_installation], [mysql], [client]
mysqladmin	[mysqladmin], [client]
mysqlbinlog	[mysqlbinlog], [client]
mysqldump	[mysqldump], [client]
mysqlimport	[mysqlimport], [client]
mysqlshow	[mysqlshow], [client]
mysqlslap	[mysqlslap], [client]
mysqltest	[mysqltest], [client]

default-character-set

https://dev.mysql.com/doc/refman/5.7/en/charset-configuration.html
https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html#option_mysql_default-character-set

pager

less

オプション	説明
`-S`
`-F`
`-X`
`-i`
`-n`

http://man7.org/linux/man-pages/man1/less.1.html
https://qiita.com/nao58/items/f651d9f2d0f420f87a50

DB および DB ユーザーの作成

DB ユーザーの作成

CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'P@ssw0rd';

DB の作成

CREATE DATABASE `dbname` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

mysqladmin create dbname

DB への権限の割り当て

GRANT ALL PRIVILEGES ON dbname.* TO 'dbuser'@'localhost';

ユーティリティ

MySQLTuner

curl -L http://mysqltuner.pl -o /usr/local/bin/mysqltuner
chmod +x /usr/local/bin/mysqltuner

MySQL Utilities

yum install mysql-utilities

Percona Toolkit

yum install https://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-4.noarch.rpm
yum install percona-toolkit

innotop

yum install epel-release
yum install innotop

一括コマンド群まとめ

yum --assumeyes install https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm && \
yum --assumeyes install mysql-community-server && \
echo -e '\ncharacter-set-server=utf8mb4' >> /etc/my.cnf && \
echo -e 'collation-server=utf8mb4_bin' >> /etc/my.cnf && \
echo -e '\nslow_query_log=ON' >> /etc/my.cnf && \
echo -e 'log_output=TABLE,FILE' >> /etc/my.cnf && \
echo -e 'min_examined_row_limit=3' >> /etc/my.cnf && \
echo -e 'long_query_time=1' >> /etc/my.cnf && \
echo -e 'log_queries_not_using_indexes=ON' >> /etc/my.cnf && \
ln -s /var/lib/mysql/localhost-slow.log /var/log/mysqld-slow.log && \
echo -e '[mysql]' >> ~/.my.cnf && \
echo -e 'default-character-set=utf8mb4' >> ~/.my.cnf && \
echo -e 'pager=less -SFXin' >> ~/.my.cnf && \
mysqld --initialize-insecure --user=mysql && \
systemctl start mysqld && systemctl enable $_ && \
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql && \
mysqladmin create wordpress && \
mysql --execute="CREATE USER 'wordpress'@'localhost' IDENTIFIED BY 'P@ssw0rd';" && \
mysql --execute="GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpress'@'localhost';" && \
mysql --execute="ALTER USER 'root'@'localhost' IDENTIFIED BY 'P@ssw0rd';" && \
rm --force ~/.mysql_history && \
history -c

curl -L http://mysqltuner.pl -o /usr/local/bin/mysqltuner && chmod +x $_ && \
yum --assumeyes install mysql-utilities && \
yum --assumeyes install https://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-4.noarch.rpm && yum --assumeyes install percona-toolkit && \
yum --assumeyes install epel-release && yum --assumeyes install innotop

MySQL 5.7 セットアップ手順と関連情報

MySQL Community Edition

MySQL Yum Repository のインストール

MySQL Community Server のインストール

文字コード

デフォルトの文字コードと照合順序の設定

character-set-server

collation-server

照合順序で生じる主な問題

照合順序の違い

文字コード設定の確認方法

各データベースの文字コードの確認方法

MySQL 5.7.6 以前で必要な設定

innodb_large_prefix

innodb_file_format

innodb_file_format_max

ログ

スロークエリーログの設定

log_output

slow_query_log

slow_query_log_file

host_name

long_query_time

min_examined_row_limit

log_queries_not_using_indexes

log_slow_admin_statements

データディレクトリの手動初期化

サービスの起動と自動起動設定

タイムゾーン

タイムゾーンの確認

タイムゾーンテーブルのロード

パスワード

初期パスワード

パスワードに関する変遷

validate_password プラグイン

validate_password

validate_password_policy

validate_password_length

root パスワードの変更

mysqladmin での変更

SQL 文での変更

mysql_config_editor

mysql_secure_installation

MySQL クライアントの設定

オプションファイル

Unix および Unix ライクなシステムで読み込まれるオプションファイル 39

オプショングループ

default-character-set

pager

less

DB および DB ユーザーの作成

DB ユーザーの作成

DB の作成

DB への権限の割り当て

ユーティリティ

MySQLTuner

MySQL Utilities

Percona Toolkit

innotop

一括コマンド群まとめ

Unix および Unix ライクなシステムで読み込まれるオプションファイル ³⁹