LoginSignup
26
29

More than 5 years have passed since last update.

@park-jh(devtopia)

CentOS 7.3でMySQL 5.7を設置、かなり勉強になりました。

Last updated at Posted at 2017-10-26

手順

ansibleのplaybookを作成する前にvagrantでplaybookの作成手順を試してみた。

  • mysqlを設置する。
  • mysqlを初期化する。
  • mysqlを起動する。
  • mysql_secure_installationと同等の処理を行う。
  • replicationの設定を行う。
  • slow query logを残す。

別にansibleを使わなくても上記の手順通りにすれば、master-slaveのレプリケーション構成でスロークエリを残してくれるmysqlサーバーが完成される。

mysqlを設置する。

CentOS 7.3ではmariadb-libsがデフォルトで設置されている。
yumでmysql-community-serverをインストールしたら、自動でmariadb-libsがmysql-community-libsに入れ替えれた。
わざとmariadb-libsを削除しなくても大丈夫。
まず、yumリポジトリを登録して、mysql-community-serverを設置する。

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

...snip...

========================================================================================================================
 Package                                アーキテクチャー  バージョン                 リポジトリー                  容量
========================================================================================================================
インストール中:
 mysql-community-libs                   x86_64            5.7.20-1.el7               mysql57-community            2.1 M
     mariadb-libs.x86_64 1:5.5.52-1.el7 を入れ替えます
 mysql-community-libs-compat            x86_64            5.7.20-1.el7               mysql57-community            2.0 M
     mariadb-libs.x86_64 1:5.5.52-1.el7 を入れ替えます
 mysql-community-server                 x86_64            5.7.20-1.el7               mysql57-community            164 M
依存性関連でのインストールをします:
 mysql-community-client                 x86_64            5.7.20-1.el7               mysql57-community             24 M
 mysql-community-common                 x86_64            5.7.20-1.el7               mysql57-community            272 k

...snip...

Running transaction
  インストール中          : mysql-community-common-5.7.20-1.el7.x86_64                                              1/6
  インストール中          : mysql-community-libs-5.7.20-1.el7.x86_64                                                2/6
  インストール中          : mysql-community-client-5.7.20-1.el7.x86_64                                              3/6
  インストール中          : mysql-community-server-5.7.20-1.el7.x86_64                                              4/6
  インストール中          : mysql-community-libs-compat-5.7.20-1.el7.x86_64                                         5/6
  削除中                  : 1:mariadb-libs-5.5.52-1.el7.x86_64                                                      6/6
  検証中                  : mysql-community-libs-5.7.20-1.el7.x86_64                                                1/6
  検証中                  : mysql-community-server-5.7.20-1.el7.x86_64                                              2/6
  検証中                  : mysql-community-common-5.7.20-1.el7.x86_64                                              3/6
  検証中                  : mysql-community-client-5.7.20-1.el7.x86_64                                              4/6
  検証中                  : mysql-community-libs-compat-5.7.20-1.el7.x86_64                                         5/6
  検証中                  : 1:mariadb-libs-5.5.52-1.el7.x86_64                                                      6/6

インストール:
  mysql-community-libs.x86_64 0:5.7.20-1.el7               mysql-community-libs-compat.x86_64 0:5.7.20-1.el7
  mysql-community-server.x86_64 0:5.7.20-1.el7

依存性関連をインストールしました:
  mysql-community-client.x86_64 0:5.7.20-1.el7               mysql-community-common.x86_64 0:5.7.20-1.el7

置換:
  mariadb-libs.x86_64 1:5.5.52-1.el7

完了しました!

設置したパッケージを確認して見よう。

$ yum list installed | grep mariadb
$ yum list installed | grep mysql
mysql-community-client.x86_64        5.7.20-1.el7                   @mysql57-community
mysql-community-common.x86_64        5.7.20-1.el7                   @mysql57-community
mysql-community-libs.x86_64          5.7.20-1.el7                   @mysql57-community
mysql-community-libs-compat.x86_64   5.7.20-1.el7                   @mysql57-community
mysql-community-server.x86_64        5.7.20-1.el7                   @mysql57-community
mysql57-community-release.noarch     el7-11                         installed

mysqlを初期化する。

mysqlを初期化する時はmysql_install_dbを使っていたが、MySQL 5.7.6からはdeprecatedされて代わりにmysqldを使うようになった。

$ sudo mysqld --initialize

MySQL 5.7.4からはセキュリティの向上のため、最初に臨時のrootパスワードが割り当てられる。
そのrootパスワードを変更しないと、mysqlコマンドやクライアントでの操作はできない。
また初期化したあとは、mysql_secure_installationコマンドを実行してMySQLサーバーをより安全な状態に作る必要がある。
しかし、mysql_secure_installationはインタラクティブで実行されるので、Ansibleなどで自動化したい場合は厄介だ。
そこで他の方法はないのかググってみたらこんな記事があった。

Puppet で MySQL 5.7.6 以降をセットアップするために mysqld --initialize-insecure を使う

ansibleがpuppetになっただけで、やりたい事は同じ。
要点だけ整理してみるとこうなる。

mysqlを初期化する時は、--initializeのほかにも--initialize-insecureも使える。
mysqld --initialize-insecureで初期化するとroot@localhostにパスワードを割り当てられない。

以前のように最初からパスワードなしの「mysql -u root」ができれば、設置を自動化するのは難しくない。
--initialize-insecureのオプションを付けてmysqlを初期化する。

$ sudo mysqld --initialize-insecure --user=mysql
$ sudo systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html

mysqldを起動する前なので、/var/log/mysqld.log（デフォルトのログファイル）は空いている。
これはansibleで最初１回だけ実行するタスクを作る時に使える。

mysqlを起動する。

mysqldを起動してみよう。

$ sudo systemctl start mysqld

$ sudo systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 月 2017-10-23 06:56:18 UTC; 1s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 4477 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 4454 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 4480 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─4480 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

10月 23 06:56:14 db1 systemd[1]: Starting MySQL Server...
10月 23 06:56:18 db1 systemd[1]: Started MySQL Server.

問題なし。
パスワードなしでrootで接続してみよう。

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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>

問題なし。
これでmysqlコマンドでクエリが実行できるようになった。

mysql_secure_installationと同等の処理を行う。

mysql_secure_installationで行う処理がどんなものなのか理解する必要がある。
「詳解MySQL 5.7」という本が役に立った。（良書なので、読んでみることを推奨する。）

MySQL 5.6以前のmysql_secure_installationがやっていたのは以下のようになる。

  • testデータベースの削除
  • 匿名ユーザーの削除
  • localhost、127.0.0.1、::1以外のホストからログインできるrootユーザーの削除

MySQL 5.6以降では以下のように変更された。

  • root@localhostのパスワードを変更する。
  • パスワードのバリデーションを行う。

MySQL 5.6以前のmysql_secure_installationで行った処理がMySQL 5.6以降ではすでにデフォルトで実装された。

実はこの本でmysql_secure_installationのセクションを読む前にググってみてansibleのplaybookを完成した。

Automating mysql_secure_installation

この記事で肝心のところだけを抜粋するとこうなる。

$ mysql --user=root <<_EOF_
UPDATE mysql.user SET Password=PASSWORD('パスワード') WHERE User='root';
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;
_EOF_

SQL文で見れば分かると思うが、上の内容と一致する。
最新のMySQLを設置するのであれば、クエリはパスワードを変更することだけになる。

  • MySQL 5.6まで
$ mysql --user=root <<_EOF_
UPDATE mysql.user SET Password=PASSWORD('パスワード') WHERE User='root';
_EOF_
  • MySQL 5.7以降
$ mysql --user=root <<_EOF_
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'パスワード';
_EOF_

勿論-eオプションを付けてもっと簡単にすることもできる。

  • MySQL 5.6まで
$ mysql -u root -e "UPDATE mysql.user SET Password=PASSWORD('パスワード') WHERE User='root';"
  • MySQL 5.7以降
$ mysql -u root -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'パスワード';"

注意：パスワードの強度によってバリデーションを通らない場合があるので、新しいパスワードを設定する時は先に確認する。

replicationの設定を行う。

レプリケーションの詳細についてはMySQLのドキュメントを参照しよう。

MySQL 5.6 リファレンスマニュアル

ここではGTIDを使ってレプリケーションを構築する。
masterとslave側に分けて設定を行う。

master

  • my.cnfに下記の内容を追加する。
# vi /etc/my.cnf
# BEGIN replication
server-id=1
gtid_mode=on
enforce-gtid-consistency
log-bin=mysql-bin
# END replication

server-idは各サーバーを識別するためのものだ。
レプリケーションの構成の場合は必須である。
gtid_mode=onはGTIDを使うということで、
enforce-gtid-consistencyはmaster上で実行されるSQL文がGTID互換であることを保証する。
log-binはバイナリログファイル名のプレフィックスでmysql-binの場合だと実際のログファイル名は「mysql-bin.000001」になる。

  • slave接続用のアカウントを作成する。
CREATE USER 'repl'@'192.168.33.%' IDENTIFIED BY パスワード;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.33.%';

mysqlはアカウントにホスト名を含めていて、'192.168.33.%'は192.168.33.で始まるホストでの接近だけを許可するという意味になる。
全ホストから接近を許可したい場合は'%'で、localhostだけ接近を許可する場合は'localhost'である。

slave

  • my.cnfに下記の内容を追加する。
# vi /etc/my.cnf
# BEGIN replication
server-id=2
gtid_mode=on
enforce-gtid-consistency
log-bin=mysql-bin
log_slave_updates
# END replication

masterの設定とほぼ同じで、異なるところは各サーバーを識別するためのserver-idとlog_slave_updatesだけである。
将来的にmasterになれるかもしれないslaveには、log-binとlog_slave_updatesを一緒に付ける必要がある。

  • レプリケーションの設定
CHANGE MASTER TO
  MASTER_HOST=192.168.33.32,
  MASTER_PORT=3306,
  MASTER_USER=repl,
  MASTER_PASSWORD=パスワード,
  MASTER_AUTO_POSITION=1;

ここではmasterが192.168.33.32でslaveが192.168.33.33だと仮定する。
MASTER_USERとMASTER_PASSWORDは先ほどmasterで作ったアカウントだ。
MASTER_AUTO_POSITIONはGTIDを使う場合、必要

slow query logを残す。

  • slow queryを残すためのログファイルを生成する。
# touch /var/log/mysqld-slow.log
# chown mysql:mysql /var/log/mysqld-slow.log

スロークエリログファイルは先に用意する必要がある。
指定したログファイルがない場合は、slow_query_logがONにならない。

  • my.cnfに下記の内容を追加する。
# vi /etc/my.cnf
# BEGIN slow query log
slow_query_log=1
slow_query_log_file=/var/log/mysqld-slow.log
long_query_time=1
log_queries_not_using_indexes
log_slow_admin_statements
# END slow query log

slow_query_log=1はスロークエリを残せという意味で、
sloq_query_log_fileはスロークエリのログファイルを指定するものだ。
long_query_time=1は1秒以上時間が掛かるクエリを残せという意味で、
log_queries_not_using_indexesはインデックスを使ってないもの、
log_slow_admin_statementsはDML以外のDDLやDCLのクエリも遅ければ残せというものだ。

  • mysqldを再起動し、適用されたのか確認する。
mysql> show variables like 'slow%';
+---------------------+--------------------------+
| Variable_name       | Value                    |
+---------------------+--------------------------+
| slow_launch_time    | 2                        |
| slow_query_log      | ON                       |
| slow_query_log_file | /var/log/mysqld-slow.log |
+---------------------+--------------------------+
3 rows in set (0.00 sec)

mysql> show variables like 'log%';
+----------------------------------------+--------------------------------+
| Variable_name                          | Value                          |
+----------------------------------------+--------------------------------+
| log_bin                                | ON                             |
| log_bin_basename                       | /var/lib/mysql/mysql-bin       |
| log_bin_index                          | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators        | OFF                            |
| log_bin_use_v1_row_events              | OFF                            |
| log_builtin_as_identified_by_password  | OFF                            |
| log_error                              | /var/log/mysqld.log            |
| log_error_verbosity                    | 3                              |
| log_output                             | FILE                           |
| log_queries_not_using_indexes          | ON                             |
| log_slave_updates                      | ON                             |
| log_slow_admin_statements              | ON                             |
| log_slow_slave_statements              | OFF                            |
| log_statements_unsafe_for_binlog       | ON                             |
| log_syslog                             | OFF                            |
| log_syslog_facility                    | daemon                         |
| log_syslog_include_pid                 | ON                             |
| log_syslog_tag                         |                                |
| log_throttle_queries_not_using_indexes | 0                              |
| log_timestamps                         | UTC                            |
| log_warnings                           | 2                              |
+----------------------------------------+--------------------------------+
21 rows in set (0.01 sec)

mysql> show variables like 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
  • slow queryを残すためのログファイル
# cat /var/log/mysqld-slow.log
/usr/sbin/mysqld, Version: 5.7.20-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument

補足

mysql_secure_installationのことだが、5.7.4からオプションを与えることでインタラクティブではない自動化ができるらしい。

MySQL-5.7 で mysql_secure_installation の自動化が楽になっていた

早速試してみたが、これもダメだった。
臨時発行されたrootパスワードではmysqladminでrootパスワードの変更ができない。
パスワードをdouble quotationで囲んでも同じ。

# 使い方
# -pの後ろはスペースなし
mysqladmin password 新パスワード -u root -p旧パスワード

$ cat /var/log/mysqld.log | grep 'temporary password'
2017-10-25T06:57:29.553548Z 1 [Note] A temporary password is generated for root@localhost: dN816IMi!B#9
$ mysqladmin password Testabc123! -u root -pdN816IMi!B#9
-bash: !B#9: event not found
$ mysqladmin password Testabc123! -u root -p"dN816IMi!B#9"
-bash: !B#9": event not found

特殊文字を含めたパスワード（特に!）をmysqladminがちゃんと処理できないぽい。
臨時発行されたrootパスワードでmysql_secure_installationを実行してみたが、これもダメだった。

$ sudo mysql_secure_installation -pdN816IMi!B#9 -D
-bash: !B#9: event not found

参考

26
29
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
26
29