Home / server / Questions / 482264
In Process
Hank
Asked: 2013-02-26 07:05:44 +0800 CST

Percona-Cluster: backup and restore procedure

  • 772

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?

backup-restoration

2 Answers

  1. 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.

    • 1

  2. It cost me much time to find out the answer. If you read the innobackup.backup.log from the donor node and observe the data being created in the adding node, you will get some idea why this happen.

    I have exactly the same problem. And for my case, comment out the customized innodb_data_home_dir and innodb_log_group_home_dir value will fix the problem.

    After synced with group, just enable those value and copy file.

    Hope this helps.

    • 1