Home / server / Questions / 675112
Accepted
Ernie
Asked: 2015-03-13 14:50:56 +0800 CST

Syntax error when setting up master-master replication in MySQL

  • 772

So I've set up my initial master-slave replication pair on two different MySQL servers. That part is working great. So all I have to do is set up the first master as a slave and set up the first slave as the second master, right?

Wrong!

This is what happens when I try to create the second slave account on the secondary master server: 

mysql> grant replication slave on *.* to 'repslave2'@'ourhost' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> start master;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'master' at line 1

What? What do you mean, "syntax error"? This is pretty simple stuff. I spelled "master" right. It worked on the other server without having to install anything special. "stop slave" and "start slave" work as advertised on this server. Why won't it work?

EDITED TO ADD:

I think I've found part of the problem - that the master-slave replication is confused between the two servers. Here, I've tried to stop the master on the primary master:

mysql> stop master;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'master' at line 1
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> stop master;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'master' at line 1
mysql> start master;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'master' at line 1
mysql> start slave;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
mysql

3 Answers

  1. Best Answer

    You must define the master setup first on the new master, then stop slave on the server you wish to make master before you send the start master; command.

    Probably not the answer you wanted, but it is correct.

    A better understanding of what you are trying to do would be helpful.

    • 1

  2. Try start slave.

    Each master should be a slave of the other master, don't forget to have different server-id in each server configured on my.cnf.

    Also set properly auto_increment_increment and auto_increment_offset to avoid duplicated primary keys who break the replication.

    Check with show slave status if Slave_IO_Running and Slave_SQL_Running are with yes status after properly configured each server.

    • 1

  3. After configuration type this command START SLAVE IO_THREAD

    • 1