[MYSQL] mysql数据加密原理和解析

# 配置文件添加如下信息:
early-plugin-load=keyring_file.so
keyring_file_data=/usr/local/mysql/keyring/keyring2

# 重启mysql实例
systemctl restart mysqld_3314

create table db1.t20240926(id int primary key, name varchar(200)) encryption='y';
insert into db1.t20240926 values(1,'ddcw');
insert into db1.t20240926 values(2,'ddcw');

-- 给已有的表设置加密
alter table db1.t1 encryption='y';

ALTER [UNDO] TABLESPACE tablespace_name
  NDB only:
    {ADD | DROP} DATAFILE 'file_name'
    [INITIAL_SIZE [=] size]
    [WAIT]
  InnoDB and NDB:
    [RENAME TO tablespace_name]
  InnoDB only:
    [AUTOEXTEND_SIZE [=] 'value']
    [SET {ACTIVE | INACTIVE}]
    [ENCRYPTION [=] {'Y' | 'N'}]
  InnoDB and NDB:
    [ENGINE [=] engine_name]
  Reserved for future use:
    [ENGINE_ATTRIBUTE [=] 'string']

ALTER INSTANCE ROTATE MASTER KEY;

import struct
from Crypto.Cipher import AES
keyring_filename = '/usr/local/mysql/keyring/keyring2'
filename = '/data/mysql_3314/mysqldata/db1/t20240926.ibd'
def read_keyring(data):
	offset = 24
	kd = {}
	xor_str = '*305=Ljt0*!@$Hnm(*-9-w;:'.encode()
	while True:
		if data[offset:offset+3] == b'EOF':
			break
		total_length, key_id_length, key_type_length, user_id_length, key_length = struct.unpack_from('<QQQQQ', data, offset) # 注意是小端字节序...
		offset += 40
		key_id = data[offset:offset+key_id_length].decode()
		offset += key_id_length
		key_type = data[offset:offset+key_type_length].decode()
		offset += key_type_length
		user_id = data[offset:offset+user_id_length]
		offset += user_id_length
		key = data[offset:offset+key_length]
		keyt = bytes([key[i] ^ xor_str[i%24] for i in range(len(key))])
		offset += key_length
		kd[key_id] = {'key':keyt,'key_type':key_type}
		if offset % 8 != 0:
			offset += 8 - (offset % 8)
	return kd

with open(keyring_filename,'rb') as f:
	keyring_data = f.read()

kd = read_keyring(keyring_data)
print(kd)

#MAGIC_SIZE=3  KEY_LEN=32  SERVER_UUID_LEN=36
#(MAGIC_SIZE + sizeof(uint32) + (KEY_LEN * 2) + SERVER_UUID_LEN + sizeof(uint32))
INFO_SIZE = 3+4+32*2+36+4
INFO_MAX_SIZE = INFO_SIZE + 4
#SDI_OFFSET = 38+112+40*256 + INFO_MAX_SIZE
SDI_VERSION = 1

  /* Encryption info to be filled in following format
    --------------------------------------------------------------------------
   | Magic bytes | master key id | server uuid | tablespace key|iv | checksum |
    --------------------------------------------------------------------------
  */

KEY_MAGIC_V1[] = "lCA";  // 5.7.11
KEY_MAGIC_V2[] = "lCB";  // 5.7.12+
KEY_MAGIC_V3[] = "lCC";  // 8.0.5+

master_key = kd['INNODBKey'+'-'+server_uuid+'-'+str(master_id)]['key']

## 解析keyring的代码我就省略了, 上面有的.
kd = read_keyring(keyring_data)
f = open(filename,'rb')
fsp = f.read(16384)
#struct.unpack('>BBHHH',fsp[26:34])
data = fsp[10390:10390+115]
print(data[:3]) # lCC
master_id = struct.unpack('>L',data[3:7])[0]
server_uuid = data[7:7+36].decode()
master_key = kd['INNODBKey'+'-'+server_uuid+'-'+str(master_id)]['key']
ase = AES.new(master_key,AES.MODE_ECB)
print('MASTER_KEY:',master_key)
key_info = ase.decrypt(data[43:43+32*2])
print('KEY:',key_info[:32])
print('IV:',key_info[32:48])

# crc32c的导入参考: https://github.com/ddcw/ddcw/tree/master/python/check_innodb_file  我这里就省略了.
calculate_crc32c(key_info) # 小坑,是校验的整个key_info(不是key+iv). mysql到处给我埋坑....
struct.unpack('>L',fsp[10390:10390+115][-8:-4])[0]

f.seek(4*16384,0)
data = f.read(16384)
struct.unpack('>BBHHH',data[26:34])
data[:200]

from cryptography.hazmat.primitives.ciphers import Cipher, algorithms, modes
from cryptography.hazmat.backends import default_backend
backend = default_backend()
cipher = Cipher(algorithms.AES(key_info[:32]), modes.CBC(key_info[32:48]),backend=backend)
decryptor = cipher.decryptor()
dedata = data[:38] + decryptor.update(data[38:])
dedata[:200]

wget https://github.com/ddcw/ibd2sql/archive/refs/heads/main.zip
unzip main.zip
cd ibd2sql-main
vim ibd2sql/ibd2sql.py添加如下逻辑

from ibd2sql import encrypt
....
# 之前压缩页那再来个elif (我们没有提前解析fsp的encryption_metadata, 所以得把fd也搞过去.)
elif data[24:26] == b'\x00\x0f': # 15: 加密页
			FIL_PAGE_VERSION,FIL_PAGE_ALGORITHM_V1,FIL_PAGE_ORIGINAL_TYPE_V1,FIL_PAGE_ORIGINAL_SIZE_V1,FIL_PAGE_COMPRESS_SIZE_V1 = struct.unpack('>BBHHH',data[26:34])
			data = data[:24] + struct.pack('>H',FIL_PAGE_ORIGINAL_TYPE_V1) + b'\x00'*8 + data[34:38] + encrypt.decrypt(self.f,data[38:])

# vim ibd2sql/encrypt.py
import struct
from Crypto.Cipher import AES
from cryptography.hazmat.primitives.ciphers import Cipher, algorithms, modes
from cryptography.hazmat.backends import default_backend

keyring_filename = '/usr/local/mysql/keyring/keyring2'
def read_keyring(data):
	offset = 24
	kd = {}
	xor_str = '*305=Ljt0*!@$Hnm(*-9-w;:'.encode()
	while True:
		if data[offset:offset+3] == b'EOF':
			break
		total_length, key_id_length, key_type_length, user_id_length, key_length = struct.unpack_from('<QQQQQ', data, offset)
		offset += 40
		key_id = data[offset:offset+key_id_length].decode()
		offset += key_id_length
		key_type = data[offset:offset+key_type_length].decode()
		offset += key_type_length
		user_id = data[offset:offset+user_id_length]
		offset += user_id_length
		key = data[offset:offset+key_length]
		keyt = bytes([key[i] ^ xor_str[i%24] for i in range(len(key))])
		offset += key_length
		kd[key_id] = {'key':keyt,'key_type':key_type}
		if offset % 8 != 0:
			offset += 8 - (offset % 8)
	return kd

with open(keyring_filename,'rb') as f:
	keyring_data = f.read()

def decrypt(f,bdata):
	f.seek(0,0)
	kd = read_keyring(keyring_data)
	fsp = f.read(16384)
	data = fsp[10390:10390+115]
	master_id = struct.unpack('>L',data[3:7])[0]
	server_uuid = data[7:7+36].decode()
	master_key = kd['INNODBKey'+'-'+server_uuid+'-'+str(master_id)]['key']
	ase = AES.new(master_key,AES.MODE_ECB)
	key_info = ase.decrypt(data[43:43+32*2])
	backend = default_backend()
	cipher = Cipher(algorithms.AES(key_info[:32]), modes.CBC(key_info[32:48]),backend=backend)
	decryptor = cipher.decryptor()
	return decryptor.update(bdata)