Categories
Database Development

MySQL / MariaDB – Start multi-source replication with mariabackup (xtrabackup)

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?

Leave a Reply

Your email address will not be published. Required fields are marked *