Xtrabackup Backup MySQL Failure

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: xtrabackup备份mysql故障

| username: wenyi

xtrabackup backup mysql5.7.40, reports that the corresponding file is corrupted, but the table has no issues with read and write operations at the database level. Has anyone encountered this before?

2
| username: WalterWj | Original post link

Is it inappropriate to ask MySQL questions in the TiDB community?

3
| username: Jolyne | Original post link

Is the storage engine correct, InnoDB?

4
| username: 像风一样的男子 | Original post link

There are plenty of resources available online for this kind of MySQL issue.
MySQL Corrupted Page Issue Analysis - Zhihu (zhihu.com)

5
| username: wenyi | Original post link

The key point is that I suspect the file is not damaged. If the file were damaged, the table should not be able to read and write normally.

6
| username: ShawnYan | Original post link

What version of xtrabackup are you using? Is it 2.4.x?

7
| username: wenyi | Original post link

Tried both xtrabackup2.4.28 and the latest xtrabackup2.4.29, same error.

8
| username: ShawnYan | Original post link

Sometimes ibd corruption might not be noticeable during runtime, but the logs will show it the next time the DB restarts.

9
| username: wenyi | Original post link

I have also tried restarting the database, and there was no report of file corruption.

10
| username: wenyi | Original post link

And the database restarts normally.

11
| username: ShawnYan | Original post link

Can the error in the screenshot be copied as text?

12
| username: wenyi | Original post link

[03] xtrabackup: Error: failed to read page after 10 retries. File ./aier_center_log/avis_logs.ibd seems to be corrupted.
[03] xtrabackup: Error: xtrabackup_copy_datafile() failed.
[03] xtrabackup: Error: failed to copy datafile.

13
| username: zhanggame1 | Original post link

It seems that the file is still corrupted, possibly with some local issues. Try using a different backup tool to back up the table.

14
| username: vcdog | Original post link

You can try using commands to repair it:

Using the REPAIR TABLE Command

This is a common method for repairing tables. The command will check all rows and indexes of the table and attempt to fix any issues found.

REPAIR TABLE table_name;

Using the CHECK TABLE Command

Before executing REPAIR TABLE, you might want to check the health of the table first.

CHECK TABLE table_name;
15
| username: wenyi | Original post link

There are no issues with the logical backup.

16
| username: wenyi | Original post link

mysql> repair table avis_logs;
±--------------------------±-------±---------±--------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
±--------------------------±-------±---------±--------------------------------------------------------+
| aier_center_log.avis_logs | repair | note | The storage engine for the table doesn’t support repair |
±--------------------------±-------±---------±--------------------------------------------------------+
1 row in set (0.00 sec)

17
| username: wenyi | Original post link

repair is for fixing the Myisam engine, right?

18
| username: vcdog | Original post link

“repaire” is the InnoDB storage engine.

19
| username: vcdog | Original post link

Using the myisamchk Tool

For tables using the MyISAM storage engine, you can also use the myisamchk tool to repair them.

Execute the following command in the command line:

myisamchk -r /path/to/mysql/data/db_name/table_name.MYI

Using the mysqlcheck Tool

mysqlcheck is a command-line tool that can check, repair, analyze, or optimize MySQL tables.

Copy and execute:

mysqlcheck -r database_name table_name
20
| username: wenyi | Original post link

The storage engine for the table doesn’t support repair