0

Following situation:

I have a MySQL Server (running Percona Server 5.7) set up as a replication slave. This server should hold only a partial replication. To get to this I made a partial backup of another identical slave and applied it to the server (using innobackupex). This is running fine with no problems at all.

The problem that i have now:

There are a few tables that are "slave-specific" that i have to create. Unfortunately when i try to create one of these tables, mysql only creates the tablespace but stops after this, telling me that the table already exists. But in a "show tables" the tables don't show up.

In the mysql data directory only the .ibd files are created for said tables. Deleting these files and trying again doesn't help.

Is there any place where InnoDB saves it's tables that i don't know of? The information_schema database was copied with the backup so maybe there is something left in it but I do not know where that could be.

Improve this question
4
  • Hi there. There's probably more information needed to get any definitive help, but here are some pointers for you. Are you using Percona XtraBackup and if so is it version 2.4? With 5.7, that would be needed. On this assumption, you'll see that the documentation suggests that restoration recommends using import rather than --copy-back Anyway have a look here and see if that sheds any light? percona.com/doc/percona-xtrabackup/LATEST/innobackupex/…
    – greenweeds
    Commented Dec 28, 2017 at 14:36
  • As I mentioned, I used innobackupex for the backup. It's a tool provided by Percona and it uses xtrabackup in it's backend. It has a few extra features for InnoDB databases. And as i said the backup itself is not really the problem. This went through without any problems and the replication is just running fine. Only recreating the empty tables which were excluded from the backup doesn't work. I somewhat found out that in the table information_schema.innodb_sys_tables the old tables still show up. even though they don't exist anymore. Unfortunately it is not possible to edit this table.
    – elmo
    Commented Dec 29, 2017 at 11:57
  • Explain what you mean by "partial backup" and "partial restore". Moving some files (as opposed to the entire tree) can lead to problems such as you describe.
    – Rick James
    Commented Dec 29, 2017 at 19:02
  • making a partial backup is a supported function of innobackupex (and thus also by xtrabackup) it just copies the databases and tables that you want. and i don't move or copy any files by hand. it's all done by the backup program. and as i said the backup itself works perfectly.
    – elmo
    Commented Jan 2, 2018 at 9:49

1 Answer 1

Reset to default
0

I solved this by making a complete backup and then truncating the tables that need to be empty on the slave. Partial backup didn't work unfortunately.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.