WSL(Ubuntu-20.04) 安装 MySQL(MariaDB)

MariaDB是一个开源的关系数据库管理系统。它最初被设计为MySQL的向后兼容二进制二进制替换。
MariaDB由MySQL的原始开发人员和开放源代码社区开发和维护。
本指南说明了如何在Ubuntu 20.04(WSL)上安装和保护MariaDB。

1、基础条件

我们假设您具有root用户或具有sudo权限的用户对Ubuntu(WSL)服务器的管理访问权限。

2、安装MariaDB

sudo apt update
sudo apt -y install mariadb-server

安装完成后,MariaDB服务不会自动启动,请使用下面命令启动MariaDB服务:

sudo service mysql start

3、配置MariaDB

MariaDB服务有一个脚本叫做mysql_secure_installation,它允许你可以很容易提高数据库服务器安全。
执行不带参数的脚本:

sudo mysql_secure_installation

该脚本将提示您输入root密码,由于您尚未设置root密码,只需在此处按“ Enter”。

Enter current password for root (enter for none):

在下一个提示符下,将要求您设置MySQL root用户的密码,输入n

Set root password? [Y/n] n

在Ubuntu上,auth_socket默认情况下插件会对MariaDB根用户进行身份验证。该插件通过检查调用客户端程序的本地系统用户是否与指定的MariaDB用户名匹配来工作。

接下来,将要求您删除匿名用户,限制root用户对本地计算机的访问,删除测试数据库并重新加载特权表。可以按照自己的喜好来修改:

# sudo mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
# 修改root用户密码
Set root password? [Y/n] n
 ... skipping.

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB 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? [Y/n] 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.
# 禁止root远程登录
Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MariaDB 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? [Y/n] n
 ... skipping.

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
# 现在重新加载特权表
Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

4、以root身份登录

要从命令行与MariaDB服务器进行交互,请使用mysql客户端实用程序或其别名mariadb。此工具是作为MariaDB服务器软件包的依赖项安装的。

该auth_socket插件对localhost通过Unix套接字文件从进行连接的用户进行身份验证。这意味着您不能通过提供密码来以root用户身份进行身份验证。

要以root用户身份登录MariaDB服务器(注意:当前方式下不再需要输入-u root -p参数了),请输入:

sudo mysql

将为您提供MariaDB shell,如下所示:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 54
Server version: 10.3.31-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>
MariaDB [(none)]> quit
Bye

5、创建一个超级用户

在运行MariaDB的Ubuntu系统上,默认情况下,将root MariaDB用户设置为使用unix_socket插件而不是使用密码进行身份验证。 由于服务器使用root帐户执行日志轮换以及启动和停止服务器等任务,因此最好不要更改root帐户的身份验证详细信息。 相反,软件包维护者建议为基于密码的访问创建一个单独的管理帐户。

如果要使用外部程序(例如phpMyAdmin)以root用户身份登录MariaDB服务器,则有两个选择。

第一个是将身份验证方法从更改auth_socket为mysql_native_password。您可以通过运行以下命令来做到这一点:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'very_strong_password';
FLUSH PRIVILEGES;

推荐的第二个选项是创建一个新的专用管理用户,该用户可以访问所有数据库:

GRANT ALL PRIVILEGES ON *.* TO 'administrator'@'localhost' IDENTIFIED BY 'very_strong_password';
FLUSH PRIVILEGES;

您可以使用任何想要的名称来命名管理用户,但是请确保使用强密码。完整的示例如下:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 41
Server version: 10.3.31-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'guess@#$%862';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> QUIT;
Bye

您可以使用mysqladmin工具(可以运行管理命令的客户端)测试该新用户。 以下mysqladmin命令以admin用户身份连接到MariaDB,并在提示用户输入密码后返回版本号:

sudo mysqladmin -u admin -p version

将收到类似于以下的输出:

sudo mysqladmin -u admin -p version
Enter password:
mysqladmin  Ver 9.1 Distrib 10.3.31-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version          10.3.31-MariaDB-0ubuntu0.20.04.1
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 10 min 31 sec

Threads: 6  Questions: 140  Slow queries: 0  Opens: 38  Flush tables: 1  Open tables: 31  Queries per second avg: 0.221

6、管理MariaDB服务和配置

你可能感兴趣的