Edited at

MySQL 8.0 初期設定覚書

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_xxxx 」と 「 validate_password.xxx 」があって気持ち悪いけど・・過渡期なのかな?


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_xxxx 」と 「 validate_password.xxx 」について、どうやら「プラグイン」と「コンポーネント」の違いだったようです。

「プラグイン(validate_password_xxxx)」は将来廃止されるとのことなので、

「コンポーネント(validate_password.xxx)」の方のみ設定しておけばよさそう。

両方設定されているときは「コンポーネント(validate_password.xxx)」のほうが優先されるみたいだし。

なので、「 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    <==プラグインは 「 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; ]