MySQL：Ubuntu安装并简单配置MySQL

祁通

2023-12-01

环境：Ubuntu 20.10

1，执行sudo apt update 更新本地存储库索引。

2，执行sudo apt install mysql-server 从APT存储库安装MySQL:

$ sudo apt install mysql-server
正在读取软件包列表... 完成
正在分析软件包的依赖关系树       
正在读取状态信息... 完成       
下列软件包是自动安装的并且现在不需要了：
  libevent-2.1-7 libfwupd2 libfwupdplugin1 libgcab-1.0-0 libjcat1 libjuh-java libjurt-java liblibreoffice-java libllvm11 libmwaw-0.3-3
  libnatpmp1 libneon27-gnutls liborcus-0.15-0 liborcus-parser-0.15-0 libridl-java libsmbios-c2 libunoloader-java libwps-0.4-4 libxmlb1
  tpm-udev transmission-common
使用'sudo apt autoremove'来卸载它(它们)。
将会同时安装下列软件：
  libaio1 libcgi-fast-perl libcgi-pm-perl libevent-core-2.1-7 libevent-pthreads-2.1-7 libfcgi-perl libhtml-template-perl libmecab2
  mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0 mysql-client-core-8.0 mysql-server-8.0 mysql-server-core-8.0
建议安装：
  libipc-sharedcache-perl mailx tinyca
下列【新】软件包将被安装：
  libaio1 libcgi-fast-perl libcgi-pm-perl libevent-core-2.1-7 libevent-pthreads-2.1-7 libfcgi-perl libhtml-template-perl libmecab2
  mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0 mysql-client-core-8.0 mysql-server mysql-server-8.0 mysql-server-core-8.0
升级了 0 个软件包，新安装了 16 个软件包，要卸载 0 个软件包，有 9 个软件包未被升级。
需要下载 31.5 MB 的归档。
解压缩后会消耗 262 MB 的额外空间。
您希望继续执行吗？ [Y/n] Y
...

3，执行mysql --version查看版本：

$ mysql --version
mysql  Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

4，执行systemctl status mysql.service查看MySQL服务状态：

$ systemctl status mysql.service
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Tue 2022-01-11 13:39:40 CST; 17min ago
   Main PID: 2162186 (mysqld)
     Status: "Server is operational"
      Tasks: 37 (limit: 11835)
     Memory: 347.2M
     CGroup: /system.slice/mysql.service
             └─2162186 /usr/sbin/mysqld

1月 11 13:39:40 DFL-VM systemd[1]: Starting MySQL Community Server...
1月 11 13:39:40 DFL-VM systemd[1]: Started MySQL Community Server.

安装mysql-server后，MySQL服务器会自动启动，否则需要执行sudo systemctl start mysql手动启动。

5，执行sudo mysql_secure_installation安全安装mysql server：

$ 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

6，在VALIDATE PASSWORD插件中为root用户设置安全密码：

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

7，是否删除匿名测试用户的信息：

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.

8，是否禁止远程系统的root登录：

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) : N

 ... skipping.

9，是否删除“测试”数据库：

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.

10，重新加载特权表，以使上述更改生效：

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!

11，执行sudo mysql登录MySQL：

$ sudo mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, 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>

12，修改登录的验证方式：

执行SELECT user,authentication_string,plugin,host FROM mysql.user;查看登录的验证方式：

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| user             | authentication_string                                                  | plugin                | host      |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| debian-sys-maint | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | caching_sha2_password | localhost |
| mysql.infoschema | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | caching_sha2_password | localhost |
| mysql.session    | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | caching_sha2_password | localhost |
| mysql.sys        | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | caching_sha2_password | localhost |
| root             |                                                                        | auth_socket           | localhost |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
5 rows in set (0.00 sec)

执行ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';修改验证方式：

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| debian-sys-maint | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | caching_sha2_password | localhost |
| mysql.infoschema | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | caching_sha2_password | localhost |
| mysql.session    | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | caching_sha2_password | localhost |
| mysql.sys        | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | caching_sha2_password | localhost |
| root             | yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy                                | mysql_native_password | localhost |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
5 rows in set (0.00 sec)

遇到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    | 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      |
+--------------------------------------+--------+
7 rows in set (0.01 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 'your_password';
Query OK, 0 rows affected (0.00 sec)

13，创建管理员admin：

mysql> create user 'admin'@'localhost' identified by 'your_password';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'admin'@'localhost' with grant option;
Query OK, 0 rows affected (0.01 sec)

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

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| user             | authentication_string                                                  | plugin                | host      |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| admin            | $A$005$`B9VXa`;C/j=	GQQQh5b6aHE/tvm0nmVzwPtBo83r/5uu6IA | caching_sha2_password | localhost |
| debian-sys-maint | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | caching_sha2_password | localhost |
| mysql.infoschema | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | caching_sha2_password | localhost |
| mysql.session    | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | caching_sha2_password | localhost |
| mysql.sys        | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | caching_sha2_password | localhost |
| root             | yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy                                | mysql_native_password | localhost |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
6 rows in set (0.00 sec)

mysql> ALTER USER 'admin'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| user             | authentication_string                                                  | plugin                | host      |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| admin            | yyyyyyyyyyyyyyzzzzzzzzzzyyyyxxxxxyyyyyyyyy                             | mysql_native_password | localhost |
| debian-sys-maint | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | caching_sha2_password | localhost |
| mysql.infoschema | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | caching_sha2_password | localhost |
| mysql.session    | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | caching_sha2_password | localhost |
| mysql.sys        | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | caching_sha2_password | localhost |
| root             | yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy                                | mysql_native_password | localhost |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
6 rows in set (0.00 sec)

14，Ubuntu root用户登录MySQL：

Ubuntu中的普通用户登录MySQL后，因为权限问题无法实现创建数据库等操作，所以需要切换到 root 用户。

dfl@DFL-VM:~$ su root
密码： 
root@DFL-VM:/home/dfl# sudo mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, 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>

15，卸载 MySQL：

关闭服务：
$ sudo systemctl stop mysql.service && sudo systemctl disable mysql.service

卸载 MySQL:
$ sudo apt purge mysql*

清理依赖：
$ sudo apt autoremove

清空缓存：
$ sudo apt autoclean

Ubuntu: install MySQL
Ubuntu安装MySQL
mysql版本:'for the right syntax to use near ‘identified by ‘password’ with grant option’
解决ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
Mysql8.0默认加密连接方式修改
 在Ubuntu 20.04上安装MySQL教程

MySQL：Ubuntu安装并简单配置MySQL

相关阅读

相关文章

相关问答

相关文档