MySQL 8.0 のインストールと設定の覚書（自分用）
－ 2019/2/5 －
新しい環境にインストールしたらいろいろ変わってたので追記
(fedora29)
インストール
sudo dnf install community-mysql-server
・・・
community-mysql-server x86_64 8.0.13-1.fc29 updates 24 M
・・・
8.0.13 が入りました
サービス起動
sudo systemctl enable mysqld
sudo systemctl start mysqld
セキュア設定
sudo mysqld --initialize-insecure
sudo mysql_secure_installation
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT 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 component?
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: 0
Please set the password for root here.
New password:
Re-enter new password:
Estimated strength of the password: 50
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
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!
VALIDATE PASSWORD はプラグインがなくなって、コンポーネントのみになってました
わざわざログファイルを確認して初期パスワードを調べなくても設定できるようになってる
これで楽になりましたね
（本来はこうだよね）
設定
文字コードとか認証プラグインとか
/etc/my.cnf.d/community-mysql-server.cnf
・・・
# 文字コード設定/照合順序設定
#character_set_server = utf8mb4
collation_server = utf8mb4_ja_0900_as_cs_ks
# パスワードポリシー
validate_password.length=4
validate_password.mixed_case_count=0
validate_password.number_count=0
validate_password.special_char_count=0
validate_password.policy=LOW
# デフォルト認証プラグイン
#default_authentication_plugin=caching_sha2_password
default_authentication_plugin=mysql_native_password
文字コードはデフォルトで utf8mb4 になってるので未指定
照合順序は utf8mb4_ja_0900_as_cs_ks にしてみました
詳細はこちら
https://mysqlserverteam.com/mysql-8-0-kana-sensitive-collation-for-japanese-ja_jp/
パスワードポリシーはお好きに
デフォルト認証プラグインを native にしておかないと現状はいろいろめんどくさそう。。
MySQL 再起動（設定反映）
systemctl restart mysqld
MySQL 設定確認
mysql -u root -p
# 文字コード確認（デフォルトで utf8mb4 になっている）
mysql> show variables like '%char%';
+--------------------------------------+--------------------------------------+
| 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/share/community-mysql/charsets/ |
| validate_password.special_char_count | 0 |
+--------------------------------------+--------------------------------------+
9 rows in set (0.01 sec)
# 照合順序確認（ utf8mb4_ja_0900_as_cs_ks になってる）
mysql> show variables like '%collation%';
+-------------------------------+--------------------------+
| Variable_name | Value |
+-------------------------------+--------------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_ja_0900_as_cs_ks |
| collation_server | utf8mb4_ja_0900_as_cs_ks |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------------+
4 rows in set (0.00 sec)
# 認証プラグイン
mysql> show variables like '%authentication%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.01 sec)
# パスワードポリシー
mysql> show variables like '%validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 4 |
| validate_password.mixed_case_count | 0 |
| validate_password.number_count | 0 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 0 |
+--------------------------------------+-------+
7 rows in set (0.00 sec)
# パスワード有効期限 （ 0 なので無期限 ）
mysql> select @@default_password_lifetime;
+-----------------------------+
| @@default_password_lifetime |
+-----------------------------+
| 0 |
+-----------------------------+
1 row in set (0.00 sec)
DB作成、ユーザ作成
パスワードの形式を native_password にしないと PDOやらプログラム系からアクセスできないことあり
mysql> CREATE DATABASE DBNAME;
mysql> CREATE USER DBUSER@localhost IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT ALL PRIVILEGES on DBNAME.* to DBUSER@localhost;
mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| DBUSER | localhost | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
おっと、root が sha2 になってる。。phpMyAdmin とかでめんどくさいので変えておく
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| DBUSER | localhost | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
以上
MySQL8になりたての。。5.7あたりからかな？のめんどくさいところがだいぶ改善されてきてますね
このまま正常進化してほしいものです。。
↓↓↓↓↓以下は古い記事です↓↓↓↓↓
MySQL 8.0 のインストールと設定の覚書（自分用）
(CentOS7)
リポジトリ追加
yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
5.7だけど 8.0のリポジトリも入る
MariaDB の削除
yum -y remove mariadb-libs
インストール
yum --enablerepo mysql80-community install mysql-server
自動起動設定
systemctl enable mysqld
systemctl start mysqld
初期パスワードの確認
grep -i password /var/log/mysqld.log
・・・・・ [Note] A temporary password is generated for root@localhost: e7G<>k/od<lf
初期パスワードの変更
パスワードポリシーを下げて？から、従来のパスワード管理方式でパスワードを変更しときます
mysql -u root -p
Enter password: ★★ここに確認した初期パスワードを入力★★
mysql> SET GLOBAL validate_password.length=4;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL validate_password.mixed_case_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL validate_password.number_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL validate_password.special_char_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL validate_password.policy=LOW;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Query OK, 0 rows affected (0.05 sec)
mysql> show variables like '%validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 4 |
| validate_password.mixed_case_count | 0 |
| validate_password.number_count | 0 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 0 |
+--------------------------------------+-------+
7 rows in set (0.01 sec)
mysql> quit
Bye
MySQL セキュア設定
パスワードポリシー LOW で設定可能なこと
mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
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: 0
Using existing password for root.
Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : N
... skipping.
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
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!
設定
文字コードとかパスワードポリシーとか [mysqld]セクションに追記
/etc/my.cnf
[mysqld]
・・・・・
### 文字コード設定/照合順序設定
character_set_server = utf8mb4
collation_server = utf8mb4_ja_0900_as_cs
### パスワードポリシー
validate_password.length=4
validate_password.mixed_case_count=0
validate_password.number_count=0
validate_password.special_char_count=0
validate_password.policy=LOW
### デフォルト認証プラグイン
#default_authentication_plugin=caching_sha2_password
default_authentication_plugin=mysql_native_password
2018-08-09 追記：
最新にアップデートして 8.0.12 にしたら phpMyAdmin(4.8.2) や PDO(php-pdo-7.2.8) で接続できなくなった
設定ファイルに上記「 デフォルト認証プラグイン 」を追記して native_password を明示してやる
MySQL再起動
systemctl stop mysqld
systemctl start mysqld
MySQL 設定確認
mysql -u root -p
###（文字コード確認）
mysql> show variables like '%char%';
+--------------------------------------+----------------------------+
| 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/share/mysql/charsets/ |
| validate_password_special_char_count | 1 |
+--------------------------------------+----------------------------+
9 rows in set (0.01 sec)
#（照合順序確認）
mysql> show variables like '%collation%';
+----------------------+-----------------------+
| Variable_name | Value |
+----------------------+-----------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_ja_0900_as_cs |
| collation_server | utf8mb4_ja_0900_as_cs |
+----------------------+-----------------------+
3 rows in set (0.00 sec)
#（パスワードポリシー）
mysql> show variables like '%validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 4 |
| validate_password.mixed_case_count | 0 |
| validate_password.number_count | 0 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 0 |
| validate_password_check_user_name | ON |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
14 rows in set (0.01 sec)
（パスワード有効期限）
mysql> select @@default_password_lifetime;
+-----------------------------+
| @@default_password_lifetime |
+-----------------------------+
| 0 |
+-----------------------------+
1 row in set (0.00 sec)
（デフォルト認証プラグイン）
mysql> show variables like '%authentication%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)
「 collation_connection 」が違うのが気持ち悪いけど、そのうち調べる
「 validate_password_ｘｘｘｘ 」と 「 validate_password.ｘｘｘ 」があって気持ち悪いけど・・過渡期なのかな？
2018/09/12 追記
https://dev.mysql.com/doc/refman/8.0/en/validate-password.html
https://dev.mysql.com/doc/refman/8.0/en/validate-password-options-variables.html
「 validate_password_ｘｘｘｘ 」と 「 validate_password.ｘｘｘ 」について、どうやら「プラグイン」と「コンポーネント」の違いだったようです。
「プラグイン（validate_password_ｘｘｘｘ）」は将来廃止されるとのことなので、
「コンポーネント（validate_password.ｘｘｘ）」の方のみ設定しておけばよさそう。
両方設定されているときは「コンポーネント（validate_password.ｘｘｘ）」のほうが優先されるみたいだし。
なので、「 mysql_secure_installation 」ではプラグインを有効にしない！にしたほうがよさげです。
そのうち「 mysql_secure_installation 」でもコンポーネントを設定するようになるのかもですが．．
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: N ＜＝＝プラグインは 「 Ｎ 」で
私もですが、プラグインを有効にしちゃってる場合は、無効にしといたほうが良いかも
mysql> show variables like 'validate_pass%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 4 |
| validate_password.mixed_case_count | 0 |
| validate_password.number_count | 0 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 0 |
| validate_password_check_user_name | ON |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
14 rows in set (0.00 sec)
mysql> UNINSTALL PLUGIN validate_password; <== プラグイン無効化
Query OK, 0 rows affected, 1 warning (0.11 sec)
mysql> show variables like 'validate_pass%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 4 |
| validate_password.mixed_case_count | 0 |
| validate_password.number_count | 0 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 0 |
+--------------------------------------+-------+
7 rows in set (0.01 sec)
mysql>
情報を挙げてくださる方々にホント感謝です！！
http://www.denet.ad.jp/technology/2018/07/mysql80.html
###MySQL DB作成 ユーザ作成
パスワードの形式を native_password にしないと PDOやらプログラム系からアクセスできないことあり
mysql> CREATE DATABASE DBNAME;
mysql> CREATE USER DBUSER@localhost IDENTIFIED WITH mysql_native_password BY 'user_password';
mysql> GRANT ALL PRIVILEGES on DBNAME.* to DBUSER@localhost;
mysql> select user, host, plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| DBUSER | localhost | mysql_native_password |
--
mysql.h
8.0 にしたら、コンパイルが通らないソースがあった
my_bool が消えてるのが原因
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html
Incompatible Change: The my_bool type is no longer used in MySQL source code. Any third-party code that used this type to represent C boolean variables should use the bool or int C type instead.
とりあえず bool か int にしとくか
5.7 の mysql.h を見ると char だけど。。
［ typedef char my_bool; ］