NoteMyLife: SQL

顯示具有 SQL 標籤的文章。顯示所有文章

2015年9月3日星期四

CentOS7 - MariaDB 10 資料庫加密

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 :

https://downloads.mariadb.org/mariadb/repositories/#mirror=nus

目前(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

安裝

用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 ~]# 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:

+---------------------+--------+------------+------------------------+---------+

+---------------------+--------+------------+------------------------+---------+

+---------------------+--------+------------+------------------------+---------+

查看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

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

2011年5月6日星期五

★設定MySQL密碼 
       #mysqladmin　password　"newpassword" 
★進入資料庫 
      1.使用PHPMyAdmin登入修改。 
      2.使用Linux文字界面登入修改。 
                 #mysql　-u 　root　-p 
                 (-u：後面接帳號，-p：輸入密碼) 
★SHOW指令 
    ．顯示資料表 
         ＞　SHOW　TABLES； 
         ＞　SHOW　TABLES　FROM　database_name； 
    ．顯示資料欄位 
         ＞　SHOW　COLUMNS　FROM　table_name； 
    ．顯示InnoDB的狀態 
         ＞　SHOW　ENGINE　INNODB　STATUS； 
 
 
 
 
 
 
 
 
 
一、資料定義語言(Data Definition Language,DDL) 
    ．DDL的主要功能 
           1.新增、刪除、修改資料庫和資料表。 
           2.主要語法 
                 CREATE：建立資料庫 
                 ALTER：修改資料庫 
                 DROP：刪除資料庫 
    ．CREATE 
            (1)建立資料庫 
                 ＞　CREATE　DATABASE　database_name； 
            (2)建立資料表 
                 ＞　CREATE　TABLE　table_name(column1_name　column1_type　column1_option,　column2_name　column2_type　column2_option,　......,　index(column_name))； 
<table align="center" border="1" style="width: 357px;"><tbody>
<tr> <td align="center" colspan="4" valign="middle">Column_Type</td> </tr>
<tr> <td align="center" valign="middle" width="110">數字</td> <td align="center" valign="middle" width="47">字元</td> <td align="center" valign="middle" width="53">時間</td> <td align="center" valign="middle" width="119">其它</td> </tr>
<tr> <td align="center" valign="middle">int (4 bytes)</td> <td align="center" valign="middle">char</td> <td align="center" valign="middle">date</td> <td align="center" valign="middle">Boolean</td> </tr>
<tr> <td align="center" valign="middle">smalling (2 bytes)</td> <td align="center" valign="middle">varchar</td> <td align="center" valign="middle">datetime</td> <td align="center" valign="middle"></td> </tr>
<tr> <td align="center" valign="middle">int8 (8 bytes)</td> <td align="center" valign="middle">text</td> <td align="center" valign="middle"></td> <td align="center" valign="middle"></td> </tr>
<tr> <td align="center" valign="middle">float</td> <td align="center" valign="middle"></td> <td align="center" valign="middle"></td> <td align="center" valign="middle"></td> </tr>
<tr> <td align="center" valign="middle">small float</td> <td align="center" valign="middle"></td> <td align="center" valign="middle"></td> <td align="center" valign="middle"></td> </tr>
<tr> <td align="center" valign="middle">money</td> <td align="center" valign="middle"></td> <td align="center" valign="middle"></td> <td align="center" valign="middle"></td> </tr>
</tbody></table>
 
<center>
註:資料型態的說明可到<a href="http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.ddi.doc/ddi57.htm">此網站</a>查詢</center>
 
<table align="center" border="1"><tbody>
<tr> <td align="center" colspan="2" valign="middle">Column_Option</td> </tr>
<tr> <td width="214">NULL</td> <td width="301">欄位可以沒有資料。</td> </tr>
<tr> <td>NOT　NULL</td> <td>欄位一定要有資料。</td> </tr>
<tr> <td>DEFAULT　'default'</td> <td>如果欄位沒資料就會使用default。</td> </tr>
<tr> <td align="center" colspan="2" valign="middle">Index</td> </tr>
<tr> <td>aoto_increment</td> <td>自動將編號加一。</td> </tr>
<tr> <td>PRIMARY　KEY(column_name)</td> <td>主索引鍵，每個資料表只能有一個主索引鍵。通常配合aoto_increment一起使用。</td> </tr>
<tr> <td>KEY(column_name)</td> <td>索引鍵，每個資料表可以有多個索引鍵。</td> </tr>
<tr> <td>INDEX(column_name)</td> <td>與KEY的功用相同。</td> </tr>
<tr> <td>UNIQUE</td> <td>唯一值，同一欄位的其他資料不可以重複。</td> </tr>
</tbody> </table>
 
 
 
    ．ALTER 
            (1)修改資料表名稱 
                 ＞　ALTER　TABLE　old_table_name　RENAME　new_table_name； 
            (2)修改欄位名稱 
                 ＞　ALTER　TABLE　table_name　CHANGE　old_column_name　new_column_name　column_type； 
            (3)修改欄位型態 
                 ＞　ALTER　TABLE　table_name　MODIFY　column_name　column_type　column2_option； 
            (4)新增欄位、索引 
                 ＞　ALTER　TABLE　table_name　ADD　new_column_name　new_column_type； 
                 ＞　ALTER　TABLE　table_name　ADD　index(column_name); 
           (5)刪除欄位、索引 
                 ＞　ALTER　TABLE　table_name　DROP　column_name； 
                 ＞　ALTER　TABLE　table_name　DROP　index(column_name)； 
           (6)新增欄位時調整欄位順序 
                 ＞　ALTER　TABLE　table_name　ADD　new_column_name　column_type　AFTER　old_column_name； 
                 ＞　ALTER　TABLE　table_name　ADD　new_column_name　column_type　FIRST； 
           (7)設置Foreign Key，以確保資料的一致性與完整性 
                 ＞　ALTER　TABLE　database_name　ENGINE=INNODB； 
            (8) 新增關聯限制 (CREATE和ALTER皆可使用) 
                 ＞　ALTER　TABLE　database_name　ADD　FOREIGN　KEY(column_name)　REFERENCES　database_name(column_name)　trigger_condition　action； 
<table align="center" border="1"><tbody>
<tr> <td align="center" colspan="2" valign="middle">Trigger_Condition(觸發條件)</td> </tr>
<tr> <td width="172">ON　DELETE</td> <td width="107">刪除資料時</td> </tr>
<tr> <td>ON　UPDATE</td> <td>更新資料時</td> </tr>
</tbody> </table>
<table align="center" border="1"><tbody>
<tr> <td align="center" colspan="2" valign="middle">Action(動作)</td> </tr>
<tr> <td width="171">CASCADE</td> <td width="305">將所有關聯的欄位進行刪除或修改</td> </tr>
<tr> <td>NO ACTION</td> <td>當欄位有其它的關聯時，會禁止父資料表的刪除或修改</td> </tr>
<tr> <td>RESTRICT</td> <td>與NO　ACTION的功用相同</td> </tr>
<tr> <td>SET NULL</td> <td>將所有關聯的欄位設定成NULL</td> </tr>
</tbody> </table>
            (8) 刪除關聯限制 
                 ＞　ALTER　TABLE　database_name　DROP　FOREIGN　KEY`database_name`； 
    ．DROP 
            (1)刪除資料庫 
                 ＞　DROP　database_name； 
            (1)刪除資料表 
                 ＞　DROP　table_name； 
 
 
 
二、資料操縱語言(Data Manipulation Language,DCL) 
 
 
 
 
 
 
 
 
 
     ．DDM的主要功能 
           1.操作資料庫資料內容的語法。 
           2.主要語法 
                  INSERT：新增資料到資料表中。 
                  UPDATE：修改資料表的資料。 
                  DELETE：刪除資料表的資料。 
                  SELECT：選取資料表的資料。 
    ．INSERT 
            (1)新增資料 
                 ＞　INSERT　INTO　database_name(column1_name,　column2_name,　...)　VALUES ('column1_data',　'column2_data',　'...')； 
            (2)資料庫閒置時才新增資料 
                 ＞　INSERT　LOW_PRIORITY　database_name(column1_name,　column2_name,　...)　VALUES ('column1_data',　'column2_data',　'...')； 
    ．SELECT 
            (1)查詢資料表的所有欄位資料 
                 ＞　SELECT　*　FROM　table_name； 
            (2)指定要查詢的欄位資料 
                 ＞　SELECT　column1_name,column2_name,....　FROM　table_name； 
            (3)指定篩選條件查詢欄位資料 
                 ＞　SELECT　*　FROM　table_name　WHERE　column_name　filter； 
<table align="center" border="1"><tbody>
<tr> <td align="center" colspan="2" valign="middle">Filter</td> </tr>
<tr> <td width="200">column_name　=　'條件'</td> <td width="248">EX：book_no　=　'B001'</td> </tr>
<tr> <td>column_name　LIKE　'條件'</td> <td>EX：book_no　=　'1%'</td> </tr>
<tr> <td>條件1　AND　條件2</td> <td>兩個條件都要符合才會顯示</td> </tr>
<tr> <td>條件1　OR　條件2</td> <td>符合兩個條件的其中一個就會顯示</td> </tr>
<tr> <td align="center" colspan="2" valign="middle">其它</td> </tr>
<tr> <td align="center" valign="middle">%</td> <td>萬用字元。EX：'B%'</td> </tr>
<tr> <td align="center" valign="middle">_</td> <td>一個字元。EX：'B00_'</td> </tr>
</tbody> </table>
    ．UPDATE 
            (1)更新欄位資料 
                 ＞　UPDATE　table_name　SET　column_name='data'　WHERE　column_name = '條件'； 
    ．DELETE 
            (1)刪除資料 
                 ＞　DELETE　FROM　table_name　WHERE　　column_name = '條件'； 
            (2)刪除資料後釋放記憶空間 
                 ＞　OPTIMIZE　TABLE　table_name；

訂閱：文章 (Atom)

NoteMyLife

搜尋此網誌

2015年9月3日星期四

CentOS7 - MariaDB 10 資料庫加密

環境準備

安裝

啟動

產生Key檔

修改mariadb設定檔

測試

2011年5月6日星期五

SQL基礎語法

About Me

標籤

網誌存檔

搜尋此網誌

2015年9月3日 星期四

CentOS7 - MariaDB 10 資料庫加密

環境準備

安裝

啟動

產生Key檔

修改mariadb設定檔

測試

2011年5月6日 星期五

SQL基礎語法

About Me

標籤

網誌存檔

2015年9月3日星期四

2011年5月6日星期五