2

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.

2

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.

  • following your commend about the server-id.. i actually tried setting it dynamically via mysql CLI using set global server_id=xxx , and after setting it, going back to the slave and setting up the master again it finally worked! i guess the binlog bug really did affect our server. – paacharon3 Sep 12 '14 at 10:13

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.