Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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/

server-id=1

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
1  
Thanks. "15.1.1.2. 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'. –  Stefan Lasiewski Aug 5 '11 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/… –  Stefan Lasiewski Aug 23 '11 at 19:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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