0

I am trying to backup data on a MySQL Galera node with XtraBackup. Unfortunatelly the tool tells me, that ibdata1 is corrupt. After double checking I indeed found some myisam tables I have importet by error. Droped those and replaced them by innodb tables. However, the error remains (though on a different page now)

xtrabackup version 2.2.12 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 8726828)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/data
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
>> log scanned up to (22054624442)
xtrabackup: Generating a list of tablespaces
[01] Copying ./ibdata1 to /data/backup/2015-09-02_15-23-50/ibdata1
[01] xtrabackup: Database page corruption detected at page 1320, retrying...

I tripple checked corruption on the database and could not find anyhthing. Is there probably a setup problem with Galera and XtraBackup?

merlin
  • 2,033
  • 11
  • 37
  • 72
  • as far as I remember Xtrabackup verifies a checksum of each page while it's copying a tablespace. Can you confirm the checksum of page 1320 is good with `innochecksum`? – akuzminsky Sep 02 '15 at 16:31
  • That would have been a good thing to check. In the meentime I did backup all dbs with mysqldump , shutdown mysql cluster, removed the ibdata1 file & logs, restarted and recovered from backup. Now XtraBackup runs on the db without complaining. Looks like the db has really been corrupted without noticing. – merlin Sep 02 '15 at 18:25
  • Just for references: If ibdata1 gets removed on MySQL 5.6 and recreated, 5 tables will be missing! One has to restore those tables manually: http://dba.stackexchange.com/questions/54608/innodb-error-table-mysql-innodb-table-stats-not-found-after-upgrade-to-mys – merlin Sep 02 '15 at 20:23
  • ibdata1 stores InnoDB dictionary, so that's expected – akuzminsky Sep 02 '15 at 20:35
  • Somehow ibdata1 is corrupt again. I get following error after innochecksum: Fail; page 1088 invalid (fails log sequence number check). Is there a way to recover from this? – merlin Sep 02 '15 at 20:44
  • If it's just LSN (which might be a result of crashed InnoDB) then InnoDB will recover the page itself. If checksum is wrong too, I would check why it gets right after you re-created it. Hardware problems, etc . – akuzminsky Sep 02 '15 at 20:58
  • by the way, was MySQL running? On the running MySQL you might be checking while InnoDB was writing to the page. – akuzminsky Sep 02 '15 at 20:59
  • This is a MySQL Galera Cluster of 3. The problem apeared after I have imported a table with 1M rows. I have now redone the process, no errors so far. Let's see if it stays the same. – merlin Sep 02 '15 at 22:30

1 Answers1

1

This may be caused by a few different things.

  • You are running an old xtrabackup version which has a bug that may flag for corruption in the data files when there is none, update to the newest xtrabackup version for your mysql version.

  • There is corruption in the secondary indexes in a table

If you're able to perform a mysqldump of the tables the data should be intact and you need to either run optimize table or rebuild the tables. Identify ibd file corruption by checking each ibd file with innochecksum and use the pt-online-schema-schema-change application to rebuild the tables

pt-online-schema-change --dry-run --alter="ENGINE=INNODB" \
    --user=your_username --ask-pass \
    D=databasename,t=tablename

Replace the --dry-run with --execute when you want to perform the task.

  • Could be caused by encryption or compression set to the table

Optimize table: https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html

Innochecksum: https://www.percona.com/blog/2015/03/16/deep-dive-mysqls-innochecksum-tool/

pt-online-schema-change: https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

JazzCat
  • 165
  • 1
  • 8