MySQL 5.5: Problem restoring a database backup

j4nd3r53nj4nd3r53n ContributorCurrent User Role Poster
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?

Comments

  • j4nd3r53nj4nd3r53n Contributor Current User Role Poster
    Grrr, shouldn't the tags ensure that several line of code keep their formatting? (fixed, as you can see)
  • j4nd3r53nj4nd3r53n Contributor Current User Role Poster
    The solution to this problem is very simple, and it is in fact mentioned in the documentation, but not in the section about restoring, where it would have been easier to spot. Just do this before trying to import the tablespaces:
    set global innodb_import_table_from_xtrabackup=1
    
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.