3

I've been setting up a master->slave_a->slave_b relationship and having a little trouble. I currently have the master->slave_a replication working fine.

Each of the two slaves have exactly the same configuration, other than the hostname, server-id setting. When ever I try and start the slave on slave_b I have the following error pop up:

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

I really don't understand, because I have all of the server IDs miles apart 1->2626->2629 (m->s->s).

I'm setting them in my /etc/mysql/conf.d/replication.cnf file - and it seems to be being included, but I have no idea how I can check which server id mysql is picking up.

Thanks in advance :) Tom.

  • We need to set server id in my.cnf file. Could you please check this link hackosis.com/how-to-setup-mysql-replication for more references. – Praveen Prasannan Dec 27 '11 at 9:59
  • I've already configured this. Other settings are being taken into account from that same config file so I'm sure it must be picking it up... "because I have all of the server IDs miles apart 1->2626->2629 (m->s->s)." – tarnfeld Dec 27 '11 at 16:10
2

The server_id of the Master is recorded in its binary logs along with each query it executed to completion. To see those server_id values in the binary log, you run mysqlbinlog against any binary log.

Since Master -> Slave_A works fine, here is what you can do to clean things up between Slave_A and Slave_B:

On Slave_A, run the following in mysql:

mysql> STOP SLAVE;
mysql> RESET MASTER;

On Slave_A, run the following in the OS:

$ echo "STOP SLAVE;" > /root/MyData.sql
$ mysqldump -u... -p... --master-data=1 --routines --triggers --all-databases >> /root/MyData.sql
$ echo "START SLAVE;" >> /root/MyData.sql
$ mysql -hSlave_B -u... -p... -A < /root/MyData.sql

On Slave_A, run the following in mysql:

mysql> START SLAVE;

This will get Slave_B to stare at the correct values for server_id, and also reestablish MySQL Replication.

BTW You need to make sure binary logging is disabled on Slave_B since it is not necessary for a Slave unless Slave_B is going to a Master to some other Slave.

1

In my case (master-slave) I solved it deleting this file

C:\ProgramData\MySQL\MySQL Server 5.7\Data\auto.cnf

and then restarting MySQL service, when MySQL service starts will generate a new UUID

0

I was struggling with this issue for sometime where master shows error

Master & Slave were set up correctly with different IPs & different "server-id" in my.ini


Relay_Master_Log_File: log-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:   
.
.
.    
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
Last_SQL_Errno: 0

ISSUE:
Slave SQL_Thread was running on master server as well...(Not sure how :( )

FIX:
run "stop slave;" on MASTER

POST ACTIONS
Make sure replication is still working by verify slave status (on SLAVE)

In my case, above fixed the issue and master-slave replication also worked fine after that.

0

Fleshing out Chumillas' solution on Linux looked like the following. All of the commands were run on the slave, no database reload necessary, no master restart, and the whole process takes just a few seconds:

mysql> STOP SLAVE;

$ sudo service mysql stop

$ sudo mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak

$ sudo service mysql start
0

I did face the similar issue with the same error. The solution was to correct the PublicIP while creating a salve in the "CHANGE MASTER TO MASTER_HOST" query.

The same error does appear when you use the same IP of the slave as a masterIP while creating a slave. Hence, the MySql slave server tends to point to the same server IP (Slave IP rather than the master IP) forming a conflict of same server ID

0

I got the same error but it was short-sightedness on my part. The server that I am now using as a slave was at one time the master. So once I attempted to start the slave the show slave status 'master_host' value defaulted to that server i/p, not the new master i/p. So I reran the 'change master' command adding the 'master_host' pointing to the new master. DUH!! Maybe this might help somebody down the road.

-1

Remove auto.cnf copied from the master's datadir. Restart mysqld.

  • 1
    Hi welcome to the site. How does this add to the already existing answers by Chumillas and Jay Huggins? Can you elaborate how and why this would work? – Tom V - try topanswers.xyz Apr 16 '18 at 7:57
  • Hi by pointing out the reason why the slave's UUID was the same as the master's. auto.cnf copied from the master. – lainatnavi Apr 16 '18 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.