I set up a Master-Master-replication with MariaDB 10.3.11 with enabled GTIDs and master_use_gtid=Slave_Pos. The two hosts (master1 and master2) have different domain-ids and different server_ids. Backup is configured on master2.

Now I have a situation where replication gets inconsistent when I restore master2 from it's backup if there had been transactions on master2 after taking the backup. The transaction between taking the backup and restoring it are lost on master2 although the GTIDs tell me otherwise. I wonder if master2 can somehow get them back from master1 which was actually my understanding of the concept of GTIDs: The slave tells the master the locally known GTIDs and gets back the missing event groups. However, I did not succeed. I thought log_slave_updates=ON might do the trick.

The actual background is to develop a robust backup-restore scenario.

Can anybody help to solve the use case of backup/restore properly?

In detail:

Both db servers are set up and replication is in a consistent state.

Now I create a backup on master2 using mariabackup:

mariabackup --backup --user=$MYSQL_USER --password=$MYSQL_PASSWORD --slave-info --safe-slave-backup --safe-slave-backup-timeout=120 --history --kill-long-queries-timeout=60 --kill-long-query-type=all --target-dir $TARGET_FOLDER

Let's say the backup contains the GTIDs 1-1-216,2-2-25 (as read from xtrabackup_binlog_info).

Execute some insert statements on master2.

Now I have a consistent set of GTIDs on both masters:

master1> select @@gtid_binlog_pos, @@gtid_current_pos, @@gtid_slave_pos;
+-------------------+--------------------+------------------+
| @@gtid_binlog_pos | @@gtid_current_pos | @@gtid_slave_pos |
+-------------------+--------------------+------------------+
| 1-1-217,2-2-29    | 1-1-217,2-2-29     | 1-1-217,2-2-29   |
+-------------------+--------------------+------------------+

master2> select @@gtid_binlog_pos, @@gtid_current_pos, @@gtid_slave_pos;
+-------------------+--------------------+------------------+
| @@gtid_binlog_pos | @@gtid_current_pos | @@gtid_slave_pos |
+-------------------+--------------------+------------------+
| 1-1-217,2-2-29    | 1-1-217,2-2-29     | 1-1-217,2-2-29   |
+-------------------+--------------------+------------------+

In a ficitve scenario I simulate a complete crash of master2 and delete /var/lib/mysql knowing I have a backup.

So I restore my backup:

mariabackup --copy-back --target-dir $backupFolder && chown -R mysql:mysql $mysqlDataDir

Reset the slave, set the GTIDs known in the backup and reset up the replication:

mysql -u$mysqlSuperUser --password="$mysqlSuperPassword" -e "RESET SLAVE;"
SET GLOBAL gtid_slave_pos = '1-1-216,2-2-25';
change master to master_host='master1', master_user='replication_user', 
master_password='bigs3cret', master_use_gtid=Slave_Pos;
START SLAVE;

Now show slave status\G tells me that I have a running replication. When I compare the GTIDs I get the impression that both servers should have the same state:

master1> select @@gtid_binlog_pos, @@gtid_current_pos, @@gtid_slave_pos;
+-------------------+--------------------+------------------+
| @@gtid_binlog_pos | @@gtid_current_pos | @@gtid_slave_pos |
+-------------------+--------------------+------------------+
| 1-1-217,2-2-29    | 1-1-217,2-2-29     | 1-1-217,2-2-29   |
+-------------------+--------------------+------------------+

master2> select @@gtid_binlog_pos, @@gtid_current_pos, @@gtid_slave_pos;
+-------------------+--------------------+------------------+
| @@gtid_binlog_pos | @@gtid_current_pos | @@gtid_slave_pos |
+-------------------+--------------------+------------------+
| 1-1-217           | 1-1-217,2-2-29     | 1-1-217,2-2-29   |
+-------------------+--------------------+------------------+

However the transactions I executed between backup and restore are lost.

Edit: On master2 the binlogs are lost, but on master1 the missing transactions are present in the binlog:

# at 25794
#181130  9:50:19 server id 2  end_log_pos 25836 CRC32 0xf01e07b4        GTID 2-2-29 trans
/*!100001 SET @@session.gtid_seq_no=29*//*!*/;
BEGIN
/*!*/;
# at 25836
# at 25908
#181130  9:50:19 server id 2  end_log_pos 25908 CRC32 0xf1353326        Annotate_rows:
#Q> insert into t1 values (concat(now(), ' master2'))
#181130  9:50:19 server id 2  end_log_pos 25962 CRC32 0x6bb0ac14        Table_map: `replicateme`.`t1` mapped to number 183

How can this be solved? Thanks for any help.

Configuration master1:

[mariadb]
log-bin
server_id=1
log-basename=master1
gtid-domain-id=1
binlog_format                  = ROW
log_slave_updates=ON
relay_log_recovery=ON
relay_log_info_repository=TABLE
master_info_repository=TABLE
slave_exec_mode=IDEMPOTENT
auto-increment-increment = 2 
auto-increment-offset = 1

Configuration master2:

[mariadb]
log-bin
server_id=2
log-basename=sdc1-lx-maria2
gtid-domain-id=2
binlog_format                  = ROW
log_slave_updates=ON
relay_log_recovery=ON
relay_log_info_repository=TABLE
master_info_repository=TABLE
slave_exec_mode=IDEMPOTENT
auto-increment-increment = 2 
auto-increment-offset = 2

migrated from stackoverflow.com Nov 30 at 13:39

This question came from our site for professional and enthusiast programmers.

  • Did you lose any binlogs in the process? – Rick James Nov 30 at 22:14

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.