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!
