Mysql – Error 1236 From Master After Restored Replication

error handlingMySQLpercona-serverreplication

I've been trying to restore a broken replication caused by power interruption. upon checking the slaveDB, it was already broken so I setup a new replication. And upon supposedly completing the replication, i'm stuck with this: Got fatal error 1236 from master when reading data from binary log: 'Misconfigured master – server id was not set'

We are running Percona Server 5.5.
Here were the procedures i made:

1.) on the Master Server masterDB (IP = 10.0.0.20, server_id = 1),I ran innobackupex to make a hot backup. checked the xtrabackup_binlog_info and noted slavedb-bin.000609 and 113535960 as the binlog file and position.

2.) Restored the backup to the slaveDB (IP = 10.0.0.40), and setup the replication as follows — CHANGE MASTER TO MASTER_HOST='10.0.0.20', MASTER_USER='user',MASTER_PASSWORD='password',MASTER_LOG_FILE='slavedb-bin.000609',MASTER_LOG_POS=113535960 (as noted from the xtrabackup_binlog_info from the innobackupex backup.

3.) After Starting the Slave, i get the error 1236 above with a misconfigured master, but the master server_id is 1.

I've read online on an old thread that a possible solution would be to go to the next binlog file since it might have been corrupted with the transactions applied.

But how would i go about moving to the next binlog file? Is it the next binlog from the innobackupex xtrabackup_binlog_info?" or should i check the master log and find out the last binlog written before it crashed?

I tried moving past the binlog from what innobackupex gave me, going to slavedb-bin.000610 and POS=0, but it still got the same error.

Best Answer

This is a guess, but please note that the option server-id cannot be activated in a hot way. In my experience, this is a common cause of problems when setting up the replication for the first time, specially because the master accepts the following syntax without complaining:

mysql> SET GLOBAL server_id = 1;
Query OK, 0 rows affected (0.04 sec)

And the server id seems to have taken effect:

mysql> SHOW GLOBAL VARIABLES like 'server\_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.02 sec)

But it doesn't work, (it does not have effect until restart and read from the my.cnf or passed as a parameter). The slave will complain until the master is restarted.

The official documentation is misleading, while it says it is dynamic in reality it is not (or at least it is not when setup for the first time, or setup with the default, etc.). See this bug for details, mentioning the misleading documentation.

In summary, activation of the binary logs and server id setup requires a server restart, so I always recommend to do it on installation time. Change it on the [mysqld]section of your configuration file and restart the service.

Related Question