61
73

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

MySQL 8.0 初期設定覚書

Last updated at Posted at 2018-01-04

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

61
73
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
61
73

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?