MariaDB/MySQL backup replication



  • I have currently two DB instanced with two-way replication. Each replicates other database. So both are master and slave. So every change in any of these databases is automatically replicated to the other. This works fine.

    But now I need one read-only backup solution, which will have also a whole database. When I replicate just the first instance of my original DB, my new backup has just changed done on this instance all changes from the second one are missing.

    So probably the only way is multi-source (multi-master) replication. This is possible by (MaridDB syntax)

    CHANGE MASTER "A" TO MASTER_HOST = '192.168.8.2', MASTER_USER = 'replicator', MASTER_PASSWORD = '...', master_use_gtid = slave_pos;
    CHANGE MASTER "B" TO MASTER_HOST = '192.168.8.3', MASTER_USER = 'replicator', MASTER_PASSWORD = '...', master_use_gtid = slave_pos;
    

    And I need to setup multiple gtid_slave_pos by

    SET GLOBAL gtid_slave_pos = "1-1-19124092,2-2-7060816";
    

    And this is a problematic part.

    I'm using this syntax to initial clone of the whole database to 3rd server

    sudo mysqldump --gtid --single-transaction --master-data dbName | pv | gzip > db-dump-fixed.sql.gz
    

    It automatically put there SET GLOBAL gtid_slave_pos='2-2-7027154';

    But how I can get the current gtid_slave_pos for the second database instance? It must be exactly the same state as it was at the moment of sudo mysqldump execute. If I will do it manually I can miss some transactions in the second database.



  • I recommend you enable log_slave_updates on both of your masters. Then they will add the replicated changed to their own binary log, so both instances' binary logs will have all changes. Then you don't need multi-master replication.

    See https://mariadb.com/kb/en/replication-and-binary-log-system-variables/#log_slave_updates




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2