4

I am working as a remote DBA for some of the production servers.

There was replication from Master to Slave.

There is something changed in the scenario they have made one of the Slaves as master now..

when i saw the error log of the machine which is master now I got an error.I was unable to understand what is this..

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.

but all are working well now.Please help to understand what was that.

Thanks..!

Abdul Manaf
  • 9,587
  • 16
  • 73
  • 84

1 Answers1

2

Here is something interesting about MySQL Replication:

When it comes to Master/Slave Replication

  • Master DB Server must have server-id defined in my.cnf
  • Master DB must have binary logging enabled (define log-bin)
  • Slave DB Server is not required to defined server-id in my.cnf

If, all of a sudden, you find yourself having to reverse the DB Server's roles (making the old Slave the new Master, and making the old Master the Slave), the requirement to define server-id and log-bin must also hold true.

BTW a DB Server without server-id being set has a default value of 1. If server-id was define as 1 on the Master and the Slave did not have server-id defined, the Slave would also have had server-id being equal to 1. Activating the Slave as the new Master with any trace remnants of the old replication setup wmay have attempted to complete the processing of any remaining SQL entries in the relay logs. Those entries carried the server-id of 1. Bringing those entries over to a new machine without server-id define would have caused mysqld to complain about processing relay logs entries with the same server-id.

Perhaps, someone forgot to define the server-id on the Slave before making it the new Master and trying to engage Replication afterwards. Adding server-id to my.cnf and a quick restart of mysqld would have eliminated this weird situation without giving it a second thought.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536