I am struggling with restoring a backup of a MySQL 5.5 database, which has been created with innobackupex --databases … This is how I set up mysql and prepare the backup for restore:
# install Percona:
DEBIAN_FRONTEND=noninteractive /usr/bin/apt-get -q -y install percona-server-server-5.5
/usr/bin/percona-release enable-only tools release
/usr/bin/apt-get update
DEBIAN_FRONTEND=noninteractive /usr/bin/apt-get -q -y install percona-xtrabackup-24
# Change mysql config:
cat > /etc/mysql/conf.d/mysql.cnf <<!
[mysql]
[mysqld]
innodb_file_per_table = 1
!
systemctl restart mysql
# unpack and restore backup
cd /var/lib/mysql
mkdir restore
cd restore
/usr/bin/aws s3 cp s3://something.db.backup/2017-09-20-something.tar.gz - | tar zxvf -
/usr/bin/innobackupex --apply-log --export $(pwd)
/bin/chown -R mysql: *
I now have to go through the tables one by one, create them, discard the tablespace, copy the backup tables files and finally import the tablespace - this is the part of my script, which should restore the databases:
for i in $(ls -d */)
do
(
cd $i
db=$(echo $i | sed 's#/$##g')
/usr/bin/mysql -e "create database $db"
for j in *frm
do
table=$(echo $j | sed 's/.frm//g')
/usr/bin/dbsake frmdump $j > $j.sql
/usr/bin/mysql $db < $j.sql
/usr/bin/mysql $db -e "alter table $table discard tablespace;"
mv $table.* ../../$db/
/usr/bin/mysql $db -e "alter table $table import tablespace;"
done
)
done
Concentrating on one table, it produces the following in the error log:
...
191022 15:24:34 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.62-38.14' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Percona Server (GPL), Release 38.14, Revision 7e0e1cc
191022 15:30:05 InnoDB: Error: tablespace id and flags in file './mydatabase/wheely_user_game.ibd' are 2483 and 0, but in the InnoDB
InnoDB: data dictionary they are 10 and 0.
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
191022 15:30:05 InnoDB: cannot find or open in the database directory the .ibd file of
InnoDB: table `mydatabase`.`wheely_user_game`
InnoDB: in ALTER TABLE ... IMPORT TABLESPACE
Surely this is a known and fixable issue? What do I need to do?