追記 2020/6/28 バージョンによりシステム変数の形式が違う説明を追記
環境
Ubuntu Server 18.04.3 LTS
MySQLのインストール
MySQLサーバとクライアントツールのインストールをします。
$ sudo apt install mysql-server mysql-client
インストールしたらバージョン確認
$ mysql --version
mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper
サービスの起動確認
$ sudo service mysql status
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Sat 2019-10-12 23:34:41 JST; 21min ago
Main PID: 2466 (mysqld)
Tasks: 27 (limit: 2319)
CGroup: /system.slice/mysql.service
mq2466 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
10月 12 23:34:40 ubuntu-server-18-04 systemd[1]: Starting MySQL Community Server...
10月 12 23:34:41 ubuntu-server-18-04 systemd[1]: Started MySQL Community Server.
rootユーザーの設定
「New password:」「Re-enter new password:」にrootユーザーのパスワードを入力します。「Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:」の所は、後で行うユーザー作成のパスワード設定のときに面倒になるので、LOWにしてます。（後で変更可）あとは「y」です。
$ sudo mysql_secure_installation
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
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
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!
MySQLにログイン
$ sudo mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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>
ユーザーを追加する前にパスワードポリシーの設定を変更
パスワードポリシーが厳しくなっているので、基準を満たしていないパスワードを入力すると下記のようにエラーがでます。面倒なので基準を緩くします。
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
現在の設定
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
+--------------------------------------+-------+
7 rows in set (0.00 sec)
※バージョンによりシステム変数の形式が違うので注意してください。
MySQL8.0
validate_password.xxx
MySQL5.7
validate_password_xxx
パスワードの文字列の長さを変更（この場合は長さを５文字に変更しています）
mysql> set global validate_password_length=5;
Query OK, 0 rows affected (0.00 sec)
ポリシーの変更を行う場合（この場合はLOWに設定しています）
mysql> set global validate_password_policy=LOW;
変更されたか確認
mysql> show variables like 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 5 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
+--------------------------------------+-------+
7 rows in set (0.00 sec)
ユーザー追加
ユーザー名、パスワードには、任意の文字列を設定し、ホスト名にはMySQLへ接続するホストを指定します。
mysql> CREATE USER 'ユーザー名'@'ホスト名' IDENTIFIED BY 'パスワード';
（例）この場合は、ユーザー名が'houtarou'、ホスト名が'localhost'、パスワードが'kirin'です。
mysql> CREATE USER 'houtarou'@'localhost' IDENTIFIED BY 'kirin';
Query OK, 0 rows affected (0.00 sec)
ユーザーの一覧表示
'houtarou'が追加されています。
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| debian-sys-maint | localhost |
| houtarou | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
権限付与
全ての権限を付与。また、[DB or Table]を*.*にすると、全てのDB、Tableが適用対象になります。
mysql> GRANT all ON [DB or Table] TO 'ユーザー名'@'ホスト名';
（例）この場合は、ユーザー名が'houtarou'、ホスト名が'localhost'です。全てを対象にしています。
mysql> GRANT all ON *.* TO 'houtarou'@'localhost';
Query OK, 0 rows affected (0.00 sec)
権限の確認
mysql> SHOW GRANTS FOR 'houtarou'@'localhost';
+-------------------------------------------------------+
| Grants for houtarou@localhost |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'houtarou'@'localhost' |
+-------------------------------------------------------+
1 row in set (0.00 sec)
ユーザーの削除
mysql> DROP USER 'ユーザー名'@'ホスト名';
（例）この場合は、ユーザー名が'houtarou'、ホスト名が'localhost'です。
mysql> DROP USER 'houtarou'@'localhost';
Query OK, 0 rows affected (0.01 sec)
削除されたか確認
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
以上です。
もし間違いなどありましたら、教えて頂けると幸いです。