MySQL 是一个开源的关系数据库管理系统(Relational Database Management System, RDBMS),适合用来架设网站。PHP 是一种适合用于网站开发的脚本式编程语言,弹性度高,可以直接被嵌入 HTML 文档中。
安装 MySQL
要在 Ubuntu Server 上安装 MySQL 的服务器,可以直接在终端机中运行以下指令:
MySQL 服务器预设使用的 TCP 连接端口为 3306,可以使用以下指令来查看 MySQL 服务器是否有确实安装成功。
ss 指令可以显示出 Socket 相关的信息。-l 参数可以只显示正在监听中的连接。若使用 ss 指令时都没给任何参数的话,会忽略掉监听中的连接。-t 参数可以只显示 TCP 连接。-n 参数可以让连接端口数字直接被输出,而不是用一个名称代替。-p 参数可以显示占用连接的进程。
如上图,如果有看到 TCP 有在监听连接端口 3306,就表示 MySQL 服务器安装成功了!
设置 MySQL
MySQL 服务器的设置档路径为 /etc/mysql/mysql.conf.d/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 服务器因为设置档错误而中断服务。
接着再运行以下指令来重新加载 MySQL 的设置档:
MySQL 客户端与 SQL 叙述(Statement)的使用方式
mysql 是 MySQL 的客户端指令工具,root 是 MySQL 的最高权限之用户名。在预设的情况下(用 auth_socket 来验证)透过以下指令可以使用 root 用户登录 MySQL 服务器:
-u 参数用来指定 MySQL 的用户名。如果该用户有激活 mysql_native_password,则要再加上 -p 参数输入密码来登录,此时也不需要用 sudo 提升指令的权限了。
登录后就可以开始输入 SQL 叙述来运行。例如以下 SQL 叙述,可以显示 MySQL 服务器中的所有数据库。
SHOW DATABASES;
以下 SQL 叙述,可以创建出一个名为 dbname 的数据库。
CREATE DATABASE `dbname`;
以下 SQL 叙述,可以进入名为 dbname 的数据库。
USE `dbname`;
以下 SQL 叙述,可以显示目前数据库中的所有表格。
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)
);
以下 SQL 叙述,可以显示 users 表格是怎么被创建的。
SHOW CREATE TABLE `users`;
以下 SQL 叙述,可以查找 users 表格中的所有数据。
SELECT * FROM `users`;
以下 SQL 叙述,插入一个字符串 Bob 至 users 表格中。
INSERT INTO `users` (`username`)
VALUES ('Bob');
以下 SQL 叙述,可以创建出能从本地端(localhost)登录 MySQL 服务器的用户 dbuser,密码为 4M{}^;dkn7Eg。
CREATE USER 'dbuser'@'localhost' IDENTIFIED BY '4M{}^;dkn7Eg';
以下 SQL 叙述,可以让从本地端(localhost)登录 MySQL 服务器的用户 dbuser,拥有 dbname 数据库的所有权限。
GRANT ALL ON `dbname`.* TO 'dbuser'@'localhost';
如果想要结束运行 mysql,可以输入 quit。
在 PHP 程序中访问 MySQL 数据库
如果您不知道如何在 Ubuntu Server 运行 PHP 程序,可以先参考这篇文章:
要在 PHP 程序中访问 MySQL 数据库,需要有 MySQL 相关模块的支持。利用以下指令可以查看目前 PHP 环境中有的模块:
如上图,并没有看到有 MySQL 相关模块的存在,所以要使用以下指令来安装:
安装完后,就可以透过 php -m 指令看到 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:
安装过程需要设置 phpMyAdmin,一开始会要求选择网页服务器,因为我们的服务器环境是 Nginx+PHP(via FPM)+MySQL,所以这边什么都不选。直接按 Enter 键确认选择。
最后会询问是否要设置 dbconfig-common,选择「是」(Yes)吧!
dbconfig-common 将需要创建一个新的 MySQL 用户来操作运行 phpMyAdmin 数据库管理工具时需要额外用到的数据表,预设的用户名为 phpmyadmin,所以接着要设置 phpmyadmin 这个 MySQL 用户的密码。
再输入一次刚才输入的 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。如下图:
加上 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 服务器因为设置档错误而中断服务。
接着再运行以下指令来重新加载 Nginx 的设置档:
创建超级用户(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';
以下 SQL 叙述,可以让从本地端(localhost)登录 MySQL 服务器的用户 dbsuperuser,拥有所有数据库的访问权限,而且可以把权限再分出去。
GRANT ALL PRIVILEGES ON *.* TO 'dbsuperuser'@'localhost' WITH GRANT OPTION;
如此一来这个 MySQL 用户 dbsuperuser,就是超级用户了。用它登录 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 开始用。
































