MySQL 8.0 初期設定覚書

MySQL 8.0 のインストールと設定の覚書（自分用）

# － 2019/2/5 － 
新しい環境にインストールしたらいろいろ変わってたので追記
(fedora29)

## インストール

```bash
sudo dnf install  community-mysql-server

・・・
 community-mysql-server    x86_64    8.0.13-1.fc29      updates      24 M
・・・

```
 8.0.13 が入りました


## サービス起動

```bash
sudo systemctl  enable  mysqld
sudo systemctl  start   mysqld
```


## セキュア設定

```bash
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 はプラグインがなくなって、コンポーネントのみになってました
わざわざログファイルを確認して初期パスワードを調べなくても設定できるようになってる
これで楽になりましたね
（本来はこうだよね）




## 設定

文字コードとか認証プラグインとか

```bash:/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/)
https://mysqlserverteam.com/mysql-8-0-kana-sensitive-collation-for-japanese-ja_jp/

パスワードポリシーはお好きに

デフォルト認証プラグインを native にしておかないと現状はいろいろめんどくさそう。。



## MySQL 再起動（設定反映）

```bash
systemctl  restart  mysqld
```


## MySQL 設定確認

```sql
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やらプログラム系からアクセスできないことあり

```sql
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 とかでめんどくさいので変えておく

```sql:
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)


### リポジトリ追加

```bash
yum localinstall  https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
```

5.7だけど 8.0のリポジトリも入る


### MariaDB の削除

```bash
yum -y  remove  mariadb-libs
```


### インストール

```bash
yum --enablerepo mysql80-community install mysql-server
```


### 自動起動設定

```bash
systemctl enable mysqld
systemctl start  mysqld
```


### 初期パスワードの確認

```bash
grep -i password /var/log/mysqld.log
      ・・・・・ [Note] A temporary password is generated for root@localhost: e7G<>k/od<lf
```

### 初期パスワードの変更

パスワードポリシーを下げて？から、従来のパスワード管理方式でパスワードを変更しときます

```sql

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 で設定可能なこと

```bash
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]セクションに追記

```conf:/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再起動

```bash
systemctl stop   mysqld
systemctl start  mysqld
```


### MySQL 設定確認

```sql

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 」でもコンポーネントを設定するようになるのかもですが．．

```bash
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  　　 ＜＝＝プラグインは 「 Ｎ 」で
```



私もですが、プラグインを有効にしちゃってる場合は、無効にしといたほうが良いかも

```sql

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やらプログラム系からアクセスできないことあり

```sql

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;　］