MySQL 5.7.21 導入

スポンサードリンク

はじめに

WordPress を Apache 2.4 + MySQL 5.7 + PHP7 の環境で構築。今回はMySQLの内容になります。

 

対象バージョン

バージョン
MySQL 5.7.21

最新バージョンは公式HPで確認してください。(2018/04/23現在 MySQL8.0がリリースされたようですが…)

 

サーバ環境(ConoHa VPS)

バージョン 参照
CentOS 7 7.4.1708
OpenSSL 1.1.0g OpenSSL 1.1.0 導入
Apache 2.4.29 Apache 2.4.29 導入

 

必要なパッケージのインストール

# yum -y install wget gcc-c++ cmake ncurses-devel zlib-devel readline-devel bison

 

mysqlユーザ作成

# useradd -s /sbin/nologin -d /usr/local/mysql mysql

 

mysqlユーザにスイッチできないことを確認する

# su - mysql
This account is currently not available.

 

ダウンロード

BOOSTライブラリ入りを落とします。

# cd /usr/local/src/
# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.21.tar.gz

 

展開

# tar zxvf mysql-boost-5.7.21.tar.gz

 

 ビルド・インストール

ポイントは以下の3点

  • BOOSTオプションを付ける
  • SSLを有効にする
  • 文字コードをutf8mb4にする
# cd /usr/local/src/mysql-5.7.21/

# cmake . \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.7.21 \
-DMYSQL_UNIX_ADDR=/usr/local/mysql-5.7.21/mysql.sock \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_DATADIR=/usr/local/mysql-5.7.21/data \
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_SSL=yes \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DWITH_BOOST=./boost/

# make

# make install

 

オーナー変更

# chown -R mysql:mysql /usr/local/mysql-5.7.21

 

パーミッション変更

# chmod 755 /usr/local/mysql-5.7.21

 

設定ファイルの作成

注意した点は以下の内容。my.cnfのパラメータは参考までに。

  • OSデフォルトでmariadbの設定ファイルがあるため退避する。
# mv /etc/my.cnf{,.org}
  • パスワードの有効期限パラメータは5.7.11以降不要
  • innodbの設定をあとでやるとエラー・トラブルが多いため、先に入れておいたほうが良い。
# vi /etc/my.cnf

[mysqld]
# base
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/mysql.sock
user = mysql
port = 3306
character-set-server = utf8mb4
default_password_lifetime = 0
skip-character-set-client-handshake
default-storage-engine = innoDB
log-timestamps = system
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 1
explicit_defaults_for_timestamp = true
secure_file_priv = /usr/local/mysql/files

# SSL
ssl-ca = ca.pem
ssl-cert = server-cert.pem
ssl-key = server-key.pem

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links = 0

# sql_mode(defualt)
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# Strage Engine
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:1G
innodb_log_group_home_dir = /usr/local/mysql/data
innodb_log_file_size = 48M
innodb_thread_concurrency = 0
innodb_file_per_table = 1
innodb_buffer_pool_size = 128M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_support_xa=1
innodb_lock_wait_timeout = 50

# Replication
#server-id =
#log-bin = /usr/local/mysql/blog/mysql-bin
#sync_binlog = 1
#expire_logs_days = 2

# Slave DB
#server-id =
#relay-log = /usr/local/mysql/relay/relay-bin
#slave_net_timeout = 120
#log-slave-updates = 1

# Tuning
#skip-name-resolve
key_buffer_size = 256M
max_allowed_packet = 4M
table_open_cache = 2000
table_open_cache_instances = 1
max_connections = 151
sort_buffer_size = 2M
read_buffer_size = 256K
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 8M

[mysqld_safe]
log-error=/var/log/mysql/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid

[client]
loose-default-character-set = utf8mb4

 

シンボリックリンク作成

# cd /usr/local/

# ln -s /usr/local/mysql-5.7.21 mysql

# ls -l
total 8
drwxr-xr-x. 2 root root 78 Jan 18 11:26 bin
drwxr-xr-x. 2 root root 6 Nov 6 2016 etc
drwxr-xr-x. 2 root root 6 Nov 6 2016 games
lrwxrwxrwx 1 root root 23 Jan 23 09:27 httpd -> /usr/local/httpd-2.4.29
drwxr-xr-x 15 root root 175 Jan 18 17:07 httpd-2.4.29
drwxr-xr-x. 4 root root 35 Jan 18 11:26 include
drwxr-xr-x. 3 root root 4096 Jan 18 11:26 lib
drwxr-xr-x. 2 root root 6 Nov 6 2016 lib64
drwxr-xr-x. 2 root root 6 Nov 6 2016 libexec
lrwxrwxrwx 1 root root 23 Jan 24 14:26 mysql -> /usr/local/mysql-5.7.21
drwxr-xr-x 10 mysql mysql 186 Jan 24 13:53 mysql-5.7.21
lrwxrwxrwx 1 root root 25 Jan 17 09:23 openssl -> /usr/local/openssl-1.1.0g
drwxr-xr-x 7 root root 67 Jan 17 09:20 openssl-1.1.0g
drwxr-xr-x. 2 root root 6 Nov 6 2016 sbin
drwxr-xr-x. 7 root root 75 Jan 18 11:15 share
drwxr-xr-x. 8 root root 4096 Jan 24 12:00 src

 

パスを通す

# vi /etc/profile

export PATH=/usr/local/mysql/bin:$PATH

# (即時反映)
. /etc/profile

 

ログ用にディレクトリ作成

# mkdir /var/log/mysql
# chown :mysql /var/log/mysql
# chmod 775 /var/log/mysql

 

バイナリログのディレクトリ作成(マスターの場合)

# mkdir /usr/local/mysql/blog
# chown mysql:mysql /usr/local/mysql/blog

 

リレーログのディレクトリ作成(スレーブの場合)

# mkdir /usr/local/mysql/relay
# chown mysql:mysql /usr/local/mysql/relay

 

secure-file-privのwarning対策

# mkdir /usr/local/mysql/files
# chmod 750 /usr/local/mysql/files
# chown mysql:mysql /usr/local/mysql/files

 

MySQLの初期化

–initialize(rootの初期パスワードあり)で実行した場合、初期化実行直後に流れるログに初期パスワードが流れるので確認しておきます。
mysql_secure_installation(MySQLのセキュア設定)の実行で始めに聞かれます。今回は–initialize-insecure(rootの初期パスワードなし)で初期化します。

# /usr/local/mysql/bin/mysqld \
--user=mysql \
--basedir=/usr/local/mysql/ \
--datadir=/usr/local/mysql/data/ \
--log-error-verbosity=3 \
--initialize-insecure

 

起動スクリプトのコピー

systemdに対応したUnitファイルは用意されてなそうですが、従来通り/etc/init.d配下に起動スクリプトを配置すればsystemctlコマンドで制御できるようになります。

# cp -p /usr/local/src/mysql-5.7.21/support-files/mysql.server /etc/init.d/mysqld
# chown root:root /etc/init.d/mysqld
# chmod 755 /etc/init.d/mysqld

 

起動スクリプトの修正(追加)

# vi /etc/init.d/mysqld

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

mysqld_pid_file_path=/usr/local/mysql/data/mysqld.pid

 

Systemdへの登録

# systemctl daemon-reload

 

自動起動の設定

unit登録していないので、従来のchkconfigで登録されます。

# systemctl enable mysqld.service
mysqld.service is not a native service, redirecting to /sbin/chkconfig.
Executing /sbin/chkconfig mysqld on

# systemctl is-enabled mysqld.service
mysqld.service is not a native service, redirecting to /sbin/chkconfig.
Executing /sbin/chkconfig mysqld --level=5
enabled

 

systemctlコマンドで認識できるか確認する

systemctl status mysqld
● mysql.service - LSB: start and stop MySQL
 Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
 Active: inactive (dead)
 Docs: man:systemd-sysv-generator(8)

 

MySQL起動

# systemctl start mysqld

 

MySQL状態確認

# systemctl status mysqld.service
● mysqld.service - LSB: start and stop MySQL
 Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
 Active: active (running) since Thu 2018-01-25 10:37:58 JST; 1s ago
 Docs: man:systemd-sysv-generator(8)
 Process: 1940 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
 CGroup: /system.slice/mysqld.service
 ├─1945 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/*****.pid
 └─2056 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=*****.err --pid-file=/usr/local/mysql/data/*****.pid

Jan 25 10:37:57 ***** systemd[1]: Starting LSB: start and stop MySQL...
Jan 25 10:37:58 ***** mysqld[1940]: Starting MySQL. SUCCESS!
Jan 25 10:37:58 ***** systemd[1]: Started LSB: start and stop MySQL.

 

プロセス確認

# ps aux | grep mysqld | grep -v grep
root 1945 0.0 0.1 115384 1724 ? S 10:37 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/*****.pid
mysql 2056 0.2 17.3 1602760 176576 ? Sl 10:37 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=*****.err --pid-file=/usr/local/mysql/data/*****.pid

 

MySQLのセキュア設定

# /usr/local/mysql/bin/mysql_secure_installation

mysql_secure_installation: [Warning] unknown variable 'loose-default-character-set=utf8mb4'

Securing the MySQL server deployment.

Connecting to MySQL using a blank password. <= 今回は初期パスワードなしなので、パスワードなしで接続しています。

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y <=ポリシーに沿ったパスワードを作成するかどうか

There are three levels of password validation policy:

LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2 <= 今回は2(LOW)で設定
Please set the password for root here.

New password:<= ポリシーに沿ったパスワード入力

Re-enter new password:<= 再度パスワード入力

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y <= 匿名ユーザ削除
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y <= rootのリモートログイン禁止
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y <= テストデータベース削除
 - Dropping test database...
Success.

- Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y <= 権限テーブルのリロード
Success.

All done!

 

MySQL接続確認

# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21 Source distribution

Copyright (c) 2000, 2018, 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> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

 

 文字コード確認

character_set_systemはutf8で固定になります。

mysql> show variables like '%character%';
+--------------------------+-----------------------------------------+
| Variable_name            | Value                                   |
+--------------------------+-----------------------------------------+
| character_set_client     | utf8mb4                                 |
| character_set_connection | utf8mb4                                 |
| character_set_database   | utf8mb4                                 |
| character_set_filesystem | binary                                  |
| character_set_results    | utf8mb4                                 |
| character_set_server     | utf8mb4                                 |
| character_set_system     | utf8                                    |
| character_sets_dir       | /usr/local/mysql-5.7.21/share/charsets/ |
+--------------------------+-----------------------------------------+
8 rows in set (0.01 sec)

 

SSL設定前の状態

mysql> show variables like '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | DISABLED        |
| have_ssl      | DISABLED        |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
9 rows in set (0.00 sec)

ここまでの作業でできている関連鍵。

# ls -l /usr/local/mysql/data/ | grep ".pem"
-rw------- 1 mysql mysql 1680 Jan 27 13:56 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Jan 27 13:56 public_key.pem

 

SSLキー発行

# mysql_ssl_rsa_setup
Generating a 2048 bit RSA private key
..............................+++
....+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
.........................................................................+++
.................................................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
..............+++
........................................+++
writing new private key to 'client-key.pem'
-----

出来た鍵。

# ls -l /usr/local/mysql/data/ | grep ".pem"
-rw------- 1 root root 1679 Jan 27 15:01 ca-key.pem
-rw-r--r-- 1 root root 1107 Jan 27 15:01 ca.pem
-rw-r--r-- 1 root root 1107 Jan 27 15:01 client-cert.pem
-rw------- 1 root root 1675 Jan 27 15:01 client-key.pem
-rw------- 1 mysql mysql 1680 Jan 27 13:56 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Jan 27 13:56 public_key.pem
-rw-r--r-- 1 root root 1107 Jan 27 15:01 server-cert.pem
-rw------- 1 root root 1679 Jan 27 15:01 server-key.pem

 

オーナ・グループ変更

# chown mysql:mysql *.pem

# ls -l /usr/local/mysql/data/ | grep ".pem"
-rw------- 1 mysql mysql 1679 Jan 27 15:01 ca-key.pem
-rw-r--r-- 1 mysql mysql 1107 Jan 27 15:01 ca.pem
-rw-r--r-- 1 mysql mysql 1107 Jan 27 15:01 client-cert.pem
-rw------- 1 mysql mysql 1675 Jan 27 15:01 client-key.pem
-rw------- 1 mysql mysql 1680 Jan 27 13:56 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Jan 27 13:56 public_key.pem
-rw-r--r-- 1 mysql mysql 1107 Jan 27 15:01 server-cert.pem
-rw------- 1 mysql mysql 1679 Jan 27 15:01 server-key.pem

 

my.cnf の設定を確認

# grep ssl /etc/my.cnf
ssl-ca = ca.pem
ssl-cert = server-cert.pem
ssl-key = server-key.pem

 

MySQL再起動

# systemctl restart mysqld.service

 

SSL設定後の状態

mysql> show variables like '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
9 rows in set (0.00 sec)

 

最後に

説明はかなり割愛しましたが、手順は良い感じになったと思います。詳細は以下のリンク先を参考までに。

 

参考

https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html
https://teratail.com/questions/27783
https://yoku0825.blogspot.jp/2017/11/mysqlbinlog-error-unknown-variable.html
http://gihyo.jp/dev/serial/01/mysql-road-construction-news/0033
https://www.slideshare.net/tmtm/mysql-62004569
http://d.hatena.ne.jp/tacohachi/20100128/p1
https://dev.mysql.com/doc/refman/5.7/en/using-encrypted-connections.html
https://dev.mysql.com/doc/refman/5.7/en/creating-ssl-rsa-files-using-mysql.html
https://dev.mysql.com/doc/refman/5.7/en/mysql-ssl-rsa-setup.html

 

スポンサードリンク