Mysql – Why does XtraBackup tell me the MySQL database is corrupt

backupgaleraMySQLxtrabackup

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?

Best Answer

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

Related Topic