在Ubuntu上安装MySQL

随着MySQL的升级,其安装配置方式也变得越来越复杂,以至于新手第一次安装可能无法顺利地登录数据库,因此本文对如何在Ubuntu上安装配置MySQL进行简单阐述。

系统环境

  • Ubuntu 18.04
  • MySQL 5.7

安装MySQL

直接从Ubuntu的软件源安装即可:

1
2
sudo apt update
sudo apt install mysql-server

配置MySQL

安全检查

首次配置MySQL,我们最好进行一次安全检查,它会修改一些默认的不安全配置,比如禁止root用户远程登陆,以及删除一些用于测试的账户和数据库等。

1
sudo mysql_secure_installation

它将会做以下几件事:

  1. 安装密码检查插件,它会对密码强度进行检测,并拒绝低强度的密码
  2. 设置root密码
  3. 删除匿名用户
  4. 禁止root用户远程登陆
  5. 删除测试数据库
  6. 重新加载权限配置

使用密码登录root用户

在MySQL 5.7中,root用户默认使用auth_socket进行身份验证,而不是使用密码。这样虽然可以在一定程度上提高安全性,但也增加了复杂性,尤其是当我们想要通过一些工具管理数据库时。要想使用密码登录root用户,需要将认证方式换成mysql_native_password

1
sudo mysql

首先查看一下每个账户在使用哪种认证方式

1
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
1
2
3
4
5
6
7
8
9
+------------------+-------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+-------------------------------------------+-----------------------+-----------+
| root | | auth_socket | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *06B203089FC724D1B1C4B9197F88991FB2D287B3 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

从查询结果可以看出,默认为root用户分配的认证方式就是auth_socket

1
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

注意,这条命令会修改两个地方:

  1. root用户的认证方式修改为mysql_native_password
  2. root用户的密码被设置为password

然后刷新MySQL的权限配置

1
mysql> FLUSH PRIVILEGES;

最后确认一下root用户的认证方式是否被修改

1
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
1
2
3
4
5
6
7
8
9
+------------------+-------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+-------------------------------------------+-----------------------+-----------+
| root | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 | mysql_native_password | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *06B203089FC724D1B1C4B9197F88991FB2D287B3 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

之后,我们就可以使用密码登录root用户了

1
mysql -u root -p

创建新用户

创建用户并设置密码

1
mysql> CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';

该命令创建了用户sammy并将其密码设置为password

对新用户授权

1
mysql> GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

该命令授权sammy拥有所有数据库的所有表的所有权限,但仅限本地登录,如果希望能够远程登陆该用户,将localhost改为具体的IP,或者%表示任意IP。

1
mysql> GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'%' WITH GRANT OPTION;

测试MySQL数据库

1
mysqladmin -p -u root version
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysqladmin  Ver 8.42 Distrib 5.7.28, for Linux on x86_64
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.

Server version 5.7.28-0ubuntu0.18.04.4
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 4 hours 20 min 8 sec

Threads: 3 Questions: 29 Slow queries: 0 Opens: 127 Flush tables: 1 Open tables: 120 Queries per second avg: 0.001

参考资料

How To Install MySQL on Ubuntu 18.04