Percona-Cluster: backup and restore procedure

backup-restorationperconapercona-xtradb-cluster

I'm trying to restore a full backup taken from one node of a three-node percona cluster (percona cluster 5.5, galera 2.1, wsrep sst method is rsync, innodb tables only).

Backup was taken like this:

rm -rf /tmp/backup/mysqldb
innobackupex --user=bkpuser --password=xxxx --galera-info --no-timestamp /tmp/backup/mysqldb/
innobackupex --apply-log --use-memory=2G /tmp/backup/mysqldb/

The restore procedure I'm attempting is like this:

  1. shut down mysql on all three nodes
  2. on first node
    1. delete contents of mysql data dir
    2. delete mysql redologs/binlogs, doublewrite file etc (they are located in a separate folder)
    3. copy back database tablespace files for my database instance
    4. copy back database tablespace files for mysql database
    5. redologs/binlogs, doublewrite file, etc
    6. start mysql with wsrep_urls = gcomm:// to initialize cluster
  3. on second and third node
    1. delete mysql redologs/binlogs, doublewrite file etc
    2. remove galera.cache and grastate.dat
    3. start mysql (wsrep_urls = gcomm://firstnode:port,gcomm://secondnode:port,gcomm://thirdnode:port)
  4. once the cluster is all synced, restart first node with full wsrep_urls configuration

(I know wsrep_urls is deprecated, but I haven't gotten galera to work with wsrep_cluster_address yet.)

My problem is that the second and third nodes report errors after SST. For each table, I see this error:


130225 15:44:43 [ERROR] Cannot find or open table myTestDb/settings from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.

Mysql show tables shows the tables as existing, but when attempting to select from them, it reports error Table 'myTestDb.settings' does not exist …

I have tried to delete the local tablespace files before starting up mysql and requesting sst, same result.

How should I perform the restore? Should I copy back the backup files to all nodes?

Best Answer

You didn't say what SST method you are using. I presume it is xtrabackup?

The symptoms you describe on the 2nd and 3rd nodes sound like a missing (and re-initialized) Innodb data dictionary that lives in the ibdata1 file. It's possible that is not getting copied from the right place on the 1st node.

You mentioned some things like trx logs and doublewrite files in a "separate folder". What are your Innodb settings in your my.cnf? Do you, perchance, set innodb_data_home_dir (and it's outside your standard datadir)?

If so, it's likely this bug: https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1098566, or possibly one similar to it.

Related Topic