Partial restoration during move of a database from a master to another master

Hi there,

I want to move a database from a replication master to another replication master too (all nodes are using gtid for a week). My plan includes to make the second master temporarily slave of the first one for the said database to keep the data in-sync until the switch-over on the application side.

Untitled Diagram

  1. I’m using for backup xtrabackup / xbstream but it comes to restoration I can’t restore the database because xtrabackup insists the mysql datadir to be emptied, as also worth mentionning, it replaces the files ibdata1, ib_logfile0 and ib_logfile1.
root@bmm-3:~# xtrabackup --copy-back --target-dir=/opt/mysql_random/ --databases my_db
....
Original data directory /var/lib/mysql is not empty!

I’m used to xtrabackup to restore a full backup (to re-sync a broken slave for instance) as described by the page How to setup a replica for replication in 6 simple steps with Percona XtraBackup but I don’t find how can I restore one single new database on a running cluster.
I used Restoring Individual Tables to restore all tables of the database

  • I’ve imported first the table structure (using mysqldump -d)
  • did a SELECT CONCAT( 'ALTER TABLE ', a.TABLE_SCHEMA, '.', a.TABLE_NAME, ' DISCARD TABLESPACE;' ) FROM information_schema.tables a WHERE a.TABLE_SCHEMA='my_db'; to discard the table file
  • move the tables files under /var/lib/mysql/my_db`
  • Reload files with SELECT CONCAT( 'ALTER TABLE ', a.TABLE_SCHEMA, '.', a.TABLE_NAME, ' IMPORT TABLESPACE;' ) FROM information_schema.tables a WHERE a.TABLE_SCHEMA='my_db';
    At the end, any SELECT on any table of the DB would tell me the table is empty.

Did I forget something ?

  1. As I’m using GTIDs in all masters and slaves setup, can I use that to make the sync between masters too ?

Thanks for your help

mysql version: 5.7.33
xtrabackup version: 2.4.3

1 Like

Hello @bmmalc,
In order to use xtrabackup to restore a single database to another already running machine, you must do a table-by-table restore using transportable tablespaces feature of InnoDB. Copy the backup to something like /var/backups/ on new server, run xtrabackup --prepare --export on this backup. Then you log into mysql, create all of the tables in a new database, discard all the tablespaces, copy the exported files from /var/backups/ to mysql datadir, then import the tables, etc, etc. It is a LONG TEDIOUS process. This is not simple.

If you want to restore the ENTIRE database server, xtrabackup is extremely simple to use. But doing a single table, or single database is difficult.

1 Like

Hi @matthewb

I think I’ve done all the steps you gave as I follow the documentation linked step by step, that why I don’t understand I got the error I described in the question.

ps: I’m giving a try to mysql shell load/dump

1 Like

You’re trying to use --copy-back which you CANNOT use when doing individual database restores. --copy-back is for restoring an ENTIRE database server which is not what you are trying to do.

1 Like

Oh I did not spot that --copy-back was for full restore.
thanks for the advice, I’m trying to find alternative to xtrabackup.

Best

1 Like

For what you are trying to do, you won’t find such an alternative other than logical dump/restore. For that, GitHub - maxbube/mydumper: Official mydumper project is king.

Because of the way InnoDB is designed, at its core, you cannot just copy .ibd files like you can with MyISAM. You must follow the proper tablespace import process and perform it one-table-at-a-time like I described above.

1 Like

Thanks for the suggestion Matthew.
I took the opportunity of this setup (I’m doing some slaves cascading with DB FILTERING) to try mysql-shell dump and import functionality. That’s quite fast so I’ll stick with it.

Best.

1 Like

Nice! You’re a rare breed; most people don’t bother with the shell.

1 Like

I was discussing on the mysql-community slack and Frédéric Descamps told me all the goodness of mysql-shell on backup functionality.

1 Like

Yep, he works for Oracle and is a big fan of mysqlshell.

1 Like