MySQL 8.0 mysql_secure_installation エラー

MySQL 8.0 インストール時に、「mysql_secure_installation」でエラーになるので、いろいろと調べておりました。

環境は、Ubuntu 20.04

Conoha VPSで、行っています。

事前に、apt-update,apt-upgradeを実施しました。

mysql-server-8.0 基本インストール

# apt install mysql-server-8.0
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages were automatically installed and are no longer required:
  libfwupdplugin1 linux-headers-5.4.0-66 linux-headers-5.4.0-66-generic linux-image-5.4.0-66-generic linux-modules-5.4.0-66-generic
  linux-modules-extra-5.4.0-66-generic
Use 'apt autoremove' to remove them.
The following additional packages will be installed:
  libcgi-fast-perl libcgi-pm-perl libencode-locale-perl libfcgi-perl libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl
  libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libmecab2 libtimedate-perl liburi-perl mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0
  mysql-client-core-8.0 mysql-common mysql-server-core-8.0
Suggested packages:
  libdata-dump-perl libipc-sharedcache-perl libwww-perl mailx tinyca
The following NEW packages will be installed:
  libcgi-fast-perl libcgi-pm-perl libencode-locale-perl libfcgi-perl libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl
  libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libmecab2 libtimedate-perl liburi-perl mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0
  mysql-client-core-8.0 mysql-common mysql-server-8.0 mysql-server-core-8.0
0 upgraded, 22 newly installed, 0 to remove and 0 not upgraded.
Need to get 31.4 MB of archives.
After this operation, 261 MB of additional disk space will be used.
Do you want to continue? [Y/n] y

(略)

done!
update-alternatives: using /var/lib/mecab/dic/ipadic-utf8 to provide /var/lib/mecab/dic/debian (mecab-dictionary) in auto mode
Setting up libhtml-parser-perl (3.72-5) ...
Setting up libhttp-message-perl (6.22-1) ...
Setting up libcgi-pm-perl (4.46-1) ...
Setting up libhtml-template-perl (2.97-1) ...
Setting up libcgi-fast-perl (1:2.15-1) ...
Processing triggers for systemd (245.4-4ubuntu3.17) ...
Processing triggers for man-db (2.9.1-1) ...
Processing triggers for libc-bin (2.31-0ubuntu9.9) ...

↑MySQL 8.0 が入りました。

# systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Thu 2022-XX-XX XX:XX:XX JST;
   Main PID: 1918 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 2273)
     Memory: 359.8M
     CGroup: /system.slice/mysql.service
             └─1918 /usr/sbin/mysqld

systemd[1]: Starting MySQL Community Server...
systemd[1]: Started MySQL Community Server.

↑MySQL が、自動的に起動をしています。

MySQL 基本設定 mysql_secure_installation

# 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

↑「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

↑「0」を選ぶ(2でもいい)

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

↑「このパスワードでいい?」と言われているので、「y」を押す。

... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.

↑ここで、上記のエラーになります。

mysql_secure_installation エラー解消方法

調べると、このエラーに遭遇している方は、少ないようで(本当?)・・・

解決方法 トライ1 safe mode 起動

mysqldを セーフモードで、起動すればいいという情報がありましたが・・・

# systemctl stop mysql

↑一度、mysqlを止めます。

# mkdir /var/run/mysqld
# chown mysql:mysql /var/run/mysqld
# mysqld_safe --skip-grant-tables &

# mysql_secure_installation

Securing the MySQL server deployment.

(略)

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
 ... Failed! Error: The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

↑と、エラーになってしまいました。

# mysqld_safe &

↑と、safe mode 起動時に、オプションを外すと

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
 ... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.

↑と、やはり、エラーになってしまいました。

解決方法 トライ2 パスワードを設定 こちらで解決

# mysql -u root -p
Enter password:

↑パスワードなし(そのまま、Enterキーを押す)で入ります。

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.29-0ubuntu0.20.04.3 (Ubuntu)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'aaaaaa';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

↑単純なパスワードだと怒られます(面倒ですが・・・)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by '(複雑なパスワード)';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

↑変更が出来ました。

mysql> Bye
#

↑一度、抜けます。

あらためて、mysql_secure_installation

# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

↑「mysql_secure_installation」を実行します。パスワードを聞いてきますので、直前で、設定したパスワードを入れます。

The 'validate_password' component is installed on the server.
The subsequent steps will run with the existing configuration
of the component.
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」としてもいいですが、テスト的に「y」を入れて、パスワードを変更してみます。

Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

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」を押して進めます。

Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y

↑これで、新しいパスワードに変更されました。

以下、「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!

↑これで、完了をしました。

もっと、スムーズな方法があるのかもしれません・・・(笑)

タイトルとURLをコピーしました