Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: xtrabackup备份mysql故障
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?
Is it inappropriate to ask MySQL questions in the TiDB community?
Is the storage engine correct, InnoDB?
There are plenty of resources available online for this kind of MySQL issue.
MySQL Corrupted Page Issue Analysis - Zhihu (zhihu.com)
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.
What version of xtrabackup are you using? Is it 2.4.x?
Tried both xtrabackup2.4.28 and the latest xtrabackup2.4.29, same error.
Sometimes ibd corruption might not be noticeable during runtime, but the logs will show it the next time the DB restarts.
I have also tried restarting the database, and there was no report of file corruption.
And the database restarts normally.
Can the error in the screenshot be copied as text?
[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.
It seems that the file is still corrupted, possibly with some local issues. Try using a different backup tool to back up the table.
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;
There are no issues with the logical backup.
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)
repair is for fixing the Myisam engine, right?
“repaire” is the InnoDB storage engine.
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
The storage engine for the table doesn’t support repair