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



安裝MySQL

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

sudo apt install mysql-server

ubuntu-server-mysql-php

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

sudo ss -tlnp | grep mysqld

ss指令可以顯示出Socket相關的資訊。-l參數可以只顯示正在監聽中的連線。若使用ss指令時都沒給任何參數的話,會忽略掉監聽中的連線。-t參數可以只顯示TCP連線。-n參數可以讓連接埠數字直接被輸出,而不是用一個名稱代替。-p參數可以顯示佔用連線的行程。

ubuntu-server-mysql-php

如上圖,如果有看到TCP有在監聽連接埠3306,就表示MySQL伺服器安裝成功了!

設定MySQL

MySQL伺服器的設定檔路徑為/etc/mysql/mysql.conf.d/mysqld.cnf。它預設的模樣長成這樣:

ubuntu-server-mysql-php

[mysqld]區塊內,比較重要的設定項目有:

  • bind-addressskip-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。應根據伺服器配置的記憶體大小來增加這個值。

位元組若有KMG單位表示要再乘上210、220、230

套用MySQL的新設定

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

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

mysqld --validate-config

ubuntu-server-mysql-php

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

sudo systemctl restart mysql

ubuntu-server-mysql-php

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

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

sudo mysql -u root

-u參數用來指定MySQL的使用者名稱。如果該使用者有啟用mysql_native_password,則要再加上-p參數輸入密碼來登入,此時也不需要用sudo提升指令的權限了。

ubuntu-server-mysql-php

登入後就可以開始輸入SQL敘述來執行。例如以下SQL敘述,可以顯示MySQL伺服器中的所有資料庫。

SHOW DATABASES;

ubuntu-server-mysql-php

以下SQL敘述,可以建立出一個名為dbname的資料庫。

CREATE DATABASE `dbname`;

ubuntu-server-mysql-php

以下SQL敘述,可以進入名為dbname的資料庫。

USE `dbname`;

ubuntu-server-mysql-php

以下SQL敘述,可以顯示目前資料庫中的所有表格。

SHOW TABLES;

ubuntu-server-mysql-php

以下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)
);

ubuntu-server-mysql-php

以下SQL敘述,可以顯示users表格是怎麼被建立的。

SHOW CREATE TABLE `users`;

ubuntu-server-mysql-php

以下SQL敘述,可以查詢users表格中的所有資料。

SELECT * FROM `users`;

ubuntu-server-mysql-php

以下SQL敘述,插入一個字串Bobusers表格中。

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

ubuntu-server-mysql-php

以下SQL敘述,可以建立出能從本地端(localhost)登入MySQL伺服器的使用者dbuser,密碼為4M{}^;dkn7Eg

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

ubuntu-server-mysql-php

以下SQL敘述,可以讓從本地端(localhost)登入MySQL伺服器的使用者dbuser,擁有dbname資料庫的所有權限。

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

ubuntu-server-mysql-php

如果想要結束執行mysql,可以輸入quit

ubuntu-server-mysql-php

在PHP程式中存取MySQL資料庫

如果您不知道如何在Ubuntu Server運行PHP程式,可以先參考這篇文章:

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

php -m

ubuntu-server-mysql-php

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

sudo apt install php-mysql

ubuntu-server-mysql-php

安裝完後,就可以透過php -m指令看到MySQL相關模組了。

ubuntu-server-mysql-php

在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

ubuntu-server-mysql-php

安裝過程需要設定phpMyAdmin,一開始會要求選擇網頁伺服器,因為我們的伺服器環境是Nginx+PHP(via FPM)+MySQL,所以這邊什麼都不選。直接按Enter鍵確認選擇。

ubuntu-server-mysql-php

最後會詢問是否要設定dbconfig-common,選擇「是」(Yes)吧!

ubuntu-server-mysql-php

dbconfig-common將需要建立一個新的MySQL使用者來操作運行phpMyAdmin資料庫管理工具時需要額外用到的資料表,預設的使用者名稱為phpmyadmin,所以接著要設定phpmyadmin這個MySQL使用者的密碼。

ubuntu-server-mysql-php

再輸入一次剛才輸入的phpmyadmin使用者的密碼。

ubuntu-server-mysql-php

新的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。如下圖:

ubuntu-server-mysql-php

加上allowdeny命令是為了要避免phpMyAdmin被有心人士攻擊。如果要在別台主機使用phpMyAdmin,可以加上更多的allow命令,或者乾脆改用HTTP基本認證(帳密認證)

加上fastcgi_read_timeoutfastcgi_send_timeout命令將PHP程式讀取和回應的間隔之逾時時間設定為1440秒,是因為phpMyAdmin需要執行SQL指令、以及匯入、匯出資料庫,可能會需要大量的運行時間。至於為什麼是1440秒,而不是更多或更少,是因為phpMyAdmin的預設登入後未進行任何操作的逾時時間就是1440秒。

這裡/phpmyadmin/location區塊之所以要用^~來修飾,是請求路徑的匹配順序的關係。如果是無修飾的話,優先權會低於~,從而導致.php結尾的請求被location ~ \.php$區塊接走。

套用Nginx的新設定

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

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

sudo nginx -t

ubuntu-server-nginx

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

sudo nginx -s reload

ubuntu-server-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';

ubuntu-server-mysql-php

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

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

ubuntu-server-mysql-php

如此一來這個MySQL使用者dbsuperuser,就是超級使用者了。用它登入phpMyAdmin,就可以操作任意功能。

ubuntu-server-mysql-php

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

ubuntu-server-mysql-php