MySQL 是一个开源的关系数据库管理系统(Relational Database Management System, RDBMS),适合用来架设网站。PHP 是一种适合用于网站开发的脚本式编程语言,弹性度高,可以直接被嵌入 HTML 文档中。

安装 MySQL

要在 Ubuntu Server 上安装 MySQL 的服务器,可以直接在终端机中运行以下指令:

sudo apt install mysql-server

以 apt 安装 mysql-server

MySQL 服务器预设使用的 TCP 连接端口为 3306,可以使用以下指令来查看 MySQL 服务器是否有确实安装成功。

sudo ss -tlnp | grep mysqld

ss 指令可以显示出 Socket 相关的信息。-l 参数可以只显示正在监听中的连接。若使用 ss 指令时都没给任何参数的话,会忽略掉监听中的连接。-t 参数可以只显示 TCP 连接。-n 参数可以让连接端口数字直接被输出,而不是用一个名称代替。-p 参数可以显示占用连接的进程。

以 ss 确认 MySQL 监听连接端口 3306

如上图,如果有看到 TCP 有在监听连接端口 3306,就表示 MySQL 服务器安装成功了!

设置 MySQL

MySQL 服务器的设置档路径为 /etc/mysql/mysql.conf.d/mysqld.cnf。它预设的模样长成这样:

mysqld.cnf 的预设内容

在 [mysqld] 区块内,比较重要的设置项目有:

  • bind-address、skip-name-resolve:若要远程连入 MySQL 服务器,就会需要设置这两个项目。参考这篇文章。
  • max_connections:设置最大连接数。不设置的话为 151。最大值为 100000。
  • back_log:设置待处理连接队列(queue)的大小。不设置的话为 -1,表示要使用最大连接数作为此队列的大小。最大值为 65535。
  • default_storage_engine:设置预设的数据库引擎。不设置的话为 InnoDB(适用于大部分的案例)。
  • tmp_table_size:设置临时表(Temporary Table)的最大内存大小(字节),当临时表超过这个限制就会存到文件系统内。不设置的话为 16M。
  • innodb_buffer_pool_size:设置 InnoDB 的缓冲池大小(字节)。不设置的话为 128M。应根据服务器配置的内存大小来增加这个值。

字节若有 K、M、G 单位表示要再乘上 210、220、230。

套用 MySQL 的新设置

改写 MySQL 设置档并存盘后,新设置并不会立即套用给正在运行中的 MySQL 服务器。

要让 MySQL 服务器重新读取设置档,最好先用以下指令检查设置档是否无误,避免 MySQL 服务器因为设置档错误而中断服务。

mysqld --validate-config

以 mysqld --validate-config 检查设置档

接着再运行以下指令来重新加载 MySQL 的设置档:

sudo systemctl restart mysql

以 systemctl restart 重新启动 MySQL

MySQL 客户端与 SQL 叙述(Statement)的使用方式

mysql 是 MySQL 的客户端指令工具,root 是 MySQL 的最高权限之用户名。在预设的情况下(用 auth_socket 来验证)透过以下指令可以使用 root 用户登录 MySQL 服务器:

sudo mysql -u root

-u 参数用来指定 MySQL 的用户名。如果该用户有激活 mysql_native_password,则要再加上 -p 参数输入密码来登录,此时也不需要用 sudo 提升指令的权限了。

以 root 登录 MySQL

登录后就可以开始输入 SQL 叙述来运行。例如以下 SQL 叙述,可以显示 MySQL 服务器中的所有数据库。

SHOW DATABASES;

以 SHOW DATABASES 显示所有数据库

以下 SQL 叙述,可以创建出一个名为 dbname 的数据库。

CREATE DATABASE `dbname`;

以 CREATE DATABASE 创建数据库

以下 SQL 叙述,可以进入名为 dbname 的数据库。

USE `dbname`;

以 USE 进入 dbname 数据库

以下 SQL 叙述,可以显示目前数据库中的所有表格。

SHOW TABLES;

以 SHOW TABLES 显示所有表格

以下 SQL 叙述,可以创建名为 users 的表格,这个表格有两个字段。第一个字段是 userid,保存的数据类型是 tinyint SIGNED(有号 8-bit 整数),必须 NOT NULL(不能是空的),且作为每列(row)数据的 PRIMARY KEY(主键),会 AUTO_INCREMENT(自动递增)。第二个字段是 username,保存的数据类型是 varchar(50)(最大可以存 50 个字符)。

CREATE TABLE `users` (
    `userid` tinyint SIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `username` varchar(50)
);

以 CREATE TABLE 创建 users 表格

以下 SQL 叙述,可以显示 users 表格是怎么被创建的。

SHOW CREATE TABLE `users`;

以 SHOW CREATE TABLE 显示表格结构

以下 SQL 叙述,可以查找 users 表格中的所有数据。

SELECT * FROM `users`;

以 SELECT 查找 users 表格

以下 SQL 叙述,插入一个字符串 Bob 至 users 表格中。

INSERT INTO `users` (`username`)
    VALUES ('Bob');

以 INSERT 插入 Bob

以下 SQL 叙述,可以创建出能从本地端(localhost)登录 MySQL 服务器的用户 dbuser,密码为 4M{}^;dkn7Eg。

CREATE USER 'dbuser'@'localhost' IDENTIFIED BY '4M{}^;dkn7Eg';

以 CREATE USER 创建 dbuser 用户

以下 SQL 叙述,可以让从本地端(localhost)登录 MySQL 服务器的用户 dbuser,拥有 dbname 数据库的所有权限。

GRANT ALL ON `dbname`.* TO 'dbuser'@'localhost';

以 GRANT 授予 dbuser 权限

如果想要结束运行 mysql,可以输入 quit。

以 quit 结束 mysql

在 PHP 程序中访问 MySQL 数据库

如果您不知道如何在 Ubuntu Server 运行 PHP 程序,可以先参考这篇文章:

要在 PHP 程序中访问 MySQL 数据库,需要有 MySQL 相关模块的支持。利用以下指令可以查看目前 PHP 环境中有的模块:

php -m

以 php -m 查看 PHP 模块

如上图,并没有看到有 MySQL 相关模块的存在,所以要使用以下指令来安装:

sudo apt install php-mysql

以 apt 安装 php-mysql

安装完后,就可以透过 php -m 指令看到 MySQL 相关模块了。

安装后出现 MySQL 相关模块

在 PHP 程序中利用 PDO (PHP Data Objects),就可以安全地访问 MySQL 数据库。用法如下:

<?php
// -----DB config-----

$dbms = 'mysql';
$dbhost = 'localhost';
$dbname = 'dbname';
$dbuser = 'dbuser';
$dbpwd = '4M{}^;dkn7Eg';

// -------------------

$dsn = "$dbms:host=$dbhost;dbname=$dbname";

$db = new PDO($dsn, $dbuser, $dbpwd);

// -------------------

header('Content-Type: text/plain');

$userid = isset($_GET['userid']) ? (int)$_GET['userid'] : 1;

$statement = $db->prepare('SELECT * FROM `users` WHERE `userid` = :userid');

$statement->execute(array(
    ':userid' => $userid
));

$row = $statement->fetch(PDO::FETCH_ASSOC);

if (is_array($row)) {
    echo $row['username'];
} else {
    echo 'Empty result!';
}
?>

透过 PDO 提供的 prepare 方法来产生 SQL 叙述对象(Statement Object),再用其提供的 execute 方法来运行 SQL 叙述是很重要的,可以避免 SQL 注入(SQL injection)攻击。

SQL 叙述对象的 fetch 方法可以用来取得前次运行 execute 方法后的结果,如果没有结果可以取得会回传 false。传入 PDO::FETCH_ASSOC 表示要将结果转成数组,用字段名称来索引;PDO::FETCH_OBJ 表示要将结果转成对象。不传入的话预设是 PDO::FETCH_BOTH,表示要将结果转成数组,用字段名称和字段顺序来索引。

如果要取得多列结果,就要改用 fetchAll 方法,如下:

<?php
// -----DB config-----

$dbms = 'mysql';
$dbhost = 'localhost';
$dbname = 'dbname';
$dbuser = 'dbuser';
$dbpwd = '4M{}^;dkn7Eg';

// -------------------

$dsn = "$dbms:host=$dbhost;dbname=$dbname";

$db = new PDO($dsn, $dbuser, $dbpwd);

// -------------------

header('Content-Type: text/plain');

$username = isset($_GET['username']) ? $_GET['username'] : 'Bob';

$statement = $db->prepare('SELECT * FROM `users` WHERE `username` = :username');

$statement->execute(array(
    ':username' => $username
));

$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

if (!empty($rows)) {
    foreach ($rows as $row) {
        echo $row['username'], "\n";
    }
} else {
    echo 'Empty result!';
}
?>

phpMyAdmin

phpMyAdmin 是使用 PHP+MySQL 环境时经常会使用的管理后台,不过并不是必要项目。如果您不需要,可以略过这部份的内容。

安装 phpMyAdmin

在终端机运行以下指令,来安装 phpMyAdmin:

sudo apt install phpmyadmin

以 apt 安装 phpmyadmin

安装过程需要设置 phpMyAdmin,一开始会要求选择网页服务器,因为我们的服务器环境是 Nginx+PHP(via FPM)+MySQL,所以这边什么都不选。直接按 Enter 键确认选择。

不选择任何网页服务器

最后会询问是否要设置 dbconfig-common,选择「是」(Yes)吧!

询问是否以 dbconfig-common 设置数据库

dbconfig-common 将需要创建一个新的 MySQL 用户来操作运行 phpMyAdmin 数据库管理工具时需要额外用到的数据表,预设的用户名为 phpmyadmin,所以接着要设置 phpmyadmin 这个 MySQL 用户的密码。

设置 phpmyadmin 用户的密码

再输入一次刚才输入的 phpmyadmin 用户的密码。

再次输入 phpmyadmin 用户的密码

新的 MySQL 用户 phpmyadmin 创建完成后,phpMyAdmin 就算是安装完毕了!

设置 Nginx

phpMyAdmin 的 PHP 程序放置在 /usr/share/phpmyadmin 目录中,我们可以利用 Nginx 设置档的 root 命令来制作一个 phpMyAdmin 的 location 区块。设置方式如下:

server {
    index index.html index.php;

    set $php_uds unix:/run/php/php7.4-fpm.sock;

    location ~ \.php$ {
        fastcgi_pass $php_uds;
        include fastcgi.conf;
    }

    location = /phpmyadmin {
        return 301 /phpmyadmin/;
    }

    location ^~ /phpmyadmin/ {
        root /usr/share;
       
        allow 127.0.0.1;
        deny all;

        fastcgi_read_timeout 1440s;
        fastcgi_send_timeout 1440s;
       
        if ($uri ~ \.php$) {
            fastcgi_pass $php_uds;
        }   
       
        include fastcgi.conf;
    }
}

如此一来就可以在同一台主机透过 HTTP 协定发送路径前缀为 /phpmyadmin/ 的请求来打开 phpMyAdmin。如下图:

phpMyAdmin 的登录页面

加上 allow 和 deny 命令是为了要避免 phpMyAdmin 被有心人士攻击。如果要在别台主机使用 phpMyAdmin,可以加上更多的 allow 命令,或者干脆改用 HTTP 基本认证(帐密认证)。

加上 fastcgi_read_timeout 和 fastcgi_send_timeout 命令将 PHP 程序读取和回应的间隔之逾时时间设置为 1440 秒,是因为 phpMyAdmin 需要运行 SQL 指令、以及导入、导出数据库,可能会需要大量的运行时间。至于为什么是 1440 秒,而不是更多或更少,是因为 phpMyAdmin 的预设登录后未进行任何操作的逾时时间就是 1440 秒。

这里 /phpmyadmin/ 的 location 区块之所以要用 ^~ 来修饰,是请求路径的匹配顺序的关系。如果是无修饰的话,优先权会低于 ~,从而导致 .php 结尾的请求被 location ~ \.php$ 区块接走。

套用 Nginx 的新设置

改写 Nginx 设置档并存盘后,新设置并不会立即套用给正在运行中的 Nginx 服务器。

要让 Nginx 服务器重新读取设置档,最好先用以下指令检查设置档是否无误,避免 Nginx 服务器因为设置档错误而中断服务。

sudo nginx -t

以 nginx -t 检查设置档

接着再运行以下指令来重新加载 Nginx 的设置档:

sudo nginx -s reload

以 nginx -s reload 重新加载设置

创建超级用户(Superuser)以便在 phpMyAdmin 中进行管理

虽然我们可以将 MySQL 的 root 用户改为使用 mysql_native_password 透过密码来登录,不过这样会连带影响使用 mysql 客户端指令工具的操作方式。所以比较好的作法还是再另外添加一个超级用户。

以下 SQL 叙述,可以创建出能从本地端(localhost)登录 MySQL 服务器的用户 dbsuperuser,密码为 c[Fzeq_U2T?6,]N5。

CREATE USER 'dbsuperuser'@'localhost' IDENTIFIED BY 'c[Fzeq_U2T?6,]N5';

以 CREATE USER 创建 dbsuperuser

以下 SQL 叙述,可以让从本地端(localhost)登录 MySQL 服务器的用户 dbsuperuser,拥有所有数据库的访问权限,而且可以把权限再分出去。

GRANT ALL PRIVILEGES ON *.* TO 'dbsuperuser'@'localhost' WITH GRANT OPTION;

以 GRANT 授予 dbsuperuser 所有权限

如此一来这个 MySQL 用户 dbsuperuser,就是超级用户了。用它登录 phpMyAdmin,就可以操作任意功能。

以超级用户登录的 phpMyAdmin

phpMyAdmin 和 MySQL 服务器不在同一个操作系统上

phpMyAdmin 预设是使用 Unix Domain Socket(简称 UDS,或称 IPC Socket)链接到本地端的 MySQL 服务器。所以对 MySQL 来说,透过 phpMyAdmin 连到 MySQL 的用户都是从 localhost 登录的。

若要让 phpMyAdmin 连到其它操作系统或是其它主机上的 MySQL 服务器,就要去修改 phpMyAdmin 的 /etc/phpmyadmin/config-db.php 设置档。

如果想连接多个 MySQL 服务器,就要去修改 phpMyAdmin 的 /etc/phpmyadmin/config.inc.php 设置档。$cfg['Servers'][$i] 用来保存一个 MySQL 服务器的连接设置,$i 必须从 1 开始用。

config.inc.php 中的 MySQL 服务器连接设置