こちらでは、パーティション単位でデータを暗号化する例を記載しています。
もし、開発環境などに重要な情報を持っていく場合に、暗号化した方がいいケースも有ると思いますので、そんな時にパーティションに分けて並列で暗号化する事で暗号化の時間を短縮する事も可能です。
Sample Table :
mysql> show create table t_partition_poc\G
*************************** 1. row ***************************
Table: t_partition_poc
Create Table: CREATE TABLE `t_partition_poc` (
`sid` int unsigned NOT NULL,
`ClientId` int unsigned NOT NULL DEFAULT '0',
`ContentId` int unsigned NOT NULL DEFAULT '0',
`ipAddress` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`os` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'No Information',
`status` tinyint unsigned NOT NULL DEFAULT '1',
`clickDate` date NOT NULL,
`clickTime` time NOT NULL,
PRIMARY KEY (`sid`,`clickDate`),
KEY `client` (`clickDate`,`ClientId`,`ContentId`),
KEY `idx_ContentId` (`ContentId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
/*!50100 PARTITION BY RANGE (to_days(`clickDate`))
(PARTITION p202302 VALUES LESS THAN (738945) ENGINE = InnoDB,
PARTITION p202303 VALUES LESS THAN (738976) ENGINE = InnoDB,
PARTITION p202304 VALUES LESS THAN (739006) ENGINE = InnoDB,
PARTITION p202305 VALUES LESS THAN (739037) ENGINE = InnoDB,
PARTITION p202306 VALUES LESS THAN (739067) ENGINE = InnoDB,
PARTITION p202307 VALUES LESS THAN (739098) ENGINE = InnoDB,
PARTITION p202308 VALUES LESS THAN (739129) ENGINE = InnoDB,
PARTITION p202309 VALUES LESS THAN (739159) ENGINE = InnoDB,
PARTITION p202310 VALUES LESS THAN (739190) ENGINE = InnoDB,
PARTITION p202311 VALUES LESS THAN (739220) ENGINE = InnoDB,
PARTITION p202312 VALUES LESS THAN (739251) ENGINE = InnoDB,
PARTITION p202401 VALUES LESS THAN (739282) ENGINE = InnoDB,
PARTITION p202402 VALUES LESS THAN (739311) ENGINE = InnoDB,
PARTITION p202403 VALUES LESS THAN (739342) ENGINE = InnoDB,
PARTITION p202404 VALUES LESS THAN (739372) ENGINE = InnoDB,
PARTITION p202405 VALUES LESS THAN (739403) ENGINE = InnoDB,
PARTITION p202406 VALUES LESS THAN (739433) ENGINE = InnoDB,
PARTITION p202407 VALUES LESS THAN (739464) ENGINE = InnoDB,
PARTITION p202408 VALUES LESS THAN (739495) ENGINE = InnoDB,
PARTITION p202409 VALUES LESS THAN (739525) ENGINE = InnoDB,
PARTITION p202410 VALUES LESS THAN (739556) ENGINE = InnoDB,
PARTITION p202411 VALUES LESS THAN (739586) ENGINE = InnoDB,
PARTITION p202412 VALUES LESS THAN (739617) ENGINE = InnoDB,
PARTITION p202501 VALUES LESS THAN (739648) ENGINE = InnoDB,
PARTITION p202502 VALUES LESS THAN (739676) ENGINE = InnoDB,
PARTITION p202503 VALUES LESS THAN (739707) ENGINE = InnoDB,
PARTITION p202504 VALUES LESS THAN (739737) ENGINE = InnoDB,
PARTITION p202505 VALUES LESS THAN (739768) ENGINE = InnoDB,
PARTITION p202506 VALUES LESS THAN (739798) ENGINE = InnoDB,
PARTITION p202507 VALUES LESS THAN (739829) ENGINE = InnoDB,
PARTITION p202508 VALUES LESS THAN (739860) ENGINE = InnoDB,
PARTITION p202509 VALUES LESS THAN (739890) ENGINE = InnoDB,
PARTITION p202510 VALUES LESS THAN (739921) ENGINE = InnoDB,
PARTITION p202511 VALUES LESS THAN (739951) ENGINE = InnoDB,
PARTITION p202512 VALUES LESS THAN (739982) ENGINE = InnoDB,
PARTITION p202601 VALUES LESS THAN (740013) ENGINE = InnoDB,
PARTITION p202602 VALUES LESS THAN (740041) ENGINE = InnoDB,
PARTITION p202603 VALUES LESS THAN (740072) ENGINE = InnoDB,
PARTITION p202604 VALUES LESS THAN (740102) ENGINE = InnoDB,
PARTITION p202605 VALUES LESS THAN (740133) ENGINE = InnoDB,
PARTITION p202606 VALUES LESS THAN (740163) ENGINE = InnoDB,
PARTITION p202607 VALUES LESS THAN (740194) ENGINE = InnoDB,
PARTITION p202608 VALUES LESS THAN (740225) ENGINE = InnoDB,
PARTITION p202609 VALUES LESS THAN (740255) ENGINE = InnoDB,
PARTITION p202610 VALUES LESS THAN (740286) ENGINE = InnoDB,
PARTITION p202611 VALUES LESS THAN (740316) ENGINE = InnoDB,
PARTITION p202612 VALUES LESS THAN (740347) ENGINE = InnoDB,
PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
mysql>
Log Table :
mysql> show create table encryption_log\G
*************************** 1. row ***************************
Table: encryption_log
Create Table: CREATE TABLE `encryption_log` (
`id` bigint NOT NULL AUTO_INCREMENT,
`partition_name` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
`status` enum('SUCCESS','ERROR','SUCCESS (DECRYPT)','ERROR (DECRYPT)') COLLATE utf8mb4_general_ci NOT NULL,
`rows_affected` bigint DEFAULT '0',
`error_message` text COLLATE utf8mb4_general_ci,
`start_time` datetime DEFAULT NULL,
`end_time` datetime DEFAULT NULL,
`duration_seconds` int DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_partition` (`partition_name`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
mysql>
Procedure :
ここでは、AES_ENCRYPTで復号化出来る様にしていますが、SHA2などで復号化出来ない様にするという選択肢も勿論有ります。詳細はMySQLのドキュメントを確認。
https://dev.mysql.com/doc/refman/8.0/ja/encryption-functions.html
DELIMITER $$
DROP PROCEDURE IF EXISTS encrypt_partition_ip$$
CREATE PROCEDURE encrypt_partition_ip(
IN p_partition_name VARCHAR(64)
)
BEGIN
DECLARE v_start_time DATETIME;
DECLARE v_end_time DATETIME;
DECLARE v_rows_affected BIGINT;
DECLARE v_error_msg TEXT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 v_error_msg = MESSAGE_TEXT;
INSERT INTO encryption_log (partition_name, status, error_message, start_time, end_time)
VALUES (p_partition_name, 'ERROR', v_error_msg, v_start_time, NOW());
ROLLBACK;
END;
SET v_start_time = NOW();
-- 動的SQLを使用してパーティション名を展開
SET @sql_stmt = CONCAT(
'UPDATE partitionDB.t_partition_poc PARTITION (', p_partition_name, ') ',
'SET ipAddress = TO_BASE64(AES_ENCRYPT(ipAddress, ''your-encryption-key-here'')) ',
'WHERE ipAddress NOT REGEXP ''^[A-Za-z0-9+/]+=*$'''
);
PREPARE stmt FROM @sql_stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET v_rows_affected = ROW_COUNT();
SET v_end_time = NOW();
-- ログ記録
INSERT INTO encryption_log (partition_name, status, rows_affected, start_time, end_time, duration_seconds)
VALUES (
p_partition_name,
'SUCCESS',
v_rows_affected,
v_start_time,
v_end_time,
TIMESTAMPDIFF(SECOND, v_start_time, v_end_time)
);
COMMIT;
END$$
DELIMITER ;暗号化の実行 :
暗号化のログ :
対象外のパーティションは暗号化されていない :
並列で処理する例 :
#!/bin/bash
DB_HOST="sample-cluster.cluster-xyz.us-east-1.rds.amazonaws.com"
DB_USER="username"
DB_PASS="password"
DB_NAME="DB Name"
MAX_PARALLEL=4 # 並列実行数
echo "=========================================="
echo "Partition Encryption - Parallel Execution"
echo "Max parallel: $MAX_PARALLEL"
echo "Start time: $(date '+%Y-%m-%d %H:%M:%S')"
echo "=========================================="
echo ""
# パーティションリストを取得
PARTITIONS=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -D $DB_NAME -N -e "
SELECT partition_name
FROM information_schema.partitions
WHERE table_schema = 'partitionDB'
AND table_name = 't_partition_poc'
AND table_rows > 0
AND partition_name != 'p_future'
ORDER BY partition_name;
" 2>/dev/null)
PARTITION_COUNT=$(echo "$PARTITIONS" | wc -l)
echo "Total partitions to process: $PARTITION_COUNT"
echo ""
# 並列実行
echo "$PARTITIONS" | xargs -P $MAX_PARALLEL -I {} bash -c "
partition='{}'
echo \"[\$(date '+%Y-%m-%d %H:%M:%S')] START: \$partition\"
start_time=\$(date +%s)
mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -D $DB_NAME -e \"CALL encrypt_partition_ip('\$partition');\" 2>&1 | grep -v 'Warning'
exit_code=\${PIPESTATUS[0]}
end_time=\$(date +%s)
duration=\$((end_time - start_time))
if [ \$exit_code -eq 0 ]; then
echo \"[\$(date '+%Y-%m-%d %H:%M:%S')] DONE: \$partition (\${duration}s)\"
else
echo \"[\$(date '+%Y-%m-%d %H:%M:%S')] ERROR: \$partition (\${duration}s)\"
fi
"
echo ""
echo "=========================================="
echo "All partitions processed"
echo "End time: $(date '+%Y-%m-%d %H:%M:%S')"
echo "=========================================="
echo ""
# 結果サマリーの表示
echo "Encryption Summary:"
mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -D $DB_NAME -e "
SELECT
COUNT(*) AS total_partitions,
SUM(rows_affected) AS total_rows_encrypted,
SUM(duration_seconds) AS total_duration_seconds,
ROUND(AVG(duration_seconds), 2) AS avg_duration_seconds,
MIN(duration_seconds) AS min_duration_seconds,
MAX(duration_seconds) AS max_duration_seconds,
SUM(CASE WHEN status = 'ERROR' THEN 1 ELSE 0 END) AS error_count
FROM encryption_log;
SELECT
partition_name,
status,
rows_affected,
duration_seconds,
DATE_FORMAT(start_time, '%Y-%m-%d %H:%i:%s') AS start_time,
DATE_FORMAT(end_time, '%Y-%m-%d %H:%i:%s') AS end_time
FROM encryption_log
ORDER BY start_time;
" 2>/dev/null