TL;DR

Percona XtraBackup 8.0.29 and INSTANT ADD/DROP Columns - Percona Database Performance Blog がほぼ全て
xb 8.0.29でMySQL 8.0.29のバックアップを取ろうとした時に、1つでもINSTANT COLUMN(ADDまたはDROP)が存在するとエラーになる


 $ xtrabackup --version

2022-07-20T18:36:05.410933+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --server-id=1 --log_bin=mysql-bin --log-bin-index=bin.index --innodb_buffer_pool_size=60G --innodb_file_per_table=1 --innodb_flush_method=O_DIRECT

xtrabackup version 8.0.29-22 based on MySQL server 8.0.29 Linux (x86_64) (revision id: c31e7ddcce3)

INSTANT COLUMNが無ければ、Vanilla MySQL 8.0.29のバックアップが取れる。

$ xtrabackup -S /usr/mysql/8.0.29/data/mysql.sock -uroot --backup
2022-07-20T18:39:23.707951+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --server-id=1 --log_bin=mysql-bin --log-bin-index=bin.index --innodb_buffer_pool_size=60G --innodb_file_per_table=1 --innodb_flush_method=O_DIRECT
2022-07-20T18:39:23.708199+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --user=root --socket=/usr/mysql/8.0.29/data/mysql.sock --user=root --backup=1
xtrabackup version 8.0.29-22 based on MySQL server 8.0.29 Linux (x86_64) (revision id: c31e7ddcce3)
220720 18:39:23  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/usr/mysql/8.0.29/data/mysql.sock' as 'root'  (using password: NO).
220720 18:39:23  version_check Connected to MySQL server
220720 18:39:23  version_check Executing a version check against the server...
220720 18:39:23  version_check Done.
2022-07-20T18:39:23.807722+09:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: /usr/mysql/8.0.29/data/mysql.sock
2022-07-20T18:39:23.812076+09:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.29
2022-07-20T18:39:23.815319+09:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2022-07-20T18:39:23.818088+09:00 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise().
2022-07-20T18:39:23.818165+09:00 0 [Note] [MY-011825] [Xtrabackup] cd to /usr/mysql/8.0.29/data/
2022-07-20T18:39:23.818196+09:00 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 0, set to 81920
2022-07-20T18:39:23.818276+09:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration:
2022-07-20T18:39:23.818297+09:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2022-07-20T18:39:23.818312+09:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend

..

2022-07-20T18:39:34.555754+09:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
2022-07-20T18:39:34.557365+09:00 0 [Note] [MY-011825] [Xtrabackup] The latest check point (for incremental): '32821155948'
2022-07-20T18:39:34.557413+09:00 0 [Note] [MY-011825] [Xtrabackup] Stopping log copying thread at LSN 32821156733
2022-07-20T18:39:34.557518+09:00 1 [Note] [MY-011825] [Xtrabackup] Starting to parse redo log at lsn = 32821155894
2022-07-20T18:39:34.557794+09:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (32821156812)
2022-07-20T18:39:35.559968+09:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK INSTANCE
2022-07-20T18:39:35.560323+09:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked
2022-07-20T18:39:35.560364+09:00 0 [Note] [MY-011825] [Xtrabackup] Backup created in directory '/home/yoku0825/xtrabackup_backupfiles/'
2022-07-20T18:39:35.560378+09:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename 'mysql-bin.000120', position '197', GTID of the last change '9079d03d-dcd3-11ec-8628-fa163f8f0da3:1-198063'
2022-07-20T18:39:35.560505+09:00 0 [Note] [MY-011825] [Xtrabackup] Writing /home/yoku0825/xtrabackup_backupfiles/backup-my.cnf
2022-07-20T18:39:35.560576+09:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /home/yoku0825/xtrabackup_backupfiles/backup-my.cnf
2022-07-20T18:39:35.561428+09:00 0 [Note] [MY-011825] [Xtrabackup] Writing /home/yoku0825/xtrabackup_backupfiles/xtrabackup_info
2022-07-20T18:39:35.561513+09:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /home/yoku0825/xtrabackup_backupfiles/xtrabackup_info
2022-07-20T18:39:36.562474+09:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (32821155948) to (32821156822) was copied.
2022-07-20T18:39:36.781992+09:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

いつの間にかxbも MY- の番号つけるようになっていたのね(何か親近感を感じる番号だ)

フツーにバックアップは取れたので、INSTANT COLUMNを足してみる。

$ mysql80 d1
mysql80 86> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) COLLATE utf8mb4_ja_0900_as_cs DEFAULT NULL,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.01 sec)

mysql80 86> ALTER TABLE t1 ADD col INT;  -- ALGORITHM=INSTANTを省略してもINSTANT ADD COLUMNになる
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 86> SELECT * FROM information_schema.INNODB_TABLES WHERE total_row_versions > 0;
+----------+-------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME  | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+-------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
|    22441 | d1/t1 |   33 |      6 | 21380 | Dynamic    |             0 | Single     |            0 |                  1 |
+----------+-------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.01 sec)

$ rm -r xtrabackup_backupfiles/

$ xtrabackup -S /usr/mysql/8.0.29/data/mysql.sock -uroot --backup
2022-07-20T18:43:20.923483+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --server-id=1 --log_bin=mysql-bin --log-bin-index=bin.index --innodb_buffer_pool_size=60G --innodb_file_per_table=1 --innodb_flush_method=O_DIRECT
2022-07-20T18:43:20.923747+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --user=root --socket=/usr/mysql/8.0.29/data/mysql.sock --user=root --backup=1
xtrabackup version 8.0.29-22 based on MySQL server 8.0.29 Linux (x86_64) (revision id: c31e7ddcce3)
220720 18:43:21  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/usr/mysql/8.0.29/data/mysql.sock' as 'root'  (using password: NO).
220720 18:43:21  version_check Connected to MySQL server
220720 18:43:21  version_check Executing a version check against the server...
220720 18:43:21  version_check Done.
2022-07-20T18:43:21.010844+09:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: /usr/mysql/8.0.29/data/mysql.sock
2022-07-20T18:43:21.015998+09:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.29
2022-07-20T18:43:21.020044+09:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2022-07-20T18:43:21.023131+09:00 0 [ERROR] [MY-011825] [Xtrabackup] Found tables with row versions due to INSTANT ADD/DROP columns
2022-07-20T18:43:21.023271+09:00 0 [ERROR] [MY-011825] [Xtrabackup] This feature is not stable and will cause backup corruption.
2022-07-20T18:43:21.023352+09:00 0 [ERROR] [MY-011825] [Xtrabackup] Please check https://docs.percona.com/percona-xtrabackup/8.0/em/instant.html for more details.
2022-07-20T18:43:21.023429+09:00 0 [ERROR] [MY-011825] [Xtrabackup] Tables found:
2022-07-20T18:43:21.023513+09:00 0 [ERROR] [MY-011825] [Xtrabackup] d1/t1
2022-07-20T18:43:21.023586+09:00 0 [ERROR] [MY-011825] [Xtrabackup] Please run OPTIMIZE TABLE or ALTER TABLE ALGORITHM=COPY on all listed tables to fix this issue.

あっさりわかりやすく転けた。
エラーメッセージにも書いてあるけど、INSTANT COLUMNはOPTIMIZE TABLEするとINSTANTじゃないカラムになるので、OPTIMIZE。

mysql80 89> OPTIMIZE TABLE d1.t1;
+-------+----------+----------+-------------------------------------------------------------------+
| Table | Op       | Msg_type | Msg_text                                                          |
+-------+----------+----------+-------------------------------------------------------------------+
| d1.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| d1.t1 | optimize | status   | OK                                                                |
+-------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.02 sec)

$ xtrabackup -S /usr/mysql/8.0.29/data/mysql.sock -uroot --backup

..
2022-07-20T18:44:57.129630+09:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

逆にDROP COLUMNの方。

mysql80 96> SELECT * FROM information_schema.INNODB_TABLES WHERE total_row_versions > 0;
Empty set (0.00 sec)

mysql80 96> ALTER TABLE d1.t1 DROP COLUMN col;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 96> SELECT * FROM information_schema.INNODB_TABLES WHERE total_row_versions > 0;
+----------+-------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME  | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+-------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
|    22442 | d1/t1 |   33 |      5 | 21381 | Dynamic    |             0 | Single     |            0 |                  1 |
+----------+-------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.01 sec)

$ xtrabackup -S /usr/mysql/8.0.29/data/mysql.sock -uroot --backup
2022-07-20T18:45:53.616425+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --server-id=1 --log_bin=mysql-bin --log-bin-index=bin.index --innodb_buffer_pool_size=60G --innodb_file_per_table=1 --innodb_flush_method=O_DIRECT
2022-07-20T18:45:53.616691+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --user=root --socket=/usr/mysql/8.0.29/data/mysql.sock --user=root --backup=1
xtrabackup version 8.0.29-22 based on MySQL server 8.0.29 Linux (x86_64) (revision id: c31e7ddcce3)
220720 18:45:53  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/usr/mysql/8.0.29/data/mysql.sock' as 'root'  (using password: NO).
220720 18:45:53  version_check Connected to MySQL server
220720 18:45:53  version_check Executing a version check against the server...
220720 18:45:53  version_check Done.
2022-07-20T18:45:53.707364+09:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: /usr/mysql/8.0.29/data/mysql.sock
2022-07-20T18:45:53.712051+09:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.29
2022-07-20T18:45:53.714724+09:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2022-07-20T18:45:53.717792+09:00 0 [ERROR] [MY-011825] [Xtrabackup] Found tables with row versions due to INSTANT ADD/DROP columns
2022-07-20T18:45:53.717842+09:00 0 [ERROR] [MY-011825] [Xtrabackup] This feature is not stable and will cause backup corruption.
2022-07-20T18:45:53.717856+09:00 0 [ERROR] [MY-011825] [Xtrabackup] Please check https://docs.percona.com/percona-xtrabackup/8.0/em/instant.html for more details.
2022-07-20T18:45:53.717886+09:00 0 [ERROR] [MY-011825] [Xtrabackup] Tables found:
2022-07-20T18:45:53.717909+09:00 0 [ERROR] [MY-011825] [Xtrabackup] d1/t1
2022-07-20T18:45:53.717921+09:00 0 [ERROR] [MY-011825] [Xtrabackup] Please run OPTIMIZE TABLE or ALTER TABLE ALGORITHM=COPY on all listed tables to fix this issue.

同じエラーでアウト。
このエラーを吐いてるところは

https://github.com/percona/percona-xtrabackup/blob/percona-xtrabackup-8.0.29-22/storage/innobase/xtrabackup/src/backup_mysql.cc#L2364-L2401

なので、MySQL 8.0.29以上かつ SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0 でマッチする行があるとエラるようになっている(MySQL 8.0.28とそれ以前にはそもそも total_row_versions カラムがない)

クラッシュリカバリがおかしいのをPercona Serverでは直した、Vanillaではまだ、ってことらしいので、MySQL側でも直ってくれることを祈りつつ。

日々の覚書

GA

2022/07/20

xtrabackup 8.0.29とINSTANT COLUMNと

TL;DR

0 件のコメント :

コメントを投稿