動手學 VPS 架站：使用 CentOS 7 + Nginx + PHP-FPM + MariaDB (9) - 資料庫

LNMP

簡單介紹資料庫的安裝及使用，還有管理 工具 的選擇。

5.1 SQLite

5.1.1 安裝及介紹

SQLite 目前已經是第 3 版，為了和第 2 版區分，當你安裝好之後，它的程式名稱為 sqlite3 。

# 安裝  sqlite  3
sudo yum install sqlite
# 查看看程式安裝在哪裡
which sqlite3
# 結果 /usr/bin/sqlite3

SQLite 首次在 2000 年 8 月發佈，使用 C 語言開發的跨平台資料庫，有別於 Client-Server 架構的資料庫引擎，SQLite 是檔案式的，可以很容易的整合進程式中，Firefox 瀏覽器就是使用 SQLite。

SQLite 的用途和 Client-Server 架構的資料庫引擎不同，什麼時候該選擇什麼架構的資料庫引擎，SQLite 官網上有給出 答案 ：

1. 資料是否和應用程式分離，透過網路來存取？

   如果是：選擇 Client-Server 架構的資料庫。

   這個情況是指，應用程式在使用者的電腦 (裝罝、App) ，這個應用程式透過網路存取遠端的資料庫中的資料；如果是網站，由於使用者是透過網頁伺服器來存取資料，因此資料庫和網頁伺服器可以視為一體，這時就可以使用 SQLite 當成 Server-side 的資料庫。

2. 需要大量同時 (並行、並發、Concurrency) 寫入資料？

   如果是：選擇 Client-Server 架構的資料庫。

   SQLite 只支援大量同時讀取；但是同時只能有一個寫入，多筆寫入時會排隊進行處理。

3. 大數據 (資料會不斷成長，多到一個磁碟裝不下的情況)？

   如果是：選擇 Client-Server 架構的資料庫。

   SQLite 最大能支援到 140 TB (terabytes)。

4. 除了以上的情況之外：選擇 SQLite。

5.1.2 操作指令簡介

完整的指令可以參考 官方說明文件 ，這裡只介紹幾個比較常用的指令。

開啟資料庫

# 資料庫名稱為 test.db
sqlite3 test.db
# 如果有該檔案就開啟，沒有則建立新資料庫

# 如果不輸入資料庫名稱，則會直接運行 SQLite 
sqlite3

指令格式

進入 SQLite 互動模式後，會出現 sqlite> 這樣的命令提示文字。SQLite 的指令是以點 . 開頭；如果要執行 SQL 指令，則必須以分號 ; 結尾，例如：

# 查看指令說明
sqlite> .help
# 執行 SQL 指令
sqlite> create table users (
   ...> id integer primary key autoincrement not null,
   ...> name varchar(20) not null
   ...> );
# 在看到分號之前，sqlite 不會執行該 SQL 指令，而是將 Enter 視為斷行

離開互動模式

sqlite> .exit
# 或
sqlite> .quit

查看資料庫、資料表及資料表綱要

# 列出資料庫名稱
sqlite> .database

# 列出所有資料表名稱
sqlite> .tables
# 結果 users
# 僅列出指定的資料表名稱
sqlite> .tables users

# 查看資料表綱要 (schema)
# 即顯示 "CREATE" 敍述的 SQL 指令
sqlite> .schema users
# 結果如下
CREATE TABLE users (
id integer primary key AUTOINCREMENT not null,
name varchar(20) not null
);
# 如果不指定資料表名稱，會顯示該資料庫中全部資料表的綱要

SQL 查詢

你可執行 SQL 語句來新增、更新、刪除或查詢資料。使用查詢語句時，可以調整輸出的格式。

# 先新增 2 筆資料
sqlite> insert into users (name) values ("Tom");
sqlite> insert into users (name) values ("Tony");

# 查詢語句
sqlite> select * from users;
# 結果如下
1,Tom
2,Tony

# 當資料欄位很多時可能不好閱讀，可以調整輸出格式
sqlite> .mode column
sqlite> select * from users;
1           Tom
2           Tony
# 還是不好閱讀，我們來加上標頭
sqlite> .header on
sqlite> select * from users;
id          name
----------  ----------
1           Tom
2           Tony
# 現在好讀多了

# 你可以用 .help 查詢 .mode 可用的輸出格式
# 你也可以查看目前的設定值
sqlite> .show
     echo: off
  explain: off
  headers: on
     mode: column
nullvalue: ""
   output: stdout
separator: ","
    stats: off
    width:
# headers 及 mode 是我們剛剛修改的兩個設定值

以上是使用內建的指令去執行一般事務，你也可以使用其他本機軟體來讀取資料庫。

使用 SQLite 的另一個好處是，當你要備份資料庫時，只要直接把檔案複製一份即可，雖然它也有提供指令：

sqlite> .backup test_backup.db

5.2 MariaDB

自從 MySQL 被賣給 Sun 昇陽公司，而昇陽公司又被 Oracle 甲骨文公司收購後，MySQL 的原作者就把 MySQL 分支出去另外建立了 MariaDB，目的是擔心有一天 MySQL 會變成不是開放源始碼的軟體。MariaDB 的目的是完全相容 MySQL，包括 API 和命令列，使之能輕鬆成為 MySQL 的替代品。

# 安裝
sudo yum install mariadb-server

# 啟動 MariaDB
sudo systemctl start mariadb
# 設定為開機時啟動
sudo systemctl enable mariadb
# 查看目前運作狀態
sudo systemctl status mariadb

MariaDB 提供了一個安全腳本，可以讓你快速設定 MariaDB 的安全性。

sudo mysql_secure_installation

首先它會要你設定 root 密碼，這個 root 是指 MariaDB 中的 root，接著的問題都以 Y/n 來輸入確認，可以一路按 Enter 來使用預設值。

完成後就可測試看看：

mysqladmin -u root -p version

連入資料庫：

mysql -u root -p
# 輸入 root 密碼

# 列出資料庫
show databases;
# 使用資料庫
use mysql;
# 列出資料表
show tables;

# 離開
\q

5.3 MySQL

如果你想安裝 MySQL 的話，沒辦法直接使用 yum 來安裝，請先到 Download MySQL Yum Repository ，找到如下項目：

Red Hat Enterprise Linux 7 / Oracle Linux 7 (Architecture Independent), RPM Package
(mysql80-community-release-el7-1.noarch.rpm) <-- 我們要這個

把 rpm 項目的名稱複製起來，附加在 https://dev.mysql.com/get/ 網址後面，然後下載：

cd /opt
sudo wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

# 確認 MD5 檢查碼是否相同
md5sum mysql80-community-release-el7-1.noarch.rpm

# 安裝 mysql rpm
sudo rpm -ivh mysql80-community-release-el7-1.noarch.rpm
# 要移除的話，請用以下指令，把找到的套件都移除
# rpm -qa | grep mysql
# 然後 sudo yum remove mysql-community-libs 及其他套件

# 安裝
sudo yum install mysql-server

# 啟動 MySQL
sudo systemctl start mysqld
# 設定為開機時啟動
sudo systemctl enable mysqld
# 查看目前運作狀態
sudo systemctl status mysqld

# 執行安全設定腳本
sudo mysql_secure_installation

# 測試
mysqladmin -u root -p version

你會發現 MariaDB 和 MySQL 的指令工具是一樣的。

註：當你使用這個方法安裝了 MySQL 之後，MariaDB 就會被取代，必須完整移除 MySQL 之後，才能安裝 MariaDB (可能要清除 yum 快取才能正常安裝)。

5.4 PostgreSQL

# Server
sudo yum install postgresql-server
# Client
sudo yum install postgresql

# 初始化資料庫
sudo postgresql-setup initdb

# 啟動
sudo systemctl start postgresql
# 設定為開機時啟動
sudo systemctl enable postgresql
# 查看目前運作狀態
sudo systemctl status postgresql

# 初始化時，建立了一個 postgres 使用者
# 我們切換成這個使用者來登入 postgresql 伺服器
sudo -i -u postgres
# 現在你用 whoami 查詢，你已經是 postgres 這個使用者
# 輸入以下指令來進入 Postgres 互動模式
psql
# 輸入 \q 可以離開
# \list 列出資料庫
# \dt 列出目前資料庫中的資料表

# 要離開 postgres 使用者
ctrl +

5.5 資料庫管理工具

有時候記不住那麼多指令，可以使用圖形界面的管理工具來輔助。以下我列出幾款免費並且可以在多個平台上使用的資料庫管理工具，你可以選擇適合自己使用的。

phpMyAdmin

phpMyAdmin 是一個用 PHP 開發的網頁式資料庫管理系統，必須在伺服器上安裝，可以管理 MySQL 及 MariaDB 的資料庫。

優點是安裝在伺服器上，其他使用者無須安裝軟體只要打開瀏覽器連上網址即可管理資料庫；缺點是網址一旦被得知，容易被攻擊，而且初次安裝時必須做一些設定。

SQLPro

SQLPro 資料庫管理程式分為多種版本，有 Studio、MSSQL、Postgres、MySQL 及 SQLite，Studio 綜合了以上資料庫外，還包含 MariaDB 及 Oracle 資料庫。

我是用 SQLPro for SQLite 來管理本地的 SQLite 資料庫，方便好用。

Sequel Pro

Sequel Pro 是個很好用的 MySQL 資料庫管理工具，可同時管理本地及遠端的 MySQL 資料庫。

如果要管理遠端的 MySQL，我推薦 Sequel Pro，簡單易用。

MySQL Workbench

MySQL Workbench 是官方提供的工具，除了管理功能還有許多設計資料庫架構的功能，功能較多也較複雜，如果只是要簡單的管理功能可能較不適合，比較適合要設計複雜的資料庫架構時使用。

SQLite Manager (Firefox)

SQLite Manager 是一個 Firefox 的外掛程式，用來管理 SQLite 資料庫很方便。

有一段時間我是用這個外掛，缺點就是必須先開 Firefox 才能使用，後來就改用 SQLPro for SQLite 了。