Hi there:
I use xtrabackup to backup mysql database,sometimes I get error as below:
[01] xtrabackup: Database page corruption detected at page 135592, retrying…
[01] xtrabackup: Database page corruption detected at page 135592, retrying…
[01] xtrabackup: Database page corruption detected at page 135592, retrying…
[01] xtrabackup: Database page corruption detected at page 135592, retrying…
[01] xtrabackup: Database page corruption detected at page 135592, retrying…
[01] xtrabackup: Database page corruption detected at page 135592, retrying…
[01] xtrabackup: Database page corruption detected at page 135592, retrying…
[01] xtrabackup: Database page corruption detected at page 135592, retrying…
[01] xtrabackup: Database page corruption detected at page 135592, retrying…
241209 01:04:51 >> log scanned up to (742656176634)
[01] xtrabackup: Error: failed to read page after 10 retries. File ./unit_activity_center/ua_meeting_record_user.ibd seems to be corrupted.
[01] xtrabackup: Error: xtrabackup_copy_datafile() failed.
[01] xtrabackup: Error: failed to copy datafile.
Sometimes it happens,not everytime,and page number this time is different than next times’,the corrupt table it shows is also diffenrent.
My xtrabackup version is percona-xtrabackup-24-2.4.29-1.el9.x86_64
My mysql database version is 5.7.38
Would you please help me to check it?
thanks in advance.
Best Regards
Calvin
Hello @calvin_zhang
Sometimes it happens,not everytime,and page number this time is different than next times’,the corrupt table it shows is also diffenrent.
My xtrabackup version is percona-xtrabackup-24-2.4.29-1.el9.x86_64
My mysql database version is 5.7.38
Would you please help me to check it?
thanks in advance.
1st you should check MySQL error log to see if corruption is in the source, try to perform select on the problematic table. If all is good, you can share the xtrabackup command for further review.
This could be a bug as mentioned here:
https://perconadev.atlassian.net/browse/PXB-3252
The issue was in the bug report is “with encryption enabled, database corruption is detected while taking incremental backup. This happens when encryption is enabled|disabled for tablespaces while backup is been taken.”
Are you using encryption?
Thanks for your reply,I checked MySQL error log as below,there is no error in this log
My xtrbackup command is as below
xtrabackup --defaults-file=/etc/my.cnf --backup --no-timestamp --throttle=9 --user=$user_name --password=$password --socket=$socket --safe-slave-backup --incremental-basedir=$backdir/incr3 --target-dir=$backdir/incr4
I don’t use encryption for tablespaces
I perform a select query on the problematic table,it’s normal
Hello @calvin_zhang,
I would perform a slow shutdown, SET GLOBAL innodb_fast_shutdown=0, then stop MySQL. Once stopped, erase the redo logs on disk (rm ib_logfile0 ib_logfile1). Start MySQL. This will force InnoDB to create brand new logs. Try a backup and see if it continues to error.
Hi @matthewb,
Thanks for your advice,I tried as you told,yesterday’s backup is OK,I’ll continue to pay close attention to my backup,apprectiate to help me if have issue again!
Hi @matthewb ：
Yesterday’s backup failed ,can you help me to check again? thanks!
Hey @calvin_zhang,
Since the issue came back after creating brand new redo logs, I would go through each table and run ALTER TABLE foo ENGINE=InnoDB This will force InnoDB to create a brand new tablespace file on disk and copy the contents. Any page faults should be fixed automatically, or the system will crash with an error on reading one of the bad pages. I suggest you take a full logical backup (mysqldump or mydumper) beforehand.
1 Like
Hi @matthewb ：
Sorry for replying you so late,I did nothing but it worked well recently,I have no idea why,if the issue comes back,I’ll do as you told above,thanks again!
Hello @matthewb
Since I backup on my slave node,if I do as you told,it will affect the replication,should I run ALTER TABLE foo ENGINE=InnoDB on master node?look forward to your reply,thanks
You can run it on the replica only, if that’s the only place the corruption occurs. I hope you understand that you need to run the ALTER TABLE for each table in every database. This will take time. it will affect replication, but replication will catch back up after each ALTER finishes.
Hi @matthewb
Yesterday,I rebuilt master-replica on my replica node where I backup failed ,today’s backup still failed,I think master node also have page corruction,what’s your suggestion,should I run the ALTER TABLE for each table in every database on master node?
How did you rebuild the replica? If you used mysqldump/mydumper, you should not have any page corruption on the replica because every data file would be brand new. If you used PXB to take the backup from source, and copied that to replica for restore, then yes, the source must also have some corruption. In that case, yes, you need to run ALTER TABLE for each on source. Or, take a logical backup with mydumper, completely erase source and rebuild source brand new with this backup.
