0

Trying to setup MySQL to run one-way replication. I have the master set to server_id = 1, a replication user setup, binary logging enabled, the Slave server_id = 2 and is connected and waiting for an event. I am also using Workbench.

However, the Master server has the following 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).

I can't figure out any reason for this error. I have looked at dozens of tutorials, manuals, etc and none of them is explaining to me what is happening.

FryarTuck
  • 1
  • 1
  • 1
  • How have you set the server_id on each master/slave? If you changed the config file, did you restart mysql after this? What does select @@global.server_id on master an slave show? – danblack Feb 26 '19 at 02:24
  • possible duplicate https://dba.stackexchange.com/questions/9756/mysql-thinks-master-slave-have-the-same-server-id – Ahmad Abuhasna Feb 26 '19 at 11:51
  • I appreciate the notice Ahmad, but I have referenced that before and it did not solve my problem. – FryarTuck Feb 26 '19 at 15:06
  • I set the server-id using the my.ini file and I have also enabled logging through the my.ini file by specifying the log-bin base value. I restarted the MySQL service after those modifications. – FryarTuck Feb 26 '19 at 15:08
  • @danblack "select @@global.server_id" on master results in a 1. When ran on the slave it results in a 2. – FryarTuck Feb 26 '19 at 15:18

3 Answers3

0

The server-id is stored in the /var/lib/mysql/auto.cnf in addition to the standard my.cnf config file.

When cloning the data directory from master to slave, you need to delete the auto.cnf file in the data directory (i.e. /var/lib/mysql) before starting the slave.

user154826
  • 1
  • Thank you for the comment! I am doing this on windows and so our directories may be different but I only see auto.cnf in the directory C:\ProgramData\MySQL\MySQL Server 8.0\Data. The file also only has a couple of lines in it showing "[auto]" and the "server-uuid". – FryarTuck Feb 26 '19 at 15:13
  • There is no risk in deleting the auto.cnf file. Stop MySQL, delete the file, and Restart MySQL. That may resolve your issue. – user154826 Feb 27 '19 at 20:15
0

If your master set to server_id = 1, and your Slave server_id = 2 and it isn´t work it, You need to change the service_id but directly on Workbench. So you need to go to Administration and select the option 'Options File', later in the tab 'General' serch the 'informational' check the server-id and change it if you are in the master set to server_id=1, if you are in the slave set to server_id=2 and finally aply the change. Reset the service MySQL and now try to do the connection master-slave again. I did it and my connection master-slave worked. I am Mexican so I know my redaction is not the best, but I hope you can understand me.enter image description here

Alejandro Sánchez
  • 1
0

for those who using docker to test replication(2 container on same host), don't use 127.0.0.1 or localhost when issuing CHANGE MASTER, it's container's address not host's, so MySQL is complaining this error even server_id is different on those 2 instances. check this to see how to use host's address

tinlyx
  • 3,540
  • 13
  • 46
  • 72
D3Hunter
  • 101
  • 2