# Partial restoration during move of a database from a master to another master **URL:** \ **Category:** Percona XtraBackup\ **Created:** [August 4, 2021, 8:33am UTC](https://forums.percona.com/t/partial-restoration-during-move-of-a-database-from-a-master-to-another-master/11649 "2021-08-04T08:33:27Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Baptiste\_Mille-Mathi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/baptiste_mille-mathi/32/9237_2.png) [@Baptiste\_Mille-Mathi](https://forums.percona.com/u/Baptiste_Mille-Mathi)\ **Post date:** [August 4, 2021, 8:33am UTC](https://forums.percona.com/t/partial-restoration-during-move-of-a-database-from-a-master-to-another-master/11649/1 "2021-08-04T08:33:27Z")
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](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/3/3223a5c2663c1d5eba154cfe1b669d7365253e8c.png) 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`. ```auto 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 - Percona XtraBackup](https://www.percona.com/doc/percona-xtrabackup/2.4/howtos/setting_up_replication.html#step-2-copy-backed-up-data-to-the-replica) but I don’t find how can I restore one single new database on a running cluster. I used [Restoring Individual Tables - Percona XtraBackup](https://www.percona.com/doc/percona-xtrabackup/2.4/xtrabackup_bin/restoring_individual_tables.html) 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` ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 4, 2021, 2:15pm UTC](https://forums.percona.com/t/partial-restoration-during-move-of-a-database-from-a-master-to-another-master/11649/2 "2021-08-04T14:15:14Z")
Hello @Baptiste_Mille-Mathi, 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. ---
**Author:** ![Baptiste\_Mille-Mathi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/baptiste_mille-mathi/32/9237_2.png) [@Baptiste\_Mille-Mathi](https://forums.percona.com/u/Baptiste_Mille-Mathi)\ **Post date:** [August 4, 2021, 2:39pm UTC](https://forums.percona.com/t/partial-restoration-during-move-of-a-database-from-a-master-to-another-master/11649/3 "2021-08-04T14:39:37Z")
Hi @matthewb > [@matthewb](#): > > 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. 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 ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 4, 2021, 2:57pm UTC](https://forums.percona.com/t/partial-restoration-during-move-of-a-database-from-a-master-to-another-master/11649/4 "2021-08-04T14:57:45Z")
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. ---
**Author:** ![Baptiste\_Mille-Mathi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/baptiste_mille-mathi/32/9237_2.png) [@Baptiste\_Mille-Mathi](https://forums.percona.com/u/Baptiste_Mille-Mathi)\ **Post date:** [August 5, 2021, 12:11pm UTC](https://forums.percona.com/t/partial-restoration-during-move-of-a-database-from-a-master-to-another-master/11649/5 "2021-08-05T12:11:02Z")
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 ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 5, 2021, 2:21pm UTC](https://forums.percona.com/t/partial-restoration-during-move-of-a-database-from-a-master-to-another-master/11649/6 "2021-08-05T14:21:10Z")
For what you are trying to do, you won’t find such an alternative other than logical dump/restore. For that, [GitHub - mydumper/mydumper: Official MyDumper project](https://github.com/maxbube/mydumper) 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. ---
**Author:** ![Baptiste\_Mille-Mathi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/baptiste_mille-mathi/32/9237_2.png) [@Baptiste\_Mille-Mathi](https://forums.percona.com/u/Baptiste_Mille-Mathi)\ **Post date:** [August 6, 2021, 5:52am UTC](https://forums.percona.com/t/partial-restoration-during-move-of-a-database-from-a-master-to-another-master/11649/7 "2021-08-06T05:52:30Z")
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. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 6, 2021, 4:49pm UTC](https://forums.percona.com/t/partial-restoration-during-move-of-a-database-from-a-master-to-another-master/11649/8 "2021-08-06T16:49:21Z")
Nice! You’re a rare breed; most people don’t bother with the shell. ---
**Author:** ![Baptiste\_Mille-Mathi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/baptiste_mille-mathi/32/9237_2.png) [@Baptiste\_Mille-Mathi](https://forums.percona.com/u/Baptiste_Mille-Mathi)\ **Post date:** [August 23, 2021, 9:02am UTC](https://forums.percona.com/t/partial-restoration-during-move-of-a-database-from-a-master-to-another-master/11649/9 "2021-08-23T09:02:10Z")
I was discussing on the mysql-community slack and Frédéric Descamps told me all the goodness of mysql-shell on backup functionality. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 23, 2021, 2:10pm UTC](https://forums.percona.com/t/partial-restoration-during-move-of-a-database-from-a-master-to-another-master/11649/10 "2021-08-23T14:10:33Z")
Yep, he works for Oracle and is a big fan of mysqlshell.