在Ubuntu上安装MySQL

2020年6月28日 追加说明不同版本的系统变量格式有所不同。

环境Ubuntu服务器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用户「新密码：」「重新输入新密码：」请输入root用户密码。「请输入 0 = 低、1 = 中等和 2 = 强:」这一段会造成用户创建密码时的麻烦，所以我选择了低（稍后可更改）。然后就是“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

当前的设置 de shè zhì)

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)

请注意不同版本的系统变量的格式可能会有所不同。

validate_password.xxx

validate_password_xxx

将密码的字符长度更改为5个字符（在这种情况下将长度更改为5个字符）。

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)

授予权限授予所有权限。同时，若将[数据库或表]设为*.*，则适用范围将涵盖所有数据库和表。

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)

以上です。

もし間違いなどありましたら、教えて頂けると幸いです。