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がまともならちゃんと動くはずだということは分かった。
0 件のコメント :
コメントを投稿