1

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
1

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.

  • There are many howtos on the internet for setting up Master-Master replication. Here's one I'm following: erlycoder.com/43/… – Ernie Mar 13 '15 at 17:47
  • That how-to is a good one, make sure you clear your iptables first (iptables -F) just so there isnt a one way connection involved. – Random-IT Mar 13 '15 at 18:52
  • That how-to is a good one, make sure you clear your iptables first (iptables -F) just so there isnt a one way connection involved. make sure you have your increment values set to skip by 1 and start the second master at 2 (this may need to be modified to start where your indexes presently are). its a good idea to break the slave setup to get both set as masters first then start the slave process. Check your my.cnf for typo's and make sure you have the settings correct. Add comments to the file so you know which one your editing and what the command is there for. – Random-IT Mar 13 '15 at 18:58
  • Cool. Um, why can't I run the command "start master" on the master 2/slave 1? – Ernie Mar 13 '15 at 22:37
  • The command is available before the slave setup. I just built a pair on Debian7 using MySQL5.5 (on a VM host, using a default install snap-shot) and once the slave was started the start master command was not working. I reverted to the default snap-shot and reconfigured both as masters first then setup the slave configs and then everything worked as expected. I even did a fail condition (stopped the VM instance on one server) and then made table injections. Once the stopped VM came back up it synced and worked correctly. YMMV but I did both configs before importing a test database. – Random-IT Mar 16 '15 at 16:51
1

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.

  • Yup, start slave works! Also, the slave is working perfectly as a slave. I see "Slave_IO_Running=yes" and "Slave_SQL_Running=yes" in the status query. Now I'm trying to make the first master into a slave too, so that they can both be masters and both be slaves. The point is high availability and the ability for any of our servers to connect to either server and write to the databases. – Ernie Mar 13 '15 at 17:45
  • Technically both server are "read/write slaves", just do the same commands on the other server, stop slave, change master to and start slave... but take a look at my.cnf and ensure both server have different server-id (i.e. in my.cnf, server-id=1 and server-id=2) – fgbreel Mar 13 '15 at 18:43
1

After configuration type this command START SLAVE IO_THREAD

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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