はじめに
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
