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.
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’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 ?
As I’m using GTIDs in all masters and slaves setup, can I use that to make the sync between masters too ?
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.
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.
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.
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.
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.
