MySQL : if server_id is not explicitly set, then get error `ERROR 1200 (HY000) at line 1: The server is not configured as slave;`

Our mysql version is "mysql Ver 14.14 Distrib 5.1.35, for portbld-freebsd7.2 (amd64) using 5.2"

We have two database servers with replication, in a simple master/slave relationship. mysql2 is the master, mysql1 is the slave. A year ago, mysql was the master and mysql2 was the slave. We had to reverse their roles during a hardware failure.

On the master, we explicitly set the server_id (server-id)

[root@mysql2 ~]# grep server-id my.cnf
server-id       = 2
[root@mysql2 ~]# mysql -e "SHOW VARIABLES LIKE 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     | 
+---------------+-------+

But, this setting was not set in my.cnf on the slave.

[root@mysql1 ~]# grep server-id my.cnf

Whoops. This shouldn't matter, because the server_id is set to 1 by default.

[root@mysql1 ~]# mysql -e "SHOW VARIABLES LIKE 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     | 
+---------------+-------+

If I restart mysql, then the replication will work fine.

However, if I manually "STOP SLAVE" "START SLAVE", I get an error:

[root@mysql1 ~]# mysql -e "STOP SLAVE;"
[root@mysql1 ~]# mysql -e "START SLAVE;"
ERROR 1200 (HY000) at line 1: The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Why do I get this error when I don't explicitly set a value in my.cnf ? Why does replication work when I restart, but not when I "STOP SLAVE;" "START SLAVE;"?

• It appears that mysql assigned a default value to the variable server_id.

-
According the default my.cnf, the server_id will default to 1

# required unique id between 1 and 2^32 - 1 
# defaults to 1 if master-host is not set 
# but will not function as a master if omitted 
#server-id  = 1

The fix is to explicitly set server-id in my.cnf, as suggested by Baron Schwartz at http://www.xaprb.com/blog/2007/08/01/why-mysql-server-not-confi

Two servers are not allowed to have the same server_id. Also, If you omit --server-id from a master, the default ID is 0, in which case a master refuses connections from all slaves, and a slave refuses to connect to a master.

For more information, see Section 15.1.1.2, “Setting the Replication Slave Configuration”.

Even if both servers have different server_id values, please make sure the new master has binary logging enabled.

StackExchange Database Administrators Q#4400, answer score: 2