Xtrabackup failed with below error :
2022-11-02T12:41:20.587275+05:30 0 [Note] [MY-012902] [InnoDB] Undo tablespace number 1 was being truncated when mysqld quit.
2022-11-02T12:41:20.587323+05:30 0 [ERROR] [MY-012903] [InnoDB] Cannot recover a truncated undo tablespace in read-only mode
2022-11-02T12:41:20.587351+05:30 0 [ERROR] [MY-011825] [Xtrabackup] xb_load_tablespaces() failed with error code 57
Server version: 8.0.28 MySQL Community Server - GPL
Xtrabackup Version : 8.0.29
Undo log report
mysql> SELECT * FROM INFORMATION_SCHEMA.FILES
→ WHERE FILE_TYPE LIKE ‘UNDO LOG’ \G
*************************** 1. row ***************************
FILE_ID: 4294958516
FILE_NAME: ./undo_001
FILE_TYPE: UNDO LOG
TABLESPACE_NAME: innodb_undo_001
TABLE_CATALOG:
TABLE_SCHEMA: NULL
TABLE_NAME: NULL
LOGFILE_GROUP_NAME: NULL
LOGFILE_GROUP_NUMBER: NULL
ENGINE: InnoDB
FULLTEXT_KEYS: NULL
DELETED_ROWS: NULL
UPDATE_COUNT: NULL
FREE_EXTENTS: 14
TOTAL_EXTENTS: 32
EXTENT_SIZE: 1048576
INITIAL_SIZE: 16777216
MAXIMUM_SIZE: NULL
AUTOEXTEND_SIZE: 16777216
CREATION_TIME: NULL
LAST_UPDATE_TIME: NULL
LAST_ACCESS_TIME: NULL
RECOVER_TIME: NULL
TRANSACTION_COUNTER: NULL
VERSION: NULL
ROW_FORMAT: NULL
TABLE_ROWS: NULL
AVG_ROW_LENGTH: NULL
DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: NULL
DATA_FREE: 18874368
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
STATUS: NORMAL
EXTRA: NULL
*************************** 2. row ***************************
FILE_ID: 4294956610
FILE_NAME: ./undo_002
FILE_TYPE: UNDO LOG
TABLESPACE_NAME: innodb_undo_002
TABLE_CATALOG:
TABLE_SCHEMA: NULL
TABLE_NAME: NULL
LOGFILE_GROUP_NAME: NULL
LOGFILE_GROUP_NUMBER: NULL
ENGINE: InnoDB
FULLTEXT_KEYS: NULL
DELETED_ROWS: NULL
UPDATE_COUNT: NULL
FREE_EXTENTS: 4
TOTAL_EXTENTS: 32
EXTENT_SIZE: 1048576
INITIAL_SIZE: 16777216
MAXIMUM_SIZE: NULL
AUTOEXTEND_SIZE: 16777216
CREATION_TIME: NULL
LAST_UPDATE_TIME: NULL
LAST_ACCESS_TIME: NULL
RECOVER_TIME: NULL
TRANSACTION_COUNTER: NULL
VERSION: NULL
ROW_FORMAT: NULL
TABLE_ROWS: NULL
AVG_ROW_LENGTH: NULL
DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: NULL
DATA_FREE: 20971520
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
STATUS: NORMAL
EXTRA: NULL
2 rows in set (0.35 sec)
We have also tried manually purge undo log:
set global innodb_undo_log_truncate=off
set global innodb_max_undo_log_size=50M
set global innodb_undo_log_truncate=ON
Before :
-rw-r----- 1 mysql mysql 704M Nov 2 11:56 undo_002
-rw-r----- 1 mysql mysql 368M Nov 2 11:56 undo_001
After :
-rw-r----- 1 mysql mysql 16K Nov 2 12:10 undo_2_trunc.log
-rw-r----- 1 mysql mysql 16M Nov 2 12:10 undo_002
-rw-r----- 1 mysql mysql 16K Nov 2 12:10 undo_1_trunc.log
-rw-r----- 1 mysql mysql 16M Nov 2 12:10 undo_001
No Luck still backup failed.
This is production server we will hardly get downtime for Instance Restart.
Any help would be highly appreciated.
Regards,
Jyoti Rajai