2024/01/23

mysql_ssl_rsa_setupがopensslコマンドを実行しているだけっぽいので見てみた

TL;DR

実行してるのは以下。


cd $datadir

echo "basicConstraints=CA:TRUE" > cav3.ext

echo "basicConstraints=CA:FALSE" > certv3.ext

openssl version
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout ca-key.pem -subj /CN=MySQL_Server_8.0.36_Auto_Generated_CA_Certificate -out ca-req.pem
openssl rsa -in ca-key.pem -out ca-key.pem
openssl x509 -sha256 -days 3650 -extfile cav3.ext -set_serial 1 -req -in ca-req.pem -signkey ca-key.pem -out ca.pem
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -subj /CN=MySQL_Server_8.0.36_Auto_Generated_Server_Certificate -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -sha256 -days 3650 -extfile certv3.ext -set_serial 2 -req -in server-req.pem -CA ca.pem -CAkey ca-key.pem -out server-cert.pem
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -subj /CN=MySQL_Server_8.0.36_Auto_Generated_Client_Certificate -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -sha256 -days 3650 -extfile certv3.ext -set_serial 3 -req -in client-req.pem -CA ca.pem -CAkey ca-key.pem -out client-cert.pem
openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
openssl genrsa -out private_key.pem 2048
openssl rsa -in private_key.pem -pubout -out public_key.pem

mysql_ssl_rsa_setup がなんか openssl コマンドをそのまま投げている気がしたので strace で雑に拾い上げてみた。

https://github.com/mysql/mysql-server/blob/mysql-8.0.36/client/mysql_ssl_rsa_setup.cc#L270-L271

execveのやつだけ拾い上げてみる。

[yoku0825@yoku0825-sandbox ~]$ strace -f -s 1000 -e execve /usr/mysql/8.0.36/bin/mysql_ssl_rsa_setup --datadir=/tmp/rsa 2>&1 1>/dev/null | grep openssl
[pid 346515] execve("/bin/sh", ["sh", "-c", "openssl version > /dev/null 2>&1"], 0x7fffd3ff2930 /* 46 vars */) = 0
[pid 346516] execve("/usr/bin/openssl", ["openssl", "version"], 0x55eb7da66760 /* 45 vars */) = 0
[pid 346517] execve("/bin/sh", ["sh", "-c", "openssl req -newkey rsa:2048 -days 3650 -nodes -keyout ca-key.pem -subj /CN=MySQL_Server_8.0.36_Auto_Generated_CA_Certificate -out ca-req.pem && openssl rsa -in ca-key.pem -out ca-key.pem > /dev/null 2>&1"], 0x7fffd3ff2930 /* 46 vars */) = 0
[pid 346518] execve("/usr/bin/openssl", ["openssl", "req", "-newkey", "rsa:2048", "-days", "3650", "-nodes", "-keyout", "ca-key.pem", "-subj", "/CN=MySQL_Server_8.0.36_Auto_Generated_CA_Certificate", "-out", "ca-req.pem"], 0x557bb10a9760 /* 45 vars */) = 0
[pid 346519] execve("/usr/bin/openssl", ["openssl", "rsa", "-in", "ca-key.pem", "-out", "ca-key.pem"], 0x557bb10ba9b0 /* 45 vars */) = 0
[pid 346520] execve("/bin/sh", ["sh", "-c", "openssl x509 -sha256 -days 3650 -extfile cav3.ext -set_serial 1 -req -in ca-req.pem -signkey ca-key.pem -out ca.pem > /dev/null 2>&1"], 0x7fffd3ff2930 /* 46 vars */) = 0
[pid 346521] execve("/usr/bin/openssl", ["openssl", "x509", "-sha256", "-days", "3650", "-extfile", "cav3.ext", "-set_serial", "1", "-req", "-in", "ca-req.pem", "-signkey", "ca-key.pem", "-out", "ca.pem"], 0x562d199db760 /* 45 vars */) = 0
[pid 346522] execve("/bin/sh", ["sh", "-c", "openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -subj /CN=MySQL_Server_8.0.36_Auto_Generated_Server_Certificate -out server-req.pem && openssl rsa -in server-key.pem -out server-key.pem > /dev/null 2>&1"], 0x7fffd3ff2930 /* 46 vars */) = 0
[pid 346523] execve("/usr/bin/openssl", ["openssl", "req", "-newkey", "rsa:2048", "-days", "3650", "-nodes", "-keyout", "server-key.pem", "-subj", "/CN=MySQL_Server_8.0.36_Auto_Generated_Server_Certificate", "-out", "server-req.pem"], 0x55882da28760 /* 45 vars */) = 0
[pid 346524] execve("/usr/bin/openssl", ["openssl", "rsa", "-in", "server-key.pem", "-out", "server-key.pem"], 0x55882da398d0 /* 45 vars */) = 0
[pid 346525] execve("/bin/sh", ["sh", "-c", "openssl x509 -sha256 -days 3650 -extfile certv3.ext -set_serial 2 -req -in server-req.pem -CA ca.pem -CAkey ca-key.pem -out server-cert.pem > /dev/null 2>&1"], 0x7fffd3ff2930 /* 46 vars */) = 0
[pid 346526] execve("/usr/bin/openssl", ["openssl", "x509", "-sha256", "-days", "3650", "-extfile", "certv3.ext", "-set_serial", "2", "-req", "-in", "server-req.pem", "-CA", "ca.pem", "-CAkey", "ca-key.pem", "-out", "server-cert.pem"], 0x5577023de760 /* 45 vars */) = 0
[pid 346527] execve("/bin/sh", ["sh", "-c", "openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -subj /CN=MySQL_Server_8.0.36_Auto_Generated_Client_Certificate -out client-req.pem && openssl rsa -in client-key.pem -out client-key.pem > /dev/null 2>&1"], 0x7fffd3ff2930 /* 46 vars */) = 0
[pid 346528] execve("/usr/bin/openssl", ["openssl", "req", "-newkey", "rsa:2048", "-days", "3650", "-nodes", "-keyout", "client-key.pem", "-subj", "/CN=MySQL_Server_8.0.36_Auto_Generated_Client_Certificate", "-out", "client-req.pem"], 0x56301ba4e760 /* 45 vars */) = 0
[pid 346529] execve("/usr/bin/openssl", ["openssl", "rsa", "-in", "client-key.pem", "-out", "client-key.pem"], 0x56301ba5f8d0 /* 45 vars */) = 0
[pid 346530] execve("/bin/sh", ["sh", "-c", "openssl x509 -sha256 -days 3650 -extfile certv3.ext -set_serial 3 -req -in client-req.pem -CA ca.pem -CAkey ca-key.pem -out client-cert.pem > /dev/null 2>&1"], 0x7fffd3ff2930 /* 46 vars */) = 0
[pid 346531] execve("/usr/bin/openssl", ["openssl", "x509", "-sha256", "-days", "3650", "-extfile", "certv3.ext", "-set_serial", "3", "-req", "-in", "client-req.pem", "-CA", "ca.pem", "-CAkey", "ca-key.pem", "-out", "client-cert.pem"], 0x5562dd1f4760 /* 45 vars */) = 0
[pid 346532] execve("/bin/sh", ["sh", "-c", "openssl verify -CAfile ca.pem server-cert.pem client-cert.pem > /dev/null 2>&1"], 0x7fffd3ff2930 /* 46 vars */) = 0
[pid 346533] execve("/usr/bin/openssl", ["openssl", "verify", "-CAfile", "ca.pem", "server-cert.pem", "client-cert.pem"], 0x562abf4f7760 /* 45 vars */) = 0
[pid 346534] execve("/bin/sh", ["sh", "-c", "openssl genrsa  -out private_key.pem 2048 > /dev/null 2>&1"], 0x7fffd3ff2930 /* 46 vars */) = 0
[pid 346535] execve("/usr/bin/openssl", ["openssl", "genrsa", "-out", "private_key.pem", "2048"], 0x55ed169cb760 /* 45 vars */) = 0
[pid 346536] execve("/bin/sh", ["sh", "-c", "openssl rsa -in private_key.pem -pubout -out public_key.pem > /dev/null 2>&1"], 0x7fffd3ff2930 /* 46 vars */) = 0
[pid 346537] execve("/usr/bin/openssl", ["openssl", "rsa", "-in", "private_key.pem", "-pubout", "-out", "public_key.pem"], 0x5593c3a5e760 /* 45 vars */) = 0

ここからコマンドだけを引っこ抜いた ( && の部分は成功したから openssl が引数の方の execve に載っていたのでリストしてある ) のが冒頭のテキスト。
echo コマンドまでは使っていなかったけど、このファイルが無いと当然 -extfile の指定で転けるので平文だしechoで作ってみた。

https://github.com/mysql/mysql-server/blob/mysql-8.0.36/client/mysql_ssl_rsa_setup.cc#L317-L321

流石に mysqld の auto_generate_certs の方はここまで雑な作りではなかった。

https://github.com/mysql/mysql-server/blob/824e2b4064053f7daf17d7f3f84b7a3ed92e5fb4/sql/auth/sql_authentication.cc#L5912-L5990

ちなみに mysql_ssl_rsa_setup は8.0.34で非推奨だそうです。
https://dev.mysql.com/doc/refman/8.0/en/mysql-ssl-rsa-setup.html

2024/01/20

keyring_fileを消したり戻したりしながら確かめてみる

ただ思いついたことを順番に実行したメモ。

1. keyring_fileを食った状態でt1を作る。

2. keyring_fileをコピーして消す

3. t2を作る -> エラーログなし

4. mysqldを停止してkeyring_fileを戻す

5. mysqldを起動する

6. t1もt2も読める

7. keyring_fileをコピーして消してmysqld再起動

8. t1, t2は読めない -> 起動時のエラーログに出る

9. t3を作る

10. mysqld再起動

11. t3を作った時点でkeyring_fileが変わっているのでt1, t2は読めない、t3だけが読める状態

```

2024-01-20T14:29:22.972253Z 1 [ERROR] [MY-012664] [InnoDB] Failed to decrypt encryption information, please check whether key file has been changed!

2024-01-20T14:29:22.972297Z 1 [ERROR] [MY-012226] [InnoDB] Encryption information in datafile: ./d1/t1.ibd can't be decrypted, please confirm that keyring is loaded.

2024-01-20T14:29:22.972780Z 1 [ERROR] [MY-012664] [InnoDB] Failed to decrypt encryption information, please check whether key file has been changed!

2024-01-20T14:29:22.972795Z 1 [ERROR] [MY-012226] [InnoDB] Encryption information in datafile: ./d1/t2.ibd can't be decrypted, please confirm that keyring is loaded.

```

12. mysqldを止めてt3を作ったkeyring_fileを消してt2までの時点のkeyring_fileを戻して起動

13. t1, t2は読めるけどt3が読めない状態で起動

14. t3のことは忘れて、keyring_fileをコピーして消してmysqld再起動 -> 当然t1, t2が読めない状態で起動

15. keyring_fileを戻してt4を作る

16. mysqld再起動

17. t1, t2は読めない、t4だけが読める

keyring_fileを食わせて起動したらさっさと消してしまってマスターキーのローテーションの時だけ戻せば…とか雑なことを考えたけど、その間に暗号化テーブルを作られたら簡単に詰む

xtrabackup + InnoDB TDE / keyring_file_dataで--prepareと--move-back 続き

日々の覚書: xtrabackup + InnoDB TDE / keyring_file_dataで--prepareと--move-back の続き

keyring_file_dataを真面目に与えなくても何故か —prepare できてしまったのが謎だったので書き込みながら。

$ for n in {101..10000} ; do
> sudo mysql -e "INSERT INTO d1.t1 VALUES ($n)"
> done

$ sudo xtrabackup --backup -uroot --stream=xbstream | pzstd -dc > test.xb.zst
2024-01-20T05:23:28.912847-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql
2024-01-20T05:23:28.913000-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --backup=1 --user=root --stream=xbstream
xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)
..

$ mkdir backup_with_running
$ cd backup_with_running
$ pzstd -dc ../test.xb.zst | xbstream -x
$ xtrabackup --prepare --target-dir=./ --keyring-file-data=/tmp/dummy2
2024-01-20T05:27:26.978005-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
2024-01-20T05:27:26.978103-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --target-dir=./ --keyring-file-data=/tmp/dummy2
xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)
..
2024-01-20T05:27:27.080448-00:00 0 [Note] [MY-012552] [InnoDB] Starting crash recovery.
2024-01-20T05:27:27.080686-00:00 0 [Note] [MY-013086] [InnoDB] Starting to parse redo log at lsn = 20334092, whereas checkpoint_lsn = 20334355 and start_lsn = 20334080
2024-01-20T05:27:27.083746-00:00 0 [Note] [MY-012550] [InnoDB] Doing recovery: scanned up to log sequence number 20923858
2024-01-20T05:27:27.143189-00:00 0 [Note] [MY-013083] [InnoDB] Log background threads are being started...
2024-01-20T05:27:27.192664-00:00 0 [Note] [MY-012532] [InnoDB] Applying a batch of 302 redo log records ...
2024-01-20T05:27:27.193694-00:00 0 [Note] [MY-012533] [InnoDB] 10%
2024-01-20T05:27:27.194170-00:00 0 [Note] [MY-012533] [InnoDB] 20%
2024-01-20T05:27:27.194838-00:00 0 [Note] [MY-012533] [InnoDB] 30%
2024-01-20T05:27:27.194982-00:00 0 [Note] [MY-012533] [InnoDB] 40%
2024-01-20T05:27:27.195690-00:00 0 [Note] [MY-012533] [InnoDB] 50%
2024-01-20T05:27:27.196225-00:00 0 [Note] [MY-012533] [InnoDB] 60%
2024-01-20T05:27:27.196386-00:00 0 [Note] [MY-012533] [InnoDB] 70%
2024-01-20T05:27:27.196937-00:00 0 [Note] [MY-012533] [InnoDB] 80%
2024-01-20T05:27:27.197047-00:00 0 [Note] [MY-012533] [InnoDB] 90%
2024-01-20T05:27:27.198439-00:00 0 [Note] [MY-011825] [InnoDB] Encryption information in datafile: ./d1/t1.ibd can't be decrypted, please confirm that keyring is loaded.
2024-01-20T05:27:27.198468-00:00 0 [ERROR] [MY-011825] [Xtrabackup] Invalid encryption metadata in tablespace header.
2024-01-20T05:27:27.198477-00:00 0 [ERROR] [MY-011825] [Xtrabackup] Unable to decrypt. Please check if xtrabackup is configured correctly to access the keyring plugin or component. Check --xtrabackup-plugin-dir. Also verify if valid keyring_file_data is passed with the option --keyring_file_data. If keyring component is used, check if --component-keyring-config points to valid configuration

ちゃんと転けた。やっぱり暗号対象テーブルスペースがクラッシュリカバリ対象にならない限りは復号化が必要ないから前回は転けなかったので合ってるらしい。

さて、ここまで整理がついたので、本題(?) の「rpmではないバイナリtarボールを展開したxtrabackupでパスの様子を見る」をやる。

$ wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.35-30/binary/tarball/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17.tar.gz
$ tar xf percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17.tar.gz
$ sudo ./percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup --backup -uroot --stream=xbstream | pzstd -c > test.xb.zst
2024-01-20T05:57:43.365008-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql
2024-01-20T05:57:43.365115-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --backup=1 --user=root --stream=xbstream
./percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)
240120 05:57:43  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: NO).
240120 05:57:43  version_check Connected to MySQL server
240120 05:57:43  version_check Executing a version check against the server...
240120 05:57:43  version_check Done.
2024-01-20T05:57:43.425978-00:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: not set
2024-01-20T05:57:43.426090-00:00 0 [ERROR] [MY-011825] [Xtrabackup] Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

そもそもtarボール版だと --socket の暗黙のデフォルトの値が違う。

$ sudo ./percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup --backup -uroot --stream=xbstream --socket=/var/lib/mysql/mysql.sock | pzstd -c > test.xb.zst
2024-01-20T06:00:06.747082-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql
2024-01-20T06:00:06.747179-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --backup=1 --user=root --stream=xbstream --socket=/var/lib/mysql/mysql.sock
./percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49
..
2024-01-20T06:00:06.846775-00:00 0 [Note] [MY-011825] [Xtrabackup] Added plugin 'keyring_file.so' to load list.
2024-01-20T06:00:06.847814-00:00 0 [ERROR] [MY-010901] [Server] Can't open shared library '/mnt/jenkins/workspace/percona-xtrabackup-8.0-RELEASE/test/TARGET/xtrabackup-build.XXP2GX/percona-xtrabackup-8.0.35
-30-Linux-x86_64.glibc2.17/lib/plugin/keyring_file.so' (errno: 2 cannot open shared object file: No such file or directory).
2024-01-20T06:00:06.847914-00:00 0 [ERROR] [MY-010736] [Server] Couldn't load plugin named 'keyring_file.so' with soname 'keyring_file.so'.
..
2024-01-20T06:00:09.573058-00:00 0 [ERROR] [MY-011825] [Xtrabackup] Space ID 2 is missing encryption information.

そもそも keyring_file.so が掴めないと言われる。
見るからにコンパイルした環境の lib/plugin を指しているっぽいので、 --xtrabackup-plugin-dir で展開したディレクトリの lib/plugin を指してやる必要がありそう。

$ sudo ./percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup --backup -uroot --stream=xbstream --socket=/var/lib/mysql/mysql.sock --xtrabackup-plugin-dir=/home/opc/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/lib/plugin | pzstd -c > test.xb.zst
2024-01-20T06:03:54.596743-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql
2024-01-20T06:03:54.596830-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --backup=1 --user=root --stream=xbstream --socket=/var/lib/mysql/mysql.sock --xtrabackup-plugin-dir=/home/opc/
percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/lib/plugin
./percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)

..
2024-01-20T06:03:57.435335-00:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'keyring_file'
2024-01-20T06:03:57.435594-00:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'daemon_keyring_proxy_plugin'
2024-01-20T06:03:57.436245-00:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

取れた。

$ mkdir binary_tarball
$ cd binary_tarball
$ pzstd -dc ../test.xb.zst | xbstream -x

$ sudo /home/opc/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup --prepare --target-dir=./ --keyring-file-data=/home/opc/mysql-secret --xtrabackup-plugin-dir=/home/opc/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/lib/plugin
2024-01-20T06:10:57.745096-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --in
nodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
2024-01-20T06:10:57.745205-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --target-dir=./ --keyring-file-data=/home/opc/mysql-secret --xtrabackup-plugin-dir=/home/opc/perco
na-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/lib/plugin
/home/opc/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)

..
2024-01-20T06:10:59.256124-00:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 24506902
2024-01-20T06:10:59.258134-00:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'keyring_file'
2024-01-20T06:10:59.258396-00:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'daemon_keyring_proxy_plugin'
2024-01-20T06:10:59.258780-00:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

--xtrabackup-plugin-dir の指定を忘れてエラーにしたりしたけど、忘れなければ大丈夫っぽい。

ちなみにxb —prepareにsudoつけるの忘れて ( /home/opc/mysql-secret はopcアカウントでは読めないパーミッション )実行したら容赦なくSEGVした。もう少し優しく教えてくれても…（）

$ /home/opc/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup --prepare --target-dir=./ --keyring-file-data=/home/opc/mysql-secret --xtrabackup-plugin-dir=/home/opc/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/lib/plugin
2024-01-20T06:09:05.770533-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
2024-01-20T06:09:05.770857-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --target-dir=./ --keyring-file-data=/home/opc/mysql-secret --xtrabackup-plugin-dir=/home/opc/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/lib/plugin
/home/opc/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)
2024-01-20T06:09:05.770919-00:00 0 [Note] [MY-011825] [Xtrabackup] cd to /home/opc/binary_tarball/
2024-01-20T06:09:05.771148-00:00 0 [Note] [MY-011825] [Xtrabackup] This target seems to be not prepared yet.
2024-01-20T06:09:05.780986-00:00 0 [Note] [MY-011825] [Xtrabackup] xtrabackup_logfile detected: size=8388608, start_lsn=(24421351)
2024-01-20T06:09:05.781545-00:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2024-01-20T06:09:05.781624-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2024-01-20T06:09:05.781635-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2024-01-20T06:09:05.781730-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = .
2024-01-20T06:09:05.781824-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 1
2024-01-20T06:09:05.781896-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 8388608
2024-01-20T06:09:05.782506-00:00 0 [Warning] [MY-013360] [InnoDB] Plugin keyring_file reported: ''keyring_file plugin' is deprecated and will be removed in a future release. Please use component_keyring_file instead'
2024-01-20T06:09:05.782771-00:00 0 [ERROR] [MY-011370] [InnoDB] Plugin keyring_file reported: 'File '/home/opc/mysql-secret' not found (OS errno 13 - Permission denied)'
2024-01-20T06:09:05.782837-00:00 0 [ERROR] [MY-011355] [InnoDB] Plugin keyring_file reported: 'keyring_file initialization failure. Please check if the keyring_file_data points to readable keyring file or keyring file can be created in the specified location. The keyring_file will stay unusable until correct path to the keyring file gets provided'
2024-01-20T06:09:05.782895-00:00 0 [ERROR] [MY-010202] [Server] Plugin 'keyring_file' init function returned error.
2024-01-20T06:09:05.783203-00:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2024-01-20T06:09:05Z UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=
Thread pointer: 0x4bdc3a0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7ffd8c5c4ef0 thread_stack 0x100000
/home/opc/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x2585b4d]
/home/opc/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup(print_fatal_signal(int)+0x393) [0x12c4893]
/home/opc/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup(handle_fatal_signal+0x95) [0x12c4975]
/lib64/libpthread.so.0(+0x12d40) [0x7fb681543d40]
/home/opc/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup(add_plugin_options(std::vector<my_option, std::allocator<my_option> >*, MEM_ROOT*)+0x68) [0x117ba38]
/home/opc/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup() [0xd0d835]
/home/opc/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup() [0xd15f23]
/home/opc/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup(main+0x1af5) [0xcc72b5]
/lib64/libc.so.6(__libc_start_main+0xe5) [0x7fb67efa0e45]
/home/opc/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup() [0xcfd115]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 0
Status: NOT_KILLED

Please report a bug at https://jira.percona.com/projects/PXB

InnoDBログとUNDOログの暗号化中のは試さなくても大丈夫かな、これなら。

2024/01/19

xtrabackup + InnoDB TDE / keyring_file_dataで--prepareと--move-back

日々の覚書: keyring_file_dataに相対パスを指定したらdatadirからの相対パスで作られてしまったの続き

xtrabackupの準備


$ sudo dnf install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm

$ sudo percona-release enable-only tools

$ sudo dnf install -y percona-xtrabackup-80

元記事の順番をかっ飛ばして、元記事の順番その3、 keyring_file_data を相対パスで指定した時のバックアップとリストア（ちなみに標準パスを使おうと絶対パスを使おうと相対パスを使おうと同じだったので他の2つはもうやらない）


$ sudo xtrabackup --backup -uroot --stream=xbstream | pzstd -c > test.xb.zst

2024-01-19T13:48:33.194738-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql

2024-01-19T13:48:33.194819-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --backup=1 --user=root --stream=xbstream

xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)

240119 13:48:33  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: NO).

240119 13:48:33  version_check Connected to MySQL server

240119 13:48:33  version_check Executing a version check against the server...

240119 13:48:33  version_check Done.

..
2024-01-19T13:48:36.010152-00:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'keyring_file'
2024-01-19T13:48:36.010214-00:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'daemon_keyring_proxy_plugin'
2024-01-19T13:48:36.010958-00:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

$ sudo cp /var/lib/mysql/path_to_key ./    ### keyring_fileを他のところにバックアップした風で

prepare

$ mkdir relpath_restore
$ cd relpath_restore
$ pzstd -dc ../test.xb.zst | xbstream -x
../test.xb.zst      : 74104109 bytes

$ xtrabackup --prepare --target-dir=./
2024-01-19T13:51:15.518235-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
2024-01-19T13:51:15.518345-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --target-dir=./
xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)
2024-01-19T13:51:15.518376-00:00 0 [Note] [MY-011825] [Xtrabackup] cd to /home/opc/relpath_restore/
2024-01-19T13:51:15.518447-00:00 0 [Note] [MY-011825] [Xtrabackup] This target seems to be not prepared yet.
2024-01-19T13:51:15.523548-00:00 0 [Note] [MY-011825] [Xtrabackup] xtrabackup_logfile detected: size=8388608, start_lsn=(20333076)
2024-01-19T13:51:15.523969-00:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2024-01-19T13:51:15.523988-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2024-01-19T13:51:15.523999-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2024-01-19T13:51:15.524024-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = .
2024-01-19T13:51:15.524034-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 1
2024-01-19T13:51:15.524043-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 8388608
2024-01-19T13:51:15.524506-00:00 0 [Warning] [MY-013360] [InnoDB] Plugin keyring_file reported: ''keyring_file plugin' is deprecated and will be removed in a future release. Please use component_keyring_file instead'
2024-01-19T13:51:15.524574-00:00 0 [ERROR] [MY-011370] [InnoDB] Plugin keyring_file reported: 'File '/usr/keyring/keyring' not found (OS errno 2 - No such file or directory)'
2024-01-19T13:51:15.524604-00:00 0 [ERROR] [MY-011355] [InnoDB] Plugin keyring_file reported: 'keyring_file initialization failure. Please check if the keyring_file_data points to readable keyring file or keyring file can be created in the specified location. The keyring_file will stay unusable until correct path to the keyring file gets provided'
2024-01-19T13:51:15.524621-00:00 0 [ERROR] [MY-010202] [Server] Plugin 'keyring_file' init function returned error.
2024-01-19T13:51:15.524698-00:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2024-01-19T13:51:15Z UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=
Thread pointer: 0x5f22260
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7ffed0918140 thread_stack 0x100000
xtrabackup(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x259c331]
xtrabackup(print_fatal_signal(int)+0x3c3) [0x13a0bf3]
xtrabackup(handle_fatal_signal+0x6d) [0x13a0c6d]
/lib64/libpthread.so.0(+0x12d40) [0x7f1ca4875d40]
xtrabackup(add_plugin_options(std::vector<my_option, std::allocator<my_option> >*, MEM_ROOT*)+0x70) [0x1256220]
xtrabackup() [0xdec882]
xtrabackup() [0xdf89c2]
xtrabackup(main+0x1d65) [0xdaa285]
/lib64/libc.so.6(__libc_start_main+0xe5) [0x7f1ca21fce45]
xtrabackup(_start+0x2e) [0xddfe2e]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 0
Status: NOT_KILLED

Please report a bug at https://jira.percona.com/projects/PXB

SEGVした。もう少し優しく、keyring_fileを指定していないよって教えてくれても良いとは思う。

2024-01-19T13:51:15.524574-00:00 0 [ERROR] [MY-011370] [InnoDB] Plugin keyring_file reported: 'File '/usr/keyring/keyring' not found (OS errno 2 - No such file or directory)'
2024-01-19T13:51:15.524604-00:00 0 [ERROR] [MY-011355] [InnoDB] Plugin keyring_file reported: 'keyring_file initialization failure. Please check if the keyring_file_data points to readable keyring file or keyring file can be created in the specified location. The keyring_file will stay unusable until correct path to the keyring file gets provided'

なんで `/usr/keyring/keyringなんかを見に行こうとしているのかは謎。backup-my.cnfにもそんなパスは書いてない。

$ cat backup-my.cnf
# This MySQL options file was generated by innobackupex.

# The MySQL server
[mysqld]
innodb_checksum_algorithm=crc32
innodb_log_checksums=1
innodb_data_file_path=ibdata1:12M:autoextend
innodb_log_file_size=50331648
innodb_page_size=16384
innodb_undo_directory=./
innodb_undo_tablespaces=2
server_id=0
innodb_log_checksums=ON
innodb_redo_log_encrypt=OFF
innodb_undo_log_encrypt=OFF
plugin_load=keyring_file.so
server_uuid=d1e91ab6-b6ac-11ee-89b6-0200170399a5
master_key_id=1

--keyring-file-data でもとの場所からコピーしてきたキーを指定するとリストアできている（風味）

$ xtrabackup --prepare --target-dir=./ --keyring-file-data=/home/opc/path_to_keyr
2024-01-19T13:53:28.950744-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
2024-01-19T13:53:28.950826-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --target-dir=./ --keyring-file-data=~/path_to_keyring
xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)

..
2024-01-19T13:53:30.463107-00:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 20333590
2024-01-19T13:53:30.464894-00:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'keyring_file'
2024-01-19T13:53:30.464952-00:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'daemon_keyring_proxy_plugin'
2024-01-19T13:53:30.465292-00:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

しかしこれ、存在しもしないkeyring_fileを指定してもcompleted OKになっちゃうんだけど大丈夫…？

$ mkdir dummy
$ cd dummy
$ pzstd -dc ../test.xb.zst | xbstream -x
$ xtrabackup --prepare --target-dir=./ --keyring-file-data=/tmp/dummy
2024-01-19T14:06:18.136573-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
2024-01-19T14:06:18.136675-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --target-dir=./ --keyring-file-data=/tmp/dummy
..
2024-01-19T14:06:19.718228-00:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'keyring_file'
2024-01-19T14:06:19.718266-00:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'daemon_keyring_proxy_plugin'
2024-01-19T14:06:19.718610-00:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

$ ll /tmp/dummy
-rw-r-----. 1 opc opc 0 Jan 19 14:06 /tmp/dummy

一応起動してみる。

$ sudo systemctl stop mysqld
$ sudo rm -r /var/lib/mysql*

$ sudo xtrabackup --move-back --target-dir=./
2024-01-19T14:08:12.872319-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql
2024-01-19T14:08:12.872402-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --move-back=1 --target-dir=./
xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)

..
2024-01-19T14:08:12.884466-00:00 1 [Note] [MY-011825] [Xtrabackup] Moving ./ibtmp1 to /var/lib/mysql/ibtmp1
2024-01-19T14:08:12.884512-00:00 1 [Note] [MY-011825] [Xtrabackup] Done: Moving file ./ibtmp1 to /var/lib/mysql/ibtmp1
2024-01-19T14:08:12.975071-00:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

$ sudo chown -R mysql. /var/lib/mysql
$ sudo cp ~/path_to_key /var/lib/mysql/path_to_key   ### 本来のkeyring_fileの戻し
$ sudo chown mysql. /var/lib/mysql/path_to_key

$ sudo systemctl start mysqld

$ sudo mysql -e "SHOW CREATE TABLE d1.t1\G SELECT * FROM d1.t1"
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
+------+
| num  |
+------+
|    1 |
|    2 |
|    3 |
+------+

起動できたし読み込めてしまった？ Σ(ﾟдﾟlll)

何故…と思ったけど、これは静止点でxtrabackup取ったからInnoDBログの適用が要らなかったから --prepare の時点では別に復号化の必要が全く無かったから存在しないものを指定したとして何も不都合がなかったということのなのかな。

ちゃんと何か書き込みしながらの --backup もあとでやる。

取り敢えず、元のパスが相対だろうとなんだろうと、別途コピーしたkeyring_fileがまともならちゃんと動くはずだということは分かった。

登録: 投稿 ( Atom )

GA