![技術語り-Tech Blog[JP]](https://blogger.googleusercontent.com/img/a/AVvXsEiYsL2e14T1O3H3pP9BUj4BxNQQDgWUK-pNhtt6X8syLNp1zvyRWOG_UEv_Gcanv4Nix1_-8MCZL02odSwi69Cbmp6WJeoNDX8f7GQSPH25NUW3dtNTkcGdKhA0sRemc7CD46aj4pMhd8WzzKViOuxBpGmZ5DaFru2sTTv9Jie9aXa98EppzJj1P3lUvyY=s175)
MariaDBの暗号化
- Ubuntu...test
- CentOS...商用
インフラ周りの違いをきっちり理解しておかなと思った。
ansibleサーバのバージョンアップ
- Table and tablespace encryption on MariaDB 10.1
- Installing and Using MariaDB via Docker <--これ違う
- File Key Management Encryption Plugin
- MariaDB tables encryption.
MariaDB [(none)]> install plugin file_key_management soname 'file_key_management.so';
Query OK, 0 rows affected (0.010 sec)
MariaDB [(none)]> CREATE TABLE tde_test2 (i int) ENGINE=InnoDB ENCRYPTED=YES;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> use tde_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [tde_test]> CREATE TABLE tde_test2 (i int) ENGINE=InnoDB ENCRYPTED=YES;
Query OK, 0 rows affected (0.022 sec)
MariaDB [tde_test]>
別ブログを見る。
https://engineers.weddingpark.co.jp/mysql-encryption/
ここでは、暗号化前にDBの中身見て、その後暗号化してもう一度確認している。
暗号化前のDB見る方法；strings /var/lib/mysql/(DB名)/テーブル名.ibd
※stringコマンドは以下でインストールした。(# apt-get install binutils)
(例)
root@66b3dc6ce2a5:/# strings /var/lib/mysql/sample_db/sample_tbl.ibd
infimum
supremum
test
angouka
hogehoge
■■■
■■■
[確認]
非暗号
root@e447a091e8ce:/# strings /var/lib/mysql/tde_test/
db.opt tde_test2.frm tde_test2.ibd test.frm test.ibd
root@e447a091e8ce:/# strings /var/lib/mysql/tde_test/test.ibd
infimum
supremum
test
test2
root@e447a091e8ce:/#
暗号
root@e447a091e8ce:/# strings /var/lib/mysql/tde_test/tde_test2.ibd
hv"}R#
l/6dC
S[DxF
(略)
O_A+{SQ
7FRf
L'Ts
eS)q
root@e447a091e8ce:/#
確認完了
■■■
■■■
[db落とした時に見えないか？]
(docker落として再起動)
MariaDB [tde_test]> y-ononoMacBook-Prodocker-compose up -d
Creating network "uims-portal_default" with the default driver
Creating uims-portal-app ... done
Creating uims-portal-db ... done
Creating uims-portal-api ... done
Creating uims-portal-web ... done
y-ononoMacBook-Pro:uims-portal y_ono$ docker-compose exec db bash
root@32ac52a3cddd:/# strings /var/lib/mysql/
(暗号化ファイル確認)
root@32ac52a3cddd:/# strings /var/lib/mysql/tde_test/tde_test2.ibd
bash: strings: command not found
root@32ac52a3cddd:/# apt-get install strings
Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package strings
->strings パッケージがない
- apt-get update
- apt-get install strings
---> 設定ファイルの初期インストールapt-getに、入れる必要あり？
Processing triggers for libc-bin (2.27-3ubuntu1) ...
(確認)
root@32ac52a3cddd:/# strings /var/lib/mysql/tde_test/tde_test2.ibd
hv"}R#
l/6dC
S[DxF
(略)
7FRf
L'Ts
eS)q
root@32ac52a3cddd:/#
ok
■■■
■■■
MariaDB [tde_test]> show tables;
+--------------------+
| Tables_in_tde_test |
+--------------------+
| tde_test2 |
| test |
+--------------------+
2 rows in set (0.001 sec)
MariaDB [tde_test]> create table not_enc_table (i int) engine=InnoDB;
Query OK, 0 rows affected (0.014 sec)
MariaDB [tde_test]> show tables;
+--------------------+
| Tables_in_tde_test |
+--------------------+
| not_enc_table |
| tde_test2 |
| test |
+--------------------+
3 rows in set (0.001 sec)
MariaDB [tde_test]> alter table not_enc_table encrypted=yes;
ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID'
MariaDB [tde_test]>
-->動かない
MariaDB [tde_test]> ALTER TABLE not_enc_table2 encrypted=yes;
ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID'
MariaDB [tde_test]> show plugins;
+-------------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+-------------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| wsrep | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CLIENT_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INDEX_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| TABLE_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| USER_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| SQL_SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_MUTEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_SEMAPHORE_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES_ENCRYPTION | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| INNODB_TABLESPACES_SCRUBBING | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| user_variables | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+-------------------------------+----------+--------------------+---------+---------+
53 rows in set (0.007 sec)
MariaDB [tde_test]> install plugin file_key_management soname 'file_key_management.so';
ERROR 15 (HY000): file-key-management-filename is not set
->db落として再起動したら、pluginが消えたから、filemanagerも消えたと解釈して、
プラグインを入れても入らない。。。
create tableしてできたんだから、まぁalter tableもできるだろう。
再現手順としては、
なので、次回は自動的にプラグインやinstall機構が走るように、my.cnfやdocker-compose.ymlを変更
■■■
■■■
