Hello, we use xtrabackup 2.4.29 with Percona mysql servers 5.7.44 to distribute copy from master server to replicas. Often enought after restore base on replica we found error messages, about one or another table in mysql database are corrupted - sometimes it’s proc, sometimes users. By the way, all work’s good, but i just can’t do REPAIR table on replicas, coz they run in super_readonly mode. Is it normal behavior of xtrabackup and myisam engine tables, or do we something wrong?
Thanks a lot.
Hello @Deniska80,
You can run SET super_readonly=0; REPAIR TABLE XXX; SET super_readonly=1 for this one issue. No, it is not normal behavior on this. However, it depends on how you are executing PXB. Please provide the whole command. You might want to do a replica rebuild from a logical backup instead of a physical backup as a test. Also, upgrading to MySQL 8 will remove this issue completely because the system tables are InnoDB in MySQL 8 which are crash resilient like all other InnoDB tables.
You can run
SET super_readonly=0; REPAIR TABLE XXX
Of course, but this approach has some disadvantages - this replica has it’s own operations.
Please provide the whole command.
just as simple as: xtrabackup --backup --target-dir=backupdir and xtrabackup --prepare --target-dir=backupdir
do a replica rebuild from a logical backup
you mean from mysqldump? Db is about 1.2Tb size, last time i try to restore biggest table from mysqldump and drop operation after 12 hours of its working.
The replica should not have any WRITE activity other than replication itself. Which means it is safe to run this repair. Also, this repair is on a system table which should be 99.99999% idle.
Any errors or othe rmessages in the output?
Never use mysqldump 
Use mydumper. It’ll be much, much faster. I would only do a logical dump/restore of the mysql system tables. The restore process will DROP the table and recreate it which should remove any corruption issues.
The replica should not have any WRITE activity other than
replication itself.
I mean REPAIR TABLE produce operations on replica, i cheked Executed_Gtid_Set before and after REPAIR TABLE and saw, that value for replica @@server_uuid changes. That’s create problems later (after some time, when binlog for that changes allready removes, and we try to use this replica as source for another one).
Any errors or othe rmessages in the output?
no errors, just some warnings, but they are not critical, as i understand
xtrabackup: warning: Log block checksum mismatch (block no 187835034 at lsn 90805880828416):
xtrabackup: warning: this is possible when the log block has not been fully written by the server, will retry later.
xtrabackup: warning: Log block checksum mismatch (block no 207752874 at lsn 90816078762496):
xtrabackup: warning: this is possible when the log block has not been fully written by the server, will retry later.
The restore process will DROP the table and recreate it
yes, and this again create some local operations for replica.