Many times I successfully performed procedure described in the https://mariadb.com/kb/en/setting-up-a-replication-slave-with-mariabackup/ – it’s fastest and easiest because replica is restored by copying files and binlog coordinates for CHANGE MASTER are saved in xtrabackup_binlog_info file.
With multi-source replication it seems to be not possible, because both backups from masters contain their own ibdata1 file – so I cannot restore them into single instance of MySQL (the future slave).
The only way I can think of is like that:
- restore 1st master into slave
- enable replication for selected databases from 1st master
- restore 2nd master into second slave (temporary)
- use mysqldump to move selected databases from temporary slave into main slave
The problem is that databases on both masters are large (~1 TB) and mysqldump/restore takes ages – and chasing few days of stalled replication (between backup and restore) takes additional significant amount of time. I’d really like to avoid this.
I know that with MyISAM tables I could just move table files but it’s innodb and it cannot be changed.
Maybe there’s some way to merge ibdata1 files? Or maybe export/import ibdata1 dictionaries related to chosen databases?