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-configured-as-slave/


1 Answer 1


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, “Setting the Replication Slave Configuration”.

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

  • 1
    Thanks. " Setting the Replication Slave Configuration" says what we need to do. What I don't understand is why replication is working fine after a restart, and why MySQL seemed to assign a default value of '1' to 'server_id'. Aug 5, 2011 at 22:59
  • 2
    I'll respond to my own comment. According to Baron Schwartz (MySQL guru, creator of Maatkit, co-author of several MySQL books), if 'server_id' is not set then MySQL can be buggy. See his description at xaprb.com/blog/2007/08/01/… Aug 23, 2011 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.