Mysql – Percona/MySql Table does not exist after innobackupex restore on Vagrant Ubuntu 16.04

awsMySQLperconaUbuntu

This question is cross posted here from stack overflow at the suggestion of stack overflow commenters (https://stackoverflow.com/questions/47086329/percona-mysql-table-does-not-exist-after-innobackupex-restore-on-vagrant-ubuntu)

I have an odd problem that doesn't make any sense to me. I have a mysql (percona 5.7) galera cluster that consists of 3 nodes.

Full backups are taken regularly using:
/usr/bin/innobackupex --user=${MYSQLROOT} --password=${MYSQLPASS} --no-timestamp ${BACKUP_PATH}${BACKUP_DIRNAME} --parallel=4

This works great an I have tested restoring on production with the following sequence of steps, and the restore works perfectly. The production server nodes are AWS instances based on Ubuntu 16.04 amd64. I have also tested the restore script on other AWS instances with the same setup as prod and it works perfectly:

#!/bin/bash

service mysql stop

s3cmd sync s3://${S3BUCKET}/${NEWEST_BACKUP_DIR} full_backup.tar.gz

tar -xzf full_backup.tar.gz

rm -rf /var/lib/mysql/*
innobackupex --apply-log --redo-only db_backup_full/

innobackupex --apply-log db_backup_full/
innobackupex --copy-back db_backup_full/

chown -R mysql:mysql /var/lib/mysql

service mysql bootstrap-pxc

Everything works as expected so far, now in order to do some local development work, I created a Vagrant config that builds a Ubuntu 16.04 amd64 node using the exact same Ansible role that was used to build the production server. Then when I run my restore script above on my local Vagrant instance it seems like the backup file is downloaded and restored correctly. The /var/lib/mysql/ directory on my local instance looks the same as the /var/lib/mysql/ directory on my production nodes. When I login into mysql I can see all my tables when I use mysql> show tables;. However, when i try to query a table I get the following error:

mysql> select * from Record limit 100;
ERROR 1146 (42S02): Table 'franchisedirect.Record' doesn't exist
mysql>

My research tells me that the issue is that you need the ib* files in the root of the MySQL datadir (e.g. ibdata1, ib_logfile0 and ib_logfile1).

However, when I look in /var/lib/mysql/ those files are present I can't see how they would not be there as the exact same restore script works on the AWS instances..

Am I missing something here ? Is this a Vagrant issue, a Percona/MySql issue, a innobackupex issue, Ubuntu 10.04 issue ? Im completely stumped, Thanks!

Best Answer

I figured out the answer if anyone else is reading this question. I think it's a bug with either vagrant or virtual box, basically, rebooting the virtual machine fixed the issue. Its like the restore data didn't get fully saved to disk and wasn't available until after the reboot.. I would be interested if anyone had a similar issue with virtual box or vagrant...