網路活動

安裝MariaDB的基本設定

一．前言：

安裝資料庫時，大都會考慮使用的語系編碼及是否使用遠端連線等問題。當程式使用中文（繁簡體）、日文等非英文語系的資料儲存至資料庫時，若沒留意語系編碼的設定，可能造成存入的資料變成亂碼，增加日後處理的困擾。而開放遠端連線時，則需考慮是否需要使用SSL加密，以避免資料傳遞過程被竊取的風險。本文將以MariaDB資料庫為例，簡單介紹語系編碼的設定及新增以SSL連線的使用者。

二．示範說明：

  1. 主機端使用系統及套件：
    FreeBSD： v10.2 (www.freebsd.org)
    MariaDB： v10.0.22 (mariadb.org)
  2. 用戶端使用的軟體：
    DB GUI工具： HeidiSQL (www.heidisql.com)
  3. 備註：
    本文皆以package的方式安裝

三．安裝MariaDB

  1. 安裝MariaDB（可選擇使用port或package的方式安裝）
    Port方式： # cd /usr/ports/databases/mariadb100-server/ && make install clean
    Package方式： # pkg install mariadb100-server


  2. 建立/usr/local/etc/my.cnf檔
    # cp /usr/local/share/mysql/my-huge.cnf /usr/local/etc/my.cnf
    備註：
    /usr/local/share/mysql目錄裡有其它的cnf檔，可以選擇與自己系統環境相似（例如：記憶體）的檔案使用。

  3. 修改my.cnf檔，採用UTF8編碼
    # vi /usr/local/etc/my.cnf
    [mysqld]
    character_set_server = utf8
    collation_server = utf8_unicode_ci
    skip-character-set-client-handshake

    [client]
    default_character_set = utf8

    [mysql]
    default_character_set = utf8

  4. 修改/etc/rc.conf檔，加入兩行設定
    # vi /etc/rc.conf
    mysql_enable="YES"
    mysql_optfile="/usr/local/etc/my.cnf"

  5. 啟動MariaDB，並執行mysql_secure_installation以增加其安全性
    # /usr/local/etc/rc.d/mysql-server start
    # /usr/local/bin/mysql_secure_installation
    備註：mysql_secure_installation會執行下列設置
    1. 為DB的root用戶設置密碼
    2. 刪除DB的匿名帳號
    3. 取消DB的root用戶遠端登錄
    4. 刪除test資料庫及對test資料庫的訪問權限
    5. 更新授權表使其修改生效

  6. 查詢資料庫預設編碼
    # mysql -u root -p -e "show variables like '%character%';"


    # mysql -u root -p -e "show variables like '%collation%';"

 

四．新增使用SSL遠端連線的用戶

  1. 檢查目前的SSL設定（預設是關閉的狀況）
    # mysql -u root -p -e "show variables like '%ssl%';"


  2. 新增資料庫所需要的憑證
    建立儲存憑證的目錄
    # mkdir -p /usr/local/etc/newcerts && cd /usr/local/etc/newcerts
    產生CA憑證（按實際狀況填寫資料）
    # openssl genrsa 2048 > ca-key.pem
    # openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem

    產生Server端的憑證（按實際狀況填寫資料）
    # openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
    # openssl rsa -in server-key.pem -out server-key.pem
    # openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

    產生Client端的憑證（按實際狀況填寫資料）
    # openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
    # openssl rsa -in client-key.pem -out client-key.pem
    # openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem


  3. 將憑證加入MariaDB的設定檔
    # vi /usr/local/etc/my.cnf
    [mysqld]
    ssl-ca=/usr/local/etc/newcerts/ca-cert.pem
    ssl-cert=/usr/local/etc/newcerts/server-cert.pem
    ssl-key=/usr/local/etc/newcerts/server-key.pem


  4. 重新啟動MariaDB，並驗證憑證是否OK？
    # /usr/local/etc/rc.d/mysql-server restart
    # openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem


  5. 再檢查一次目前的SSL設定
    # mysql -u root -p -e "show variables like '%ssl%';"


  6. 新增一個使用者，強制使用SSL連線
    # mysql -u root -p --execute="GRANT ALL on *.* to userid@client-ip IDENTIFIED BY 'passwd' REQUIRE SSL;"
    備註：
    userid：新增的使用者帳號
    client-ip：使用者從那一個Client端的IP連線
    passwd：使用者的密碼

  7. Client端的設定(FreeBSD或Linux系統)
    將三個檔案(ca-cert.pem、client-cert.pem、client-key.pem)拷貝至Client端，並修改Client端的my.cnf檔
    # vi /usr/local/etc/my.cnf
    [client]
    ssl-ca=/usr/local/etc/newcerts/ca-cert.pem
    ssl-cert=/usr/local/etc/newcerts/client-cert.pem
    ssl-key=/usr/local/etc/newcerts/client-key.pem

    測試連線是否正常？
    # mysql -u userid -ppasswd -h server-ip -e 'status;'
    備註：
    userid：使用者的帳號
    passwd：使用者的密碼
    server-ip：預登入的主機端IP


  8. Client端的設定（Windows系統，使用HeidiSQL軟體）
    將三個檔案(ca-cert.pem、client-cert.pem、client-key.pem)拷貝至Client端，並開啟HeidiSQL軟體，新建一個會話連線，在【設置】的部分，填入「server-ip」、「userid」、「passwd」


    在【高級】的部分，勾選SSL連線，並選擇對應的檔案存放位置後，按【打開】即可