Not the answer you need?
Register and ask your own question!

Validating backup & restore procedure

mjuszczakmjuszczak EntrantInactive User Role Beginner
We are looking to validate our restore procedure from an xbstream-based backup, because it doesn't seem to be well documented, and we're having some corruption issues during our test restores.

The backup configuration is:
[xtrabackup]
compress
compress-threads=4
host=localhost
user=backup
password=backuppass
target_dir=/data/backups

[xtrabackup_decompress_decrypt]
decompress
decompress-threads=4

The backup is taken with:
xtrabackup --backup --stream=xbstream > /data/backups/2016-09-26_01-54-10.xbstream
The backup is then restored with these commands:
$ sudo service mysql stop

# Extract the backup to /tmp/restore
$ mkdir -p /tmp/restore
$ xbstream -x -v -C /tmp/restore < /data/backups/2016-09-26_01-54-10.xbstream

# Decompress the backup files, remove compressed versions
$ pushd /tmp/restore
$ for i in `find . -iname "*\.qp"`; do qpress -do $i > $(dirname $i)/$(basename $i .qp) && rm -f $i; done
$ popd

# Move the backup files to back to datadir
$ sudo rsync -avzh /tmp/restore/ /data/mysql/
$ sudo chown -R mysql: /data/mysql
$ sudo service mysql start

# if all goes well, delete /tmp/restore
$ rm -rf /tmp/restore/
However, we're getting some ibdata1/log file position mismatch errors and MySQL is not starting (restoring to same server):
Oct 13 14:25:27 test mysqld: 2016-10-13 14:25:27 140163571337152 [Note] InnoDB: The log sequence numbers 770055386153 and 770055386153 in ibdata files do not match the log sequence number 1144662140864 in the ib_logfiles!
Oct 13 14:25:27 test mysqld: 2016-10-13 14:25:27 140163571337152 [Note] InnoDB: Database was not shutdown normally!
Oct 13 14:25:27 test mysqld: 2016-10-13 14:25:27 140163571337152 [Note] InnoDB: Starting crash recovery.
Oct 13 14:25:27 test mysqld: 2016-10-13 14:25:27 140163571337152 [Note] InnoDB: Reading tablespace information from the .ibd files...
Oct 13 14:25:27 test mysqld: 2016-10-13 14:25:27 140163571337152 [Note] InnoDB: Restoring possible half-written data pages
Oct 13 14:25:27 test mysqld: 2016-10-13 14:25:27 140163571337152 [Note] InnoDB: from the doublewrite buffer...
Oct 13 14:25:27 test mysqld: InnoDB: Error: trying to access page number 3636950279 in space 0,
Oct 13 14:25:27 test mysqld: InnoDB: space name ./ibdata1,
Oct 13 14:25:27 test mysqld: InnoDB: which is outside the tablespace bounds.
Oct 13 14:25:27 test mysqld: InnoDB: Byte offset 0, len 16384, i/o type 10.
Oct 13 14:25:27 test mysqld: InnoDB: If you get this error at mysqld startup, please check that
Oct 13 14:25:27 test mysqld: InnoDB: your my.cnf matches the ibdata files that you have in the
Oct 13 14:25:27 test mysqld: InnoDB: MySQL server.
Does anyone have any ideas on whether the restore procedure we're using is valid? Thanks, Matt
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.