怎么弄个自己的博客 —— 安装和使用数据库

就算你没有听说过这个名词，你也应该见过这张 meme：

或者这张：

所谓 SQL 注入（injection）是指在输入字符串中注入 SQL 指令，如果设计不良的程序忽略了字符串检查，那么注入的恶意指令就会被数据库服务器误认为是正常的 SQL 语句并执行，从而使得数据库被恶意破坏。

SQL 的原理也非常简单，即数据和代码的混淆，本应该作为数据的 SQL 字段意外传递了其他信息，命令在运行时超过预期的范围，从而允许可能有害的行为。Cloudflare 的教程给出了一些例子，并给出了应对方法，简单总结如下：

• 使用参数化查询。强制开发人员定义所有的 SQL 代码，然后仅将特定参数传递给 SQL 查询；输入的数据明确地给出有限范围，无法进行扩展。这使得数据库可以区分输入的数据和要运行的代码
• 对用户输入进行转义。为了避免用户恶意将 SQL 关键字输入 API 请求中，可以对用户提供的输入进行转移，告诉数据库不要将其解析为命令或条件

与 HTTP 服务器的环境一样，我们使用 Ubuntu Server mininal 系统。我们可以在 MySQL 官网上找到 apt 相关的教程，跟着走就行了。嫌官方教程太简陋的话可以看这个。

需要说明的是，某些博客系统可能会指定某个特定版本的 MySQL，比如 5.7，下面安装的是最新的 MySQL 8.0，我会在下一节给出 5.7 版本的安装方法。具体用哪一个版本的 MySQL 取决于博客系统的要求，高版本并不一定能与低版本兼容。

首先，更新 apt 并安装 mysql-server：

sudo apt update
sudo apt install mysql-server

完成安装后，运行以下命令确保启动了 mysql 服务：

sudo systemctl start mysql.service

再通过以下命令判断 mysql 是否正常运行：

systemctl status mysql

得到如下结果说明安装成功：

按照惯例接下来需要运行 mysql_secure_installation 来进行安全性方面的配置，比如禁止远程 root 登入和删除样本用户。对于 MySQL 8.0 会有如下的问题：

1. 是否设置密码验证插件，该插件会检查用户的密码是否足够强，建议选 Y 和 STRONG（也就是 2）

   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

2. 随后的 PROMPT 会提示你设置 root 用户的密码，需要两次输入

   New password:
   
   Re-enter new password:

   在这一步的时候，你可能会碰到这样的报错：

   ... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication
   method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER
   instead if you want to change authentication parameters.

   然后进入输入密码的死循环。想要退出死循环建议先输入两次相同密码，看见 Do you wish to continue with the password provided? 时再 C-c 。

   出现这种情况的原因是 Ubuntu 上的 MySQL 默认安装后 root 登入 MySQL 默认是不需要密码的，所以也就没有办法设置 New password。为了让 mysql_secure_installation 正常运行下去，我们可以先 sudo MySQL 使用 root 登入 MySQL，再使用下面的代码修改默认密码：

   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Yy@114514';

   随后使用 exit 退出 MySQL，继续运行 sudo mysql_secure_installation 进行配置。

3. 是否删除匿名用户，这个账户用于测试目的，这里可以直接 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, and other key for No) : Y

4. 是否禁用 root 远程登入，直接 Y ：

   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

5. 是否移除测试数据库以及对它的访问，直接 Y ：

   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

6. 重新加载 privilege tables ，使到此位置的所有配置生效，直接 Y ：

   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

7. 完成，此时屏幕会显示 All done! ，到了这里我们就完成了 MySQL 基本的安全配置

对于 Ubuntu Server 22.04，在 apt 里面找不到 MySQL-5.7：

sudo apt-cache policy mysql-server

mysql-server:
  Installed: 8.0.31-0ubuntu0.22.04.1
  Candidate: 8.0.31-0ubuntu0.22.04.1
  Version table:
*** 8.0.31-0ubuntu0.22.04.1 500
       500 http://archive.ubuntu.com/ubuntu jammy-updates/main amd64 Packages
       500 http://archive.ubuntu.com/ubuntu jammy-security/main amd64 Packages
       100 /var/lib/dpkg/status
    8.0.28-0ubuntu4 500
       500 http://archive.ubuntu.com/ubuntu jammy/main amd64 Packages

Wordpress 等博客系统现在（2022-12-17）使用的还是 MySQL-5.7（或者 MariaDB 10.3 或更高），所以我们还是有必要学习一下如何安装 MySQL-5.7。

如果你像我一样已经完成了 MySQL-8.0 的安装，那么你需要首先卸载 MySQL-8.0，才能开始安装 MySQL-5.7。卸载步骤如下：

1. 停止 mysql：

   sudo systemctl stop mysql

   

2. 使用 apt-get 移除所有 mysql 相关的包：

   sudo apt purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*

3. 删除所有的 MySQL 配置：（小心 sudo rm -rf !!!）

   sudo rm -rf /etc/mysql /var/lib/mysql /var/log/mysql

4. 移除所有不需要的包

   sudo apt autoremove
   sudo apt autoclean

在完成了以上操作后，MySQL-8.0 应该已经被完全地移除了，现在我们开始安装 MySQL-5.7。

首先我们前往 https://dev.mysql.com/downloads/repo/apt/ ，目前最新的文件是 mysql-apt-config_0.8.24-1_all.deb 。下载文件后放到服务器上，或者我们下载后对下载链接直接使用 wget ：

wget https://dev.mysql.com/mysql-apt-config_0.8.24-1_all.deb

网上的一些其他教程也许和这里的链接有所不同，甚至你在这个网页上看到的文件也与我看到的不同。这也无所谓，使用能够得到这个配置文件的方法就行了。

接着，参考官方文档运行以下命令来配置软件包：

sudo dpkg -i mysql-apt-config_0.8.24-1_all.deb

由于我这个 Ubnutu Server minimal 环境太简陋了，所有这里没有弹出 Dialog，而直接是字符界面……

这里还是让我们先装上 dialog ：

sudo apt-get install dialog

接着再次 sudo dpkg -i mysql-apt-config_0.8.24-1_all.deb ，可以看到如下界面：

选择第一项后可以看到：

嗯？怎么只有 8.0？看了这个帖子我才发现 Ubuntu 18.04 是最后一版支持 MySQL-5.7 的系统…

那么，除了从源代码安装（还是算了吧）我们只能用之前的配置文件尝试一下了，参考这里使用下面命令下载配置文件。由于之前安装了官网的 mysql-apt-config 配置，这里首先移除掉它：

sudo dpkg -P mysql-apt-config
wget https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.12-1_all.deb

按照教程要求选择 Ubuntu Bionic ，随后和上面一样，选第一项后来到版本选择界面：

这里选择 5.7 即可（不要选 7.5），完成后运行如下命令：

sudo apt-get update

这里出现了一条 Warning： W: http://repo.mysql.con/apt/ubuntu/dists/bionic/InRelease: Key is stored in legacy trusted.gpg keyring (/etc/apt/trusted.gpg), see the DEPRECATION section in apt-key(8) for details 。参考上面的教程可以用以下命令解决：

sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 467B942D3A79BD29
sudo apt-get update

不过文中面对的是 Error，这里的 Warning 我就不管了（笑）。

运行 sudo apt-cache policy mysql-server ，现在就能看到 5.7 版本了：

sudo apt-cache policy mysql-server

mysql-server:
  Installed: 8.0.31-0ubuntu0.22.04.1
  Candidate: 8.0.31-0ubuntu0.22.04.1
  Version table:
*** 8.0.31-0ubuntu0.22.04.1 500
       500 http://archive.ubuntu.com/ubuntu jammy-updates/main amd64 Packages
       500 http://archive.ubuntu.com/ubuntu jammy-security/main amd64 Packages
       100 /var/lib/dpkg/status
    8.0.28-0ubuntu4 500
       500 http://archive.ubuntu.com/ubuntu jammy/main amd64 Packages
    5.7.40-1ubuntu18.04 500
       500 http://repo.mysql.com/apt/ubuntu bionic/mysql-5.7 amd64 Packages

接着，使用如下命令安装 MySQL-5.7：

sudo apt install -f mysql-client=5.7* mysql-community-server=5.7* mysql-server=5.7*

与上面 MySQL-8.0 的安装过程不同，中途它会要求你输入 root 密码，所以在进行基本安全配置时就不会有上面更改 root 密码的死循环。记住这里输入密码是没有回显的。

接下来的步骤和 MySQL-8.0 差不多，运行 mysql_secure_installation 进行安全配置即可，可能有一些顺序上的不同。

完成配置后，使用 systemctl status mysql 检查 MySQL-5.7 的状态，使用 mysql --version 检查 MySQL 版本：

可见 MySQL-5.7 工作正常，我们成功完成了它的安装。

Ubuntu 22 的版本代号是 jammy，而 Ubuntu 18 的代号才是 bionic，我们这相当于是强行在 Ubuntu 22 上装 Ubuntu 18 上的 MySQL，也不知道这种办法还能用多久（应该还挺久的）。如果这种方法还装不上的话，我们要么选择 MariaDB，它的安装过程与 MySQL 非常像（毕竟就是 MySQL 分出来的），要么从源代码安装吧（够折腾的）。当然还有一种方法，那就是使用 docker。

不管你安装的是 8.0 还是 5.7，它们在基础配置上并没有什么区别。MySQL 的配置文件位于 /etc/mysql/ 中，配置文件名是 my.cnf 。如果我们不修改这个文件，那么我们使用的就是 MySQL 的默认配置。MySQL 默认使用 3306 端口，而且禁用远程连接。

由于我们直接使用 apt 安装，参考文档，我们无需配置 MySQL 的数据目录，它位于 /var/lib/mysql 。接下来我会介绍一些常用的 my.cnf 选项，关于配置文件的格式可以参考这里。不同的应用会读取配置文件中的不同 [group] ，客户端会读取 [client] ， mysql 会读取 [mysql] ，mysqld 会读取 [mysqld] ，等等。这里我们只对 [client] ， [mysql] 和 [mysqld] 三个 group 进行介绍。

首先是 [client] ，它用于指定所有客户端的选项，所有的客户端程序都会使用它。文档中指出所有位于 [client] 中的选项必须被所有客户端程序识别，若某程序无法识别某选项会直接退出并实现错误消息。一般会在 [client] 中指定 port 和 socket ，它们是客户端连接的端口和用于本地连接的 socket 文件，这两个选项应与 [mysqld] 中的选项相同。一个简单的 [client] 配置如下所示：

[client]
port = 3306
socket = /path/to/mysql.sock

我们也可以在用户的 .my.cnf （注意 my 前面 有 . ）中指定密码，就比如 yy 在 home 目录中添加如下内容的 .my.cnf 文件：

[client]
user = "root"
password = "Yy@114514"

在 sudo systemctl restart mysql 后，你就可以直接通过 mysql 命令进入数据库了。如果我们不指定 user 的话，客户端会使用当前的用户名来作为数据库用户名，对于 yy 来说就是 yy 。文档中强调使该文件仅能被所属用户访问。

通过以下命令可以观察 mysql 使用哪些配置文件：

接下来是 [mysql] group，它的范围没有 [client] 那么广，只针对 mysql 这一个客户端。其余与 [client] 区别不大。我们可以这样指定 mysql 默认编码：

[mysql]
default-character-set = utf8mb4

最后是选项最多的 [mysqld] group。我安装的 MySQL 5.7 的默认 [mysqld] 配置位于 /etc/mysql/mysql.conf.d/mysqld.cnf ，配置内容大致如下：

和上一篇的 HTTP 服务器类似，我们的目的也不是配置一个超强性能的数据库，大部分选项我们也用不上，这里就简单列一些常见的选项：

• port ，mysqld 的监听端口，默认 3306
• socket ，本地通信 socket 文件
• bind-address ，绑定的 IP 地址， 127.0.0.1 表示只接受 localhost， 0.0.0.0 表示允许任何 IP 访问，为某个特定 IP 则只允许单个 IP 访问
  • 可以接受多个 IP，IP 之间用空格隔开
• skip-networking ，为 1 表示不再监听 TCP/IP 端口，只通过 socket 或管道进行通信。为 0 则关闭该功能
• max-connections ，最大连接数

很多配置都和数据库的性能相关，我不是很熟悉就没有列出来。网上有很多的 MySQL 配置，可以进行参考。

此处我没有对 my.cnf 进行任何修改，我们使用全默认的 MySQL 学习使用它的方式。MySQL 的 Reference 中的 Tutorial 是一份非常不错的教程，跟着它走一遍就可以学会最基本的用法了。

在本机上登入数据库可以使用 mysql -u root -p ，随后输入 root 密码（是 MySQL 的 root 用户密码，不是系统的 root 密码）即可进入。如果要远程登入数据库还需要 -h 选项，本机登入就不需要了。

mysql 是数据库的客户端程序，我们通过它与服务端进行交互。除了使用 mysql 外，我们也可以使用多种编程语言中提供的 connector 来与数据库交互，比如 Python，Node，PHP 等。作为 DMBS，MySQL 可以管理多个数据库，每个数据库下面可以有一张或多张表。我们可以使用 SHOW DATABASES; 来列出所有的数据库：

可见有 4 个已经存在的数据库，它们的名字和作用分别是：

• information_schema ，提供了访问数据库元数据的方式。如数据库名或表名，表的数据类型与访问权限等，通过 SELECT * from information_schema.schemata 我们可以得到与 SHOW DATABASES 相似的结果。实际上 SHOW DATABASES 的结果就取自这张表：
• mysql ，MySQL 的核心数据库，主要负责存储数据库的用户、权限设置、关键字等控制和管理信息。不要轻易修改 mysql 里面的表
• performance_schema ，用于监控 MySQL 的低级运行过程中的资源消耗等情况，在 5.7.x 版本及以上默认启用
• sys ，可以看作简化版的 performance_schema

在上面的截图中除了 root 用户我们也看到了两个名叫 mysql.session 和 mysql.sys 的用户。这是在 5.7 中新增的账号， mysql.sys 用于 sys schema 中对象的定义。使用它可以避免 DBA 重命名或删除 root 用户时发生的问题。该用户被锁定，无法通过客户端连接。 mysql.session 被 MySQL 插件内部使用来访问数据库服务器，同样无法用于连接 MySQL。

我们可以使用 CREATE DATABASE dbname 来创建数据库，并使用 USE dbname 来选中一个数据库，就像这样：

有了数据库之后，我们可以在其中创建表。这里我们创建一张保存编程语言名字和出生年月的表：

对名字我使用了 VARCHAR 类型，对生日我使用了 DATE 类型，其他的类型可以参考文档进行了解。不过考虑编程语言的发明可能很难具体到哪一天，这里我们还是把 DATE 改成 YEAR 再说，使用 ALTER TABLE pls MODIFY birthday YEAR DEFAULT NULL 即可。使用 INSERT 我们可以向表中插入新的列：

MySQL 为我们提供了 LOAD ，可以方便地从文件中添加数据到数据库中，我在这个页面找到了一些语言的出现时间，然后添加到了一个 txt 文件中：

我们使用以下命令可以完成添加， LOAD 的完整用法可以参考文档：

现在让我们看一看在 2000 年后出现的语言：

我们可以使用 TRUNCATE 清空整张表，或使用 DELETE 删除其中的某一项，或是使用 DROP 完全移除表（这里失误了，应该先用 TRUNCATE 再用 DROP 的，如果使用 TRUNCATE 应该是 TRUNCATE TABLE pls ）：

上面我们展示了数据库的一些入门用法，要完整地入门 MySQL 可以去读读官方文档或者一些教材。我们上面的操作都是在 root 用户下完成的，下面我们简单学习一下数据库的用户和权限管理。

我们可以使用 CREATE USER 来创建用户，比如：

CREATE USER 'yy1'@'localhost' IDENTIFIED BY 'Yy@191981'
CREATE USER 'yy2'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Yy@191982'
CREATE USER 'yy3'@'localhost' IDENTIFIED WITH sha256_password BY 'Yy@191983'

用户的格式是 'name'@'host_name' ， name 是用户名， host_name 是主机名。如果只指定用户名而不指定主机名，那么主机名默认为 % ，表示对所有主机开放权限。注意到上面指定密码的方式有两种，一是直接 IDENTIFIED BY ，二是添加 WITH plugin BY ，后者指定了要使用的授权插件，如果我们没有指定的话这里就默认使用 caching_sha2_password 。通过下面的命令我们就创建了一个用户：

接下来我们就可以使用如下命令为该用户授权了：

GRANT PRIVILEGE ON database.table TO 'username'@'host';

其中的 PRIVILEGE 就是权限， database.table 是授权的数据库和表， 'username'@'host' 就是用户和 IP。文档中列出了所有的权限，常见的有 SELECT CREATE ， ALTER ， DROP ， INSERT ， UPDATE ， DELETE 等等。下面是几条授权命令例子：

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'yy'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'yy'@'localhost' WITH GRANT OPTION;

这两条授权命令的结尾都有 WITH GRANT OPTION ，这表示被授权的用户有权力为其他用户授权。注意第二条命令的 ALL PRIVILEGES ，这是非常危险的，执行后被授权用户拥有了所有的权限。在执行授权命令后可以运行 FLUSH PRIVILEGES 来使其立即生效。不过根据文档来看这似乎不是必须的，不过用一下也没有什么坏处。

我们可以使用 SHOW GRANTS FOR 'username'@'host' 来观察某用户的当前权限：

如果我们想移除某用户的某些权限，可以使用：

REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';

如果我们想要删除某用户，我们可以使用 DROP USER 'username'@'localhost' 来删除：

同样，这也是非常基础的用户管理功能。除了这种方法外还有其他的方法可以管理用户，这里就不进一步展开了，已经够用了。

本来我还想着介绍一下 PostgreSQL 的安装和使用，不过这 MySQL 就够我喝一壶的，如果你需要安装 PostgreSQL，这个页面也许有用。