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ログの暗号化中のは試さなくても大丈夫かな、これなら。

​

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がまともならちゃんと動くはずだということは分かった。

​

keyring_file_dataに相対パスを指定したらdatadirからの相対パスで作られてしまった

TL;DR

• keyring_file_data に絶対パスを指定したら面倒なことになった
• 相対パスを指定するとdatadirの真下に作りやがる

---

準備。 keyring_file_data を指定しない場合はコンパイル時デフォルト (rpmは /var/lib/mysql-keyring 、 Standaloneは basedir/keyring ) に作る。

$ sudo dnf install -y https://dev.mysql.com/get/mysql80-community-release-el8-9.noarch.rpm
$ sudo dnf module -y disable mysql
$ sudo dnf install -y mysql-community-server
$ sudo vim /etc/my.cnf
..
user=mysql
early_plugin_load=keyring_file.so

$ sudo mysqld --initialize-insecure
$ sudo systemctl start mysqld
$ sudo mysql -e "CREATE DATABASE d1; CREATE TABLE d1.t1 (num int) ENCRYPTION='Y'; INSERT INTO d1.t1 VALUES(1), (2), (3)"

$ sudo ls -l /var/lib/mysql-keyring/keyring
-rw-r-----. 1 mysql mysql 187 Jan 19 09:10 /var/lib/mysql-keyring/keyring

絶対パスに書き換えてみる。そのままだと自動生成に失敗するので先にディレクトリを作っておく。 ディレクトリには少なくとも700のパーミッションがないとkeyring_fileを掴むのに失敗して、MySQLは起動するけど ERROR 3185 (HY000) at line 1: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully. のエラーを吐かれる

$ sudo systemctl stop mysqld
$ sudo dnf remove -y mysql-community-server
$ sudo rm -r /var/lib/mysql*

$ sudo dnf install -y mysql-community-server

$ sudo vim /etc/my.cnf
..
user=mysql
early_plugin_load=keyring_file.so
keyring_file_data=/mysql/mysql-secret

$ sudo mkdir -m 700 /mysql
$ sudo chown -R mysql. /mysql

$ sudo mysqld --initialize-insecure
$ sudo systemctl start mysqld
$ sudo mysql -e "CREATE DATABASE d1; CREATE TABLE d1.t1 (num int) ENCRYPTION='Y'; INSERT INTO d1.t1 VALUES(1), (2), (3)"

$ sudo ls -l /mysql/mysql-secret
-rw-r-----. 1 mysql mysql 187 Jan 19 09:23 /mysql/mysql-secret

相対パスに書き換えてみる。

$ sudo systemctl stop mysqld
$ sudo dnf remove -y mysql-community-server
$ sudo rm -r /var/lib/mysql* /mysql

$ sudo dnf install -y mysql-community-server

$ sudo vim /etc/my.cnf
..
user=mysql
early_plugin_load=keyring_file.so
keyring_file_data=./path_to_key

$ sudo mysqld --initialize-insecure
$ sudo systemctl start mysqld
$ sudo mysql -e "CREATE DATABASE d1; CREATE TABLE d1.t1 (num int) ENCRYPTION='Y'; INSERT INTO d1.t1 VALUES(1), (2), (3)"

$ sudo ls -l /var/lib/mysql/path_to_key
-rw-r-----. 1 mysql mysql 187 Jan 19 09:26 /var/lib/mysql/path_to_key

Σ(ﾟдﾟlll) えっ、datadirからの相対パスだと解釈しちゃうの…？

​

MySQL ShellのloadDumpが MySQL Error 1449 (HY000): The user specified as a definer ('xxx'@'%') does not exist で転けたり転けなかったりする

 

TL;DR

• DEFINERが存在しないSQL SECURITY DEFINERなビューと、そのビューを使ったビューがある時にMySQL ShellのloadDumpが転けることも転けないこともある
  • mysqldumpからのリストアは100%転ける
• 転けるのが正しい気がするけど何故か転けずに完了してしまうのが気になったのでこのエントリ

---

参考: 日々の覚書: MySQLの論理バックアップにおける2段階のViewのリストア

下準備。
ビューのDEFINERになっているアカウントをDROPして、「DEFINERが存在しないビュー」「そのビューを使ったビュー」を作る。
（先にアカウントを作ってからDROPしないと、「DEFINERが存在しないビュー」までは無理矢理作れるけれども「そのビューを使ったビュー」が作れない）

CREATE USER dummy;
CREATE DATABASE d1;
CREATE definer=dummy VIEW d1.v1 AS SELECT 1;
CREATE VIEW d1.v2 AS SELECT * FROM d1.v1;
DROP USER dummy;
SHOW WARNINGS;

DROP USERした時にちゃんとワーニングは出る。

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 4005 | User 'dummy'@'%' is referenced as a definer account in a view. |
+---------+------+----------------------------------------------------------------+
1 row in set (0.00 sec)

比較のためにmysqldumpも取っておく。ちなみに --single-transaction を省くと個別にLOCK TABLEしようとして転ける。

$ mysqldump -h172.17.0.2 -B d1 --single-transaction > /tmp/d1.sql

このダンプをリストアしようとすると必ず転ける。

$ mysql -h172.17.0.3 -vv < /tmp/d1.sql
..
--------------
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v2` AS select `v1`.`1` AS `1` from `v1` */
--------------

ERROR 1449 (HY000) at line 85: The user specified as a definer ('dummy'@'%') does not exist
Bye

MySQL ShellでdumpInstanceする。

$ mysqlsh -h172.17.0.2 -- util dumpInstance '/tmp/dump'
Please provide the password for 'root@172.17.0.2':
Save password for 'root@172.17.0.2'? [Y]es/[N]o/Ne[v]er (default No):
Acquiring global read lock
Global read lock acquired
Initializing - done
1 out of 5 schemas will be dumped and within them 0 tables, 2 views.
3 out of 6 users will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
?% (0 rows / ?), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s
Total duration: 00:00:00s
Schemas dumped: 1
Tables dumped: 0
Uncompressed data size: 0 bytes
Compressed data size: 0 bytes
Compression ratio: 0.0
Rows written: 0
Bytes written: 0 bytes
Average uncompressed throughput: 0.00 B/s
Average compressed throughput: 0.00 B/s

この時、 /tmp/dump/d1.json に入っている views の順番も多少関係する。v1が先に来ていれば転ける確率の方が高くなり、v2が先に来ていれば転けない可能性の方が高かった。

$ cat /tmp/dump/d1.json
{
    "schema": "d1",
    "includesDdl": true,
    "includesViewsDdl": true,
    "includesData": true,
    "tables": [],
    "views": [
        "v2",
        "v1"
    ],
    "events": [],
    "functions": [],
    "procedures": [],
    "basenames": {
        "v2": "d1@v2",
        "v1": "d1@v1"
    }
}

--threads を大きめにすると転ける可能性が高くなる。

転けない時はこんな感じ。

$ mysqlsh -h172.17.0.4 -- util loadDump '/tmp/dump' --threads=10
Please provide the password for 'root@172.17.0.2':
Save password for 'root@172.17.0.2'? [Y]es/[N]o/Ne[v]er (default No):
Loading DDL and Data from '/tmp/dump' using 10 threads.
Opening dump...
Target is MySQL 8.0.35. Dump was produced from MySQL 8.0.35
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Executing common postamble SQL
Starting data load
?% (0 bytes / ?), 0.00 B/s, 0 / 0 tables done
Recreating indexes - done
No data loaded.
0 warnings were reported during the load.

転ける時はこんな感じ。

$ mysqlsh -h172.17.0.5 -- util loadDump '/tmp/dump' --threads=20
Please provide the password for 'root@172.17.0.2':
Save password for 'root@172.17.0.2'? [Y]es/[N]o/Ne[v]er (default No):
Loading DDL and Data from '/tmp/dump' using 20 threads.
Opening dump...
Target is MySQL 8.0.35. Dump was produced from MySQL 8.0.35
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
ERROR: Error executing DDL script for view `d1`.`v2`: MySQL Error 1449 (HY000): The user specified as a definer ('dummy'@'%') does not exist: /*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v2` AS select `v1`.`1` AS `1` from `v1` */
Executing view DDL - done
ERROR: The user specified as a definer ('dummy'@'%') does not exist

ビューを2段階リストアしているので、mysqldumpとかで直列にやった場合はこうなる。

CREATE VIEW v1 AS SELECT 1 AS 1;
CREATE VIEW v2 AS SELECT 1 AS 1;
DROP VIEW v1;
CREATE DEFINER=dummy VIEW v1 AS SELECT 1;
DROP VIEW v2;
CREATE VIEW v2 AS SELECT * FROM v1;    -- ここでER_NO_SUCH_USER(=1449)で転ける

しかしパラレルにリストアすると、まれに v2 と v1 のタイミングがひっくり返る。MySQL Shellはd1.jsonのviewsに順番に読もうとする（んだと思う）ので、v2が先に来ているとよくこの順番になるんではないか。

CREATE VIEW v1 AS SELECT 1 AS 1;
CREATE VIEW v2 AS SELECT 1 AS 1;
DROP VIEW v2;
CREATE VIEW v2 AS SELECT * FROM v1;    -- この時点ではv1は不正なビューではないので転けない

DROP VIEW v1;

CREATE DEFINER=dummy VIEW v1 AS SELECT 1;  -- 不正なビューだがワーニング止まりなので転けはしない

なので、転けたり転けなかったりするという違いが生まれている模様。
たぶん、動作としては常に転けるのが正しいとは思う。

MySQLの論理バックアップにおける2段階のViewのリストア

TL;DR

• mysqldump とかが CREATE VIEW v1 AS SELECT 1 AS num, 1 AS val とかいう一見VIEW定義となんの関係もない CREATE VIEW 文を吐く理由

---

mysqldumpは単にアルファベット順にテーブルやビューの定義を取得するので、シンプルに直接 CREATE VIEW を書いてしまうと順番によってはリストアに転ける。

mysql80 38> CREATE TABLE t1 (num serial, val varchar(32));
Query OK, 0 rows affected (0.04 sec)

mysql80 38> INSERT INTO t1 VALUES (1, 'one'), (2, 'two');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql80 38> CREATE VIEW v1 AS SELECT * FROM t1;
Query OK, 0 rows affected (0.01 sec)

mysql80 38> CREATE VIEW a_v1 AS SELECT val FROM v1 WHERE num = 1;
Query OK, 0 rows affected (0.02 sec)

mysql80 38> SHOW TABLES;
+--------------+
| Tables_in_d2 |
+--------------+
| a_v1         |              <-- ビューv1を参照しているのでアルファベット順にリストアすると転ける
| t1           |
| v1           |
+--------------+
3 rows in set (0.00 sec)

mysql80 38> use d3
Database changed

mysql80 38> CREATE VIEW a_v1 AS SELECT val FROM v1 WHERE num = 1;
ERROR 1146 (42S02): Table 'd3.v1' doesn't exist

なのでこれを避けるために、

• Base Tableの CREATE TABLE と ダミーの CREATE VIEW を流す
• ↑が流れ終わった後に本物の CREATE VIEW を流す
  という2ステップで実行している。

$ mysqldump80 d2
..
--
-- Temporary view structure for view `a_v1`
--

DROP TABLE IF EXISTS `a_v1`;
/*!50001 DROP VIEW IF EXISTS `a_v1`*/;
/*!50001 CREATE VIEW `a_v1` AS SELECT
 1 AS `val`*/;

--
-- Table structure for table `t1`
--

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `num` bigint unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Temporary view structure for view `v1`
--

DROP TABLE IF EXISTS `v1`;
/*!50001 DROP VIEW IF EXISTS `v1`*/;
SET @saved_cs_client     = @@character_set_client;
/*!50001 CREATE VIEW `v1` AS SELECT
 1 AS `num`,
 1 AS `val`*/;

--
-- Final view structure for view `a_v1`
--

/*!50001 DROP VIEW IF EXISTS `a_v1`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `a_v1` AS select `v1`.`val` AS `val` from `v1` where (`v1`.`num` = 1) */;

--
-- Final view structure for view `v1`
--

/*!50001 DROP VIEW IF EXISTS `v1`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v1` AS select `t1`.`num` AS `num`,`t1`.`val` AS `val` from `t1` */;

..

1. 最初のTemporary view structureの時点では CREATE VIEW では固定値を列挙するだけなのでビューのビュー ( a_v1 )でも依存関係は問題はなくリストアできて
2. Final view structureの時点でダミーを DROP VIEW して本来の定義を CREATE VIEW することで復元できる、たとえ順番が入れ違って a_v1 が先にリストアされても、カラムの定義まではダミーの方で作成済みなのでビューのビューでもリストアできる

という仕組み。

意外とよくできてる。