Xaprb

Stay curious!

Why MySQL says the server is not configured as a slave

with 7 comments

Is MySQL giving you the error message “ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO” when you try to run START SLAVE? There are a few simple troubleshooting steps to take, but I always forget what to do. This article is to help me remember in the future!

  • First, make sure you have run CHANGE MASTER TO and configured the server as a slave. If you’ve done this, you should get some output from SHOW SLAVE STATUS. If so, go to the next step.
  • Next, make sure you have set a server ID on both the master and the slave. Try running SHOW VARIABLES LIKE 'server_id' on both servers. If the value is zero or one, check the configuration file for an explicit setting, because zero or one is often the default value when nothing is specified. I have seen this cause the slave to fail, even in cases where the master’s ID is 1, which ought to work okay but sometimes doesn’t.
  • Finally, make sure your master and slave have different server IDs (on small networks, I usually set the server ID to the last octet in the server’s IP address, because it’s handy and easy to remember). MySQL slaves will refuse to replicate from a master with the same ID.

You should now be able to run START SLAVE and start your slave replicating from the master.

Did I miss anything? Let me know, and I’ll add it!

Written by Xaprb

August 1st, 2007 at 8:15 am

Posted in SQL

Tagged with , , , , , ,

« Introducing MySQL Visual Explain
Introducing MySQL Slave Delay »

7 Responses to 'Why MySQL says the server is not configured as a slave'

Subscribe to comments with RSS or TrackBack to 'Why MySQL says the server is not configured as a slave'.

  1. Yeah . The whole server-id thing is amazingly stupid.

    Why can’t a slave compute it’s own server-id and why is it even needed in the first place? Can’t you hashcode the IP address or even turn the IP address into a 32bit int?

    I realize that none of these situations are PERFECT but some more reasonable defaults would be nice!

    Kevin Burton

    2 Aug 07 at 2:03 am

  2. Hi, found your blog, love it, may find my answer as I go thru it. I posted this on the mysql forum and got not response :( care to take a shot ?

    – THanks, Andrew

    I have a table named JOB that looks like this:

    ————– ————— —— —– ——— —————-
    | Field | Type | Null | Key | Default | Extra |
    ————– ————— —— —– ——— —————-
    | id | int(11) | NO | PRI | NULL | auto_increment |
    | created_at | datetime | YES | | NULL | |
    | processed_at | datetime | YES | | NULL | |
    ————– ————— —— —– ——— —————-

    I have been processing this from a single thread by just finding the oldest non-completed job and processing it…

    - SELECT id FROM job WHERE processed_at IS NULL ORDER BY created_at;
    - UPDATE job SET processed_at = CURDATE() where id =
    - process the job

    Now I need to speed things up a bit, and need to process these jobs from multiple threads. The code above does not work, as the same job could get processed twice.

    What’s the best practice way to do something like this ?

    andrew

    2 Aug 07 at 12:52 pm

  3. dead link to an article

    this post
    http://www.xaprb.com/blog/2007/01/11/how-to-implement-a-queue-in-sql/

    reference this post
    http://www.xaprb.com/blog/2006/02/21/flexible-insert-and-update-in-mysql/“

    which seems to be missing ?

    andrew

    2 Aug 07 at 12:55 pm

  4. Tsk, tsk, off topic :-)

    I’d update the job and set a ‘processor’ column to CONNECTION_ID(), like this:

    update job
set processor = CONNECTION_ID()
where processor is null
   and processed_at is null
limit 1;

select * from job where processor = CONNECTION_ID() ....

    You need to handle the case where your processing application claims a job but never finishes it, in which case nothing else would ever claim it. I’d have a periodic job to just reset processor to NULL after the job has been claimed but not processed for a while.

    Xaprb

    2 Aug 07 at 9:17 pm

  5. Andrew, thanks for the note. I was missing a quote in a link. Fixed now.

    Xaprb

    2 Aug 07 at 9:19 pm

  6. I find it useful to run a RESET SLAVE command before running the CHANGE MASTER command. This helps keep weird things from happening.

    Keith Murphy

    14 Aug 07 at 4:47 pm

  7. If this is on the master

    character-set-server=utf8
    collation-server=utf8_unicode_ci

    default-character-set=utf8
    default-collation=utf8_unicode_ci

    Make sure it is on the slave too (my.cnf)

    Anonymous

    7 Dec 07 at 9:14 pm

Leave a Reply