How to Tell When All Is Well?

MySQL & MariaDB Percona XtraBackup
1

What markers or indications show that an xtrabackup operation completed correctly and the resulting backup is good?

I ask because the logs seem to have conflicting information. For example, in the following output, xtrabackup exited with code 0 and printed “completed OK!” on the screen, but there are some pretty scary error messages about a corrupt database.

~: xtrabackup: using the following InnoDB configuration for recovery:
~: 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 = 1073741824
~: InnoDB: PUNCH HOLE support available
~: InnoDB: Mutexes and rw_locks use GCC atomic builtins
~: InnoDB: Uses event mutexes
~: InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
~: InnoDB: Compressed tables use zlib 1.2.11
~: InnoDB: Number of pools: 1
~: InnoDB: Using CPU crc32 instructions
~: InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
~: InnoDB: Completed initialization of buffer pool
~: InnoDB: page_cleaner coordinator priority: -20
~: InnoDB: Highest supported file format is Barracuda.
~: InnoDB: Are you sure you are using the right ib_logfiles to start up the database? Log sequence number in the ib_logfiles is 3027505, less than the log sequence number in the first system tablespace file header, 3030706.
~: InnoDB: The log sequence number 3030706 in the system tablespace does not match the log sequence number 3027505 in the ib_logfiles!
~: InnoDB: Database was not shutdown normally!
~: InnoDB: Starting crash recovery.
~: InnoDB: Page [page id: space=0, page number=7] log sequence number 3030618 is in the future! Current system log sequence number 3027514.
~: InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recovery for information about forcing recovery.
~: InnoDB: Page [page id: space=0, page number=5] log sequence number 3030618 is in the future! Current system log sequence number 3027514.
~: InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recovery for information about forcing recovery.
~: InnoDB: Page [page id: space=0, page number=382] log sequence number 3027741 is in the future! Current system log sequence number 3027514.
~: InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recovery for information about forcing recovery.
~: InnoDB: Page [page id: space=0, page number=249] log sequence number 3028572 is in the future! Current system log sequence number 3027514.
~: InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recovery for information about forcing recovery.
~: InnoDB: Page [page id: space=0, page number=430] log sequence number 3029309 is in the future! Current system log sequence number 3027514.
~: InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recovery for information about forcing recovery.
~: InnoDB: Page [page id: space=0, page number=250] log sequence number 3030662 is in the future! Current system log sequence number 3027514.
~: InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recovery for information about forcing recovery.
~: InnoDB: Page [page id: space=0, page number=384] log sequence number 3030662 is in the future! Current system log sequence number 3027514.
~: InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recovery for information about forcing recovery.
~: InnoDB: Page [page id: space=0, page number=251] log sequence number 3030706 is in the future! Current system log sequence number 3027514.
~: InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recovery for information about forcing recovery.
~: InnoDB: Page [page id: space=0, page number=385] log sequence number 3030706 is in the future! Current system log sequence number 3027514.
~: InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recovery for information about forcing recovery.
~: InnoDB: Page [page id: space=0, page number=5] log sequence number 3030618 is in the future! Current system log sequence number 3027514.
~: InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recovery for information about forcing recovery.
~: InnoDB: xtrabackup: Last MySQL binlog file position 244268, file name mysql-bin.000003
~: InnoDB: Page [page id: space=0, page number=7] log sequence number 3030618 is in the future! Current system log sequence number 3027514.
~: InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recovery for information about forcing recovery.
~: InnoDB: Removed temporary tablespace data file: “ibtmp1”
~: InnoDB: Creating shared tablespace for temporary tables
~: InnoDB: Setting file ‘./ibtmp1’ size to 12 MB. Physically writing the file full; Please wait …
~: InnoDB: File ‘./ibtmp1’ size is now 12 MB.
~: InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
~: InnoDB: 32 non-redo rollback segment(s) are active.
~: InnoDB: Page [page id: space=0, page number=0] log sequence number 3028572 is in the future! Current system log sequence number 3027524.
~: InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recovery for information about forcing recovery.
~: InnoDB: 5.7.35 started; log sequence number 3027505
~: xtrabackup: starting shutdown with innodb_fast_shutdown = 1
~: InnoDB: FTS optimize thread exiting.
~: InnoDB: Starting shutdown…
~: InnoDB: Page [page id: space=0, page number=249] log sequence number 3028572 is in the future! Current system log sequence number 3027524.
~: InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recovery for information about forcing recovery.
~: InnoDB: Page [page id: space=0, page number=250] log sequence number 3030662 is in the future! Current system log sequence number 3027524.
~: InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recovery for information about forcing recovery.
~: InnoDB: Page [page id: space=0, page number=251] log sequence number 3030706 is in the future! Current system log sequence number 3027524.
~: InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recovery for information about forcing recovery.
~: InnoDB: Shutdown completed; log sequence number 3027524
~: 220213 22:30:10 completed OK!
~: xtrabackup exited with code: 0
~: result: ok

1 Like
2

Hi @geek_prophet .

We try to bail out the backup / prepare as soon as possible if we are 100% sure there will be an unrecoverable issue, however, there are issues such as corruption that is hard to enforce a bailout.
A corruption for example could be on a secondary index and by just rebuilding the index everything will be fine. So aborting the prepare is not the right thing to do.

Ideally you should restore and test the resulting backup (configure a slave, run query on necessary tables, run pt-table-checksum) to validate you can use the backup for the purpose you intended to.

1 Like