Restoring table from xtrabackup -- how to synchronize between cluster members?

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi folk! I’ve got and XtraDB Cluster running with two DB servers and one Galera arbitrator. I am not running in true dual master – as I have a VIP that only points to one of the two DB servers. (still gunshy about that and haven’t needed it) So for all practical purposes it’s a master/slave setup but fully capable of doing dual master.

So, I’m using xtrabackup with Bareos to perform regular nightly backups and that’s been working great. However I’m running into some isms when I’m attempting to perform a restore. In this case, I’m only aiming to restore a single table, and not to it’s original location. I do backups and restores from the slave of the cluster.

What I’m doing is:

  1. Prepare the restore with it’s incrementals
  2. Recreate the table on the slave in a “bareos_restores” db that I have created, using the .frm file pulled from the restore and dbsake
  3. Hop onto the slave, and discard the tablespace of the table I just created
  4. Copy the .ibd file (and really whatever else aside from the .frm, but all I have in this case is a .ibd file) into place with the new table name
  5. Back on the slave, import tablespace
  6. Yay! the slave is looking good

Ok so here’s where thing get complicated. The slave now has a properly restored copy of the table. The master does not. It has an empty table from when I did the create table in step 2, but no data has made it in after the import tablespace.

So, ok, guessing xtradb cluster doesn’t acknowledge the import tablespace. So I went looking for a solution and came across pt-table-sync. I attempted to pt-table-sync from slave to master. Well as you can probably imagine, that only sort of worked, as it ended up causing a wsrep deadlock.

So I’m not real sure how to proceed from here. I need the table to be sync’ed after import tablespace. Any ideas?

One thing I will mention is that I am not explicitly setting innodb_import_table_from_xtrabackup or innodb_expand_import. I am not fully clear on what the benefit of that is. Also I’m running the latest XtraDB Cluster 5.7 series.

Thanks! I’m hoping either I can get some pointers or something will magically come to me soon. =)

2

Hi,
Indeed Galera won’t “replicate” the table space import operation. You’d have to repeat the same import table space on each cluster node (while making sure the table isn’t written yet on any node).
You may also try this approach (haven’t tested on PXC myself):
[url]https://www.percona.com/blog/2014/09/17/syncing-mysql-slave-table-with-pt-online-schema-change/[/url]

3

Ok thank you! I’m glad I wasn’t just losing my mind. I’ll take a look at pt-online-schema-change but if that doesn’t work, I have a few other ideas to try after sleeping on it. I can’t tell whether pt-online-schema-change would have worked because hangs head in shame the table in question doesn’t have a primary key. (well I mean it has a hidden primary key that was auto-created, but not one I specified) Anyway, thanks again!