MySQL Master-Master with offsets getting ERROR 1872

Question

I'm trying to fix the replication between a master-master MySQL database replication model.

Server 1 (with the IP of 192.168.2.6) is configured with:

auto-increment-increment       = 2
auto-increment-offset          = 1

So it writes only odd primary key auto-incrementing IDs.

Server 2 (with the IP of 192.168.2.7) is configured with:

auto-increment-increment       = 2
auto-increment-offset          = 2

So it writes only even primary key auto-incrementing IDs.

I've been told that this was working for some time, but since I've been hired, it has been in failure to sync status. I'm operating under the assumption that this is possible.

My job is to get it working in this manner. I've done the following steps to try to get them to sync:

Server 1:
    mysql>
    RESET MASTER;
    # First time only:
    #GRANT REPLICATE SLAVE ON *.* TO 'replicate'@192.168.2.7 IDENTIFIED BY '[password_redacted]';
    #FLUSH PRIVILEGES;
    FLUSH TABLES WITH READ LOCK;
    SHOW MASTER STATUS;

    bash#
    mysqldump -u root -p -ceQRE --hex-blob --triggers --opt --single-transaction --comments --dump-date --no-autocommit --all-databases -r /root/master.sql
    scp -P 5934 /root/master.sql root@192.168.2.7:.

    mysql>
    UNLOCK TABLES;

Server 2:
    mysql>
    STOP SLAVE;

    bash#
    mysql -u root -p < /root/master.sql

    mysql>
    # First time only:
    #GRANT REPLICATE SLAVE ON *.* TO 'replicate'@192.168.2.6 IDENTIFIED BY '[password_redacted]';
    #FLUSH PRIVILEGES;
    RESET SLAVE;
    CHANGE MASTER TO MASTER_HOST='192.168.2.6', MASTER_USER='replicate', MASTER_PASSWORD='[password_redacted]', MASTER_LOG_FILE='ibm.000001', MASTER_LOG_POS=28556380;
    START SLAVE;
    SHOW SLAVE STATUS\G

Server 1:
    mysql>
    STOP SLAVE;
    CHANGE MASTER TO MASTER_HOST='192.168.2.7', MASTER_USER='replicate', MASTER_PASSWORD='[password_redacted]', MASTER_LOG_FILE='ibm.000004', MASTER_LOG_POS=121875;
    CHANGE REPLICATION FILTER REPLICATE_DO_DB = (reminderdental2), REPLICATE_IGNORE_DB = (mysql);
    START SLAVE;

Each time it says it syncronizing but the row counts never come into conformity. After some time, I'll get the error:

Slave SQL for channel '': Slave failed to initialize relay log info structure from the repository, Error_code: 1872
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

I've also tryed Percona XtraBackup to get a binary-backup/restore in order to kick it off the first time.

I've considered moving to GTID replication but have heard the performance is not as good. I'm interested in both solutions to get it working in this way as well as feedback regarding performance of a 2-9 server cluster using GTID.

Server 1's version is:

5.7.21-0ubuntu0.16.04.1-log (Ubuntu)
percona-xtrabackup-24/unknown,now 2.4.9-1.xenial amd64 [installed]

Serer 2's version is:

5.7.21-log MySQL Community Server (GPL)
percona-xtrabackup-24, 2.4.9.1.el7

What versions of MySQL and Percona XtraBackup are you running? There are MySQL bugs recorded for that error, such as this one https://bugs.mysql.com/bug.php?id=77496 but that particular one is marked fixed — greenweeds, Mar 27 '18 at 10:08

score 4 · Answer 1 · answered Dec 12 '18 at 10:22

4

On MySQL > 6 you should use also

mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;

Should be the same, but using documented command is better than tweaking into system tables. See also https://dev.mysql.com/doc/refman/8.0/en/reset-slave.html

answered Dec 12 '18 at 10:22

Alessandro Carini

312
2
13

2

This worked for me. I had to follow it up with a "CHANGE MASTER TO MASTER_HOST=..." command, but this did work. My issue is that I changed my /etc/hostname file, rebooted, and then my replication broke. This fixed it once I ran the change master command. – Maximus Nov 16 '20 at 18:51

score 1 · Answer 2 · answered Sep 21 '18 at 12:28

If you are getting the following error:

[ERROR] Slave SQL for channel 'group_replication_applier': Slave failed to initialize relay log info structure from the repository, Error_code: 1872

Here is the solution:

From the mysql console:

USE mysql;
TRUNCATE TABLE slave_relay_log_info;

Than restart the mysql:

service mysql restart

Hope this works for you guys too!

score -1 · Answer 3 · answered Jul 18 '19 at 09:48

-1

I had the same issue, in my case the reason was missing relay file on the slave. There were some entries with filenames in the "index" relay file, but one of that entries was missing. I removed that entry, then after restart the replication started. Cheers.

answered Jul 18 '19 at 09:48

Piotr

1

1

Hi, welcome on serverfault. Sorry but your answer seems really far away from original poster's question : he talks about master master replication – bgtvfr Jul 18 '19 at 10:14

MySQL Master-Master with offsets getting ERROR 1872

3 Answers3