如何在 Ubuntu 20.04 LTS Linux 上安装 MySQL 服务器

如何在 Ubuntu 20.04 LTS Linux 服务器上安装 MySQL 服务器 8.0？如何在新设置的 Ubuntu 服务器上添加新的 MySQL 用户和数据库？

MySQL 8.0 版是一个免费的开源数据库系统，互联网上大多数 Web 应用程序和网站都在使用它。通常，MySQL 是 LAMP（Linux、Apache、MySQL、Perl/Python/PHP）堆栈的一部分。流行的开源软件（如 WordPress、MediaWiki 等）被 MySQL 大量用作数据库存储引擎。让我们看看如何在 Ubuntu 20.04 LTS Linux 服务器上为您的 Web 应用程序或博客系统安装 MySQL 服务器 8.x 版。

如何在 Ubuntu 20.04 LTS 服务器上安装 MySQL 服务器

步骤如下。首先，打开终端应用程序并使用 ssh 命令登录到您的服务器，然后更新 Ubuntu repo 以在 Ubuntu Linux 上应用安全更新和修复：
sudo apt update
sudo apt upgrade
## Reboot Linux system if a new Linux kernel installed ##
sudo reboot

步骤 1 - 安装 MySQL 服务器

让我们搜索包：
apt search mysql-server
获取有关 mysql-server 版本的信息，然后安装 mysql 服务器：
sudo apt info mysql-server
输出：

Package: mysql-server
Version: 8.0.21-0ubuntu0.20.04.4
Priority: optional
Section: database
Source: mysql-8.0
Origin: Ubuntu
Maintainer: Ubuntu Developers 
Original-Maintainer: Debian MySQL Maintainers 
Bugs: https://bugs.launchpad.net/ubuntu/+filebug
Installed-Size: 111 kB
Depends: mysql-server-8.0
Homepage: http://dev.mysql.com/
Task: lamp-server
Download-Size: 9552 B
APT-Sources: http://archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages
Description: MySQL database server (metapackage depending on the latest version)
 This is an empty package that depends on the current "best" version of
 mysql-server (currently mysql-server-8.0), as determined by the MySQL
 maintainers. Install this package if in doubt about which MySQL
 version you need. That will install the version recommended by the
 package maintainers.
 .
 MySQL is a fast, stable and true multi-user, multi-threaded SQL database
 server. SQL (Structured Query Language) is the most popular database query
 language in the world. The main goals of MySQL are speed, robustness and
 ease of use.

在 Ubuntu 20.04 LTS 上安装：
sudo apt install mysql-server

第 2 步 - 保护 MySQL 服务器

我们需要通过运行以下命令来保护新安装的 MySQL 服务器：
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

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

当然，我们也必须删除匿名用户：

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.

不允许通过 LAN 或 Internet 登录 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) : Y
Success.

最后，删除一个名为test的Oracle MySQL数据库并重新加载权限：

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! 

步骤 3 - 在启动时启用 MySQL 服务器

确保我们的服务器在 Ubuntu 20.04 LTS 启动时启动：
sudo systemctl is-enabled mysql.service
如果未启用，请键入以下命令来启用服务器：
sudo systemctl enable mysql.service
通过键入以下 systemctl 命令来验证服务器状态：
sudo systemctl status mysql.service

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2020-10-23 09:20:17 UTC; 16min ago
   Main PID: 1129 (mysqld)
     Status: "Server is operational"
      Tasks: 40 (limit: 4680)
     Memory: 338.6M
     CGroup: /system.slice/mysql.service
             └─1129 /usr/sbin/mysqld

Oct 23 09:20:16 ubuntu-pdb systemd[1]: Starting MySQL Community Server...
Oct 23 09:20:17 ubuntu-pdb systemd[1]: Started MySQL Community Server.

步骤 4 - 启动/停止/重新启动 MySQL 服务器

我们可以使用命令行选项本身来控制 Ubuntu 上的 MySQL 服务器。让我们用
sudo systemctl start mysql.service
停止 MySQL 服务器来启动它，输入：
sudo systemctl stop mysql.service
重新启动 MySQL 服务器，如下所示：
sudo systemctl restart mysql.service
我们可以使用 journalctl 命令查看 MySQL 服务日志，如下所示：示例日志文件：
sudo journalctl -u mysql.service -xe
sudo tail -f /var/log/mysql/error.log

2020-10-23T09:20:13.285531Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp' in the path is accessible to all OS users. Consider choosing a different directory.
2020-10-23T09:20:13.309795Z 7 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.21-0ubuntu0.20.04.4).
2020-10-23T09:20:15.758296Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21-0ubuntu0.20.04.4)  (Ubuntu).
2020-10-23T09:20:16.780030Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21-0ubuntu0.20.04.4) starting as process 1129
2020-10-23T09:20:16.789608Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-10-23T09:20:17.046682Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-10-23T09:20:17.151840Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2020-10-23T09:20:17.220152Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-10-23T09:20:17.220347Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-10-23T09:20:17.242986Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.21-0ubuntu0.20.04.4'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu).

步骤 5 – 登录 MySQL 服务器进行测试

到目前为止，我们学习了如何在 Ubuntu 20.04 LTS 上安装、设置、保护和启动/停止 MySQL 服务器版本 8。现在是时候以 root（管理员）用户身份登录了。语法是： STATUS 命令显示版本和其他信息： 我们可以按如下方式查看 MySQL 版本：
mysql -u root -p
mysql -u USER -h host -p

STATUS;

SHOW VARIABLES LIKE "%version%";

步骤 6 - 创建新的 MySQL 数据库和用户/密码

让我们创建一个名为 wpblog 的新数据库，输入：
CREATE DATABASE wpblog;
接下来，我将为名为 wpblog 的数据库创建一个名为“wpuser”的新用户，如下所示：
CREATE USER 'wpuser'@'%' IDENTIFIED BY 'Your_Super_Secret_Password';
最后，授予权限：
GRANT SELECT, INSERT, UPDATE, DELETE ON wpblog.* TO 'wpuser'@'%';
当然，我也可以授予所有权限，如下所示：
GRANT ALL PRIVILEGES ON wpblog.* TO 'wpuser'@'%';
查看 MySQL 用户及其授予： 测试如下： 其中，
SELECT user,host FROM mysql.user;
SHOW GRANTS for wpuser;

mysql -u wpuser -p wpblog
mysql -u wpuser -h localhost -p wpblog

• -u wpuser: 登录用户
• -h localhost：连接到名为 localhost 的主机
• -p：提示输入密码
• wpblog：连接到名为 wpblog 的数据库

结论

至此，Oracle MySQL 服务器版本 8.x 已在 Ubuntu Linux 20.04 LTS 服务器上正确设置并运行。此外，您还学习了如何为项目添加新数据库、用户和密码。有关 SQL 和其他命令，请参阅 Oracle MySQL 数据库文档。