MariaDB 從 10.1.3版開始支援table encryption，官方建議使用mariadb 10.1.4以上版本。
MariaDB Encryption 以table為最小的加密單位，據官方文件說啟用加密效能約下降10%，目前支援的 storage engine有InnoDB、XtraDB 和Aria。
雖然說它是加密的，但原理似乎跟Linux的LUKS硬碟加密差不多，在啟動MariaDB的時候需要有加密的Key檔，沒有Key檔就不能啟動，跟LUKS一樣都是在防止硬碟遭竊取時被打開來看，這樣說起來不是用硬碟加密就好了?(誤
環境準備
首先要先準備MariaDB官方的Yum Repository File :
目前(20150903)官方預設是使用Mariadb 10.0版，因此我們要將baseurl修改一下:
[root@jyc-blog ~]# cat /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
安裝
用Yum安裝:
[root@jyc-blog ~]# yum install MariaDB-server MariaDB-client
啟動
跟Mariadb 5版不同，10版的服務名稱為mysql，且要用chkconfig設定開機啟動:
[root@jyc-blog ~]# systemctl start mysql.service
[root@jyc-blog ~]# chkconfig mysql on
[root@jyc-blog ~]# chkconfig mysql on
接下來做一些簡單的設定:
[root@jyc-blog ~]# mysql_secure_installation
產生Key檔
首先用openssl這個指令產生:
[root@jyc-blog ~]# openssl enc -aes-256-cbc -k YOURPASSWORD -P -md sha1
salt=A1A4F8EF1CC6D09E
key=5D56081334F9252ABD4D641AC640907317604A8B3CA92BC94FD6769C0F746628
iv =F39393DD5C735D2B0614356C413569D4
Key檔的格式為: <key-id>;<iv>;<key>
預設MariaDB會找編號為"1"的Key，因此我們將這把Key的id指定為"1"，並將Key檔儲存在/etc/my.cnf.d :
[root@jyc-blog ~]# cd /etc/my.cnf.d/
[root@jyc-blog my.cnf.d]# vim keys.txt
#add following
1;F39393DD5C735D2B0614356C413569D4;5D56081334F9252ABD4D641AC640907317604A8B3CA92BC94FD6769C0F746628
再來將keys.txt加密，後面MariaDB讀取的就是加密後的Key檔:
[root@jyc-blog my.cnf.d]# openssl enc -aes-256-cbc -md sha1 -k YOURPASSWORD -in keys.txt -out keys.enc
MariaDB支援兩種加密演算法: AES_CBC和AES_CTR，官方建議使用AES_CTR，但需要較新的openssl版本 。
修改mariadb設定檔
編輯/etc/my.cnf.d/server.conf :
[root@jyc-blog my.cnf.d]# vim server.conf
# line 12 add following
default-storage-engine = innodb
plugin_dir=/usr/lib64/mysql/plugin
plugin-load-add=file_key_management.so
file-key-management
file_key_management_encryption_algorithm=aes_cbc
file_key_management_filename = /etc/my.cnf.d/keys.enc
file_key_management_filekey = YOURPASSWORD
innodb-encrypt-log=ON
innodb-encryption-threads=4
innodb-encrypt-tables=FORCE
innodb-default-encryption-key-id=1
說明:
file_key_management.so : MariaDB Encryption的Plugin。
innodb-encrypt-log : 官方建議啟用，似乎比較安全。
innodb-encrypt-tables : [ON | OFF | FORCE]，若設定成FORCE，建立table時設定ENCRYPTED=NO會建立失敗。
設定完後儲存，再將MariaDB重新啟動:
[root@jyc-blog ~]# systemctl restart mysql.service
測試
檢查是否有載入file_key_management plugin:
[root@jyc-blog ~]# mysql -u root -p -e "SHOW PLUGINS SONAME 'file_key_management.so';"
Enter password:
+---------------------+--------+------------+------------------------+---------+
| Name | Status | Type | Library | License |
+---------------------+--------+------------+------------------------+---------+
| file_key_management | ACTIVE | ENCRYPTION | file_key_management.so | GPL |
+---------------------+--------+------------+------------------------+---------+
查看plugin的各個參數:
[root@jyc-blog ~]# mysql -u root -p -e "show variables like '%encrypt%';"
Enter password:
+------------------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------------------+-----------+
| aria_encrypt_tables | OFF |
| encrypt_tmp_disk_tables | OFF |
| encrypt_tmp_files | ON |
| file_key_management_encryption_algorithm | aes_cbc |
| innodb_default_encryption_key_id | 1 |
| innodb_encrypt_log | ON |
| innodb_encrypt_tables | FORCE |
| innodb_encryption_rotate_key_age | 1 |
| innodb_encryption_rotation_iops | 100 |
| innodb_encryption_threads | 4 |
+-------------------------------------------------------+------------+
建立資料庫和資料表:
[root@jyc-blog ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.1.6-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE DATABASE encrypted;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use encrypted;
Database changed
MariaDB [encrypted]> CREATE TABLE test (id INTEGER NOT NULL PRIMARY KEY, col1 VARCHAR(100)) ENGINE=Innodb ENCRYPTED=YES ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.02 sec)
#查看table是不是有加密:
MariaDB [encrypted]> SHOW TABLE STATUS ;
+-----------------------------------------------------------------+
| Create_options |
+-----------------------------------------------------------------+
| `ENCRYPTED`=YES `ENCRYPTION_KEY_ID`=1 |
+-----------------------------------------------------------------+
# 測試innodb-encrypt-tables=FORCE是否生效，將ENCRYPTED修改為NO:
MariaDB [encrypted]> ALTER test ENCRYPTED=NO;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'test ENCRYPTED=NO' at line 1
