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:
[INDENT][xtrabackup]
compress
compress-threads=4
host=localhost
user=backup
password=backuppass
target_dir=/data/backups
[xtrabackup_decompress_decrypt]
decompress
decompress-threads=4[/INDENT]
The backup is taken with:
[INDENT]xtrabackup --backup --stream=xbstream > /data/backups/2016-09-26_01-54-10.xbstream
[/INDENT]
The backup is then restored with these commands:
[INDENT]$ 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/
[/INDENT]
[INDENT] [/INDENT]
However, we’re getting some ibdata1/log file position mismatch errors and MySQL is not starting (restoring to same server): [INDENT]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.[/INDENT]
Does anyone have any ideas on whether the restore procedure we’re using is valid? Thanks, Matt