使用Ubuntu Server架設MySQL伺服器，並於PHP程式中存取MySQL資料庫

MySQL是一個開源的關聯式資料庫管理系統(Relational Database Management System, RDBMS)，適合用來架設網站。PHP是一種適合用於網站開發的腳本式程式語言，彈性度高，可以直接被嵌入HTML文件中。

安裝MySQL

要在Ubuntu Server上安裝MySQL的伺服器，可以直接在終端機中執行以下指令：

sudo apt install mysql-server

MySQL伺服器預設使用的TCP連接埠為3306，可以使用以下指令來查看MySQL伺服器是否有確實安裝成功。

sudo ss -tlnp | grep mysqld

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單位表示要再乘上2¹⁰、2²⁰、2³⁰。

套用MySQL的新設定

改寫MySQL設定檔並存檔後，新設定並不會立即套用給正在運行中的MySQL伺服器。

要讓MySQL伺服器重新讀取設定檔，最好先用以下指令檢查設定檔是否無誤，避免MySQL伺服器因為設定檔錯誤而中斷服務。

mysqld --validate-config

接著再執行以下指令來重新載入MySQL的設定檔：

sudo systemctl restart mysql

MySQL客戶端與SQL敘述(Statement)的使用方式

mysql是MySQL的客戶端指令工具，root是MySQL的最高權限之使用者名稱。在預設的情況下(用auto_socket來驗證)透過以下指令可以使用root使用者登入MySQL伺服器：

sudo mysql -u root

-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程式，可以先參考這篇文章：

https://magiclen.org/ubuntu-server-nginx-php/

要在PHP程式中存取MySQL資料庫，需要有MySQL相關模組的支援。利用以下指令可以查看目前PHP環境中有的模組：

php -m

如上圖，並沒有看到有MySQL相關模組的存在，所以要使用以下指令來安裝：

sudo apt install 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：

sudo apt install 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伺服器因為設定檔錯誤而中斷服務。

sudo nginx -t

接著再執行以下指令來重新載入Nginx的設定檔：

sudo 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';

以下SQL敘述，可以讓從本地端(localhost)登入MySQL伺服器的使用者dbsuperuser，擁有所有資料庫的存取權限，而且可以把權限再分出去。

GRANT ALL 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開始用。