0

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 [email protected]:.

    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
1
  • 1
    What versions of MySQL and Percona XtraBackup are you running? There are MySQL bugs recorded for that error, such as this one bugs.mysql.com/bug.php?id=77496 but that particular one is marked fixed
    – greenweeds
    Mar 27, 2018 at 10:08

4 Answers 4

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

1
  • 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, 2020 at 18:51
1

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!

0

I know this question is old, it happened to me today, and what worked for me was a combination of Alessandro Carini and user260223 answers:

in mysql console with root user:

mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;

and then:

USE mysql;
TRUNCATE TABLE slave_relay_log_info;

then restarted mysql service

sudo service mysql restart

set master info:

CHANGE MASTER TO MASTER_HOST='[your master host/IP]',
MASTER_USER='[your master replication user]',
MASTER_PASSWORD='[your master replication password]',
MASTER_LOG_FILE='[your master file]',
MASTER_LOG_POS=[your master pos];

then

START SLAVE;

and check everything ok with:

SHOW SLAVE STATUS\G;
-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.

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, 2019 at 10:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .