Error during Master to Master Replication of MySQL server

Question

I have setup Master to Master Replication on MySQL server 5.2 with my existing database. I am sucessfully able to replicate data of my Master 1 to Master 2 with the user named as replication. But I am getting error from Master 1 when I try to connect to master 2. Here is the detail information of them:

Master 1:

mysql> show slave status \G;

*************************** 1. row ***************************

           Slave_IO_State: Connecting to master
              Master_Host: 10.34.1.37
              Master_User: replication
              Master_Port: 3306
            Connect_Retry: 10
          Master_Log_File: mysql-bin.000018
      Read_Master_Log_Pos: 107
           Relay_Log_File: XX-XX-01-relay-bin.000002
            Relay_Log_Pos: 4
    Relay_Master_Log_File: mysql-bin.000018
         Slave_IO_Running: Connecting
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 107
          Relay_Log_Space: 107
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
       Master_SSL_CA_File:
       Master_SSL_CA_Path:
          Master_SSL_Cert:
        Master_SSL_Cipher:
           Master_SSL_Key:
    Seconds_Behind_Master: NULL
    Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 2003
            Last_IO_Error: error connecting to master 'replication@10.34.1.3
    7:3306' - retry-time: 10  retries: 86400
           Last_SQL_Errno: 0
           Last_SQL_Error:
    Replicate_Ignore_Server_Ids:
         Master_Server_Id: 0
   1 row in set (0.00 sec)

In Master 2:

mysql> show slave status \G;

*************************** 1. row ***************************

           Slave_IO_State: Waiting for master to send event
              Master_Host: 10.5.224.12
              Master_User: replication
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000019
      Read_Master_Log_Pos: 107
           Relay_Log_File: xx-xx-xx-relay-bin.000029
            Relay_Log_Pos: 253
    Relay_Master_Log_File: mysql-bin.000019
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 107
          Relay_Log_Space: 560
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
       Master_SSL_CA_File:
       Master_SSL_CA_Path:
          Master_SSL_Cert:
        Master_SSL_Cipher:
           Master_SSL_Key:
    Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error:
           Last_SQL_Errno: 0
           Last_SQL_Error:
    Replicate_Ignore_Server_Ids:
         Master_Server_Id: 1
   1 row in set (0.00 sec)

   ERROR:
   No query specified

Please help me. Your solution will guide me to resolve task. Thank you in advance.

score 2 · Answer 1 · answered Oct 17 '12 at 13:31

2

Make sure you can connect from the host of Master 1 DB to Master 2's MySQL socket, you can verify if you are able to connect via shell command:

telnet master-2-ip MYSQLPORT //3306 is the default port for mysql

if this command succeeds (which means you don't get 'Connection Refused'), try connecting with:

mysql -u user -p'password' -H host -P port

if the problem is not connectivity issues posting your mysql logs under /var/log might help a bit more.

answered Oct 17 '12 at 13:31

flatline

86
2

Thank you for your prompt answer. I got error while doing TELNET from Master 1 as: C:\Program Files\MySQL\MySQL Server 5.5\bin>telnet 10.34.1.37 3306 Connecting To 10.34.1.37...Could not open connection to the host, on port 3306: Connect failed What could be the error :( – Udhy Oct 17 '12 at 13:40
1

apparently your master1 can not connect to master2, hence its not pushing its updates to master2. Check if master2 mysql daemon binds on your network interface, not the local loop. Look into your my.cnf and check bind-address variable, if it's 127.0.0.1 your mysql daemon only binds on local interface, which means any other host than itself wouldn't be allowed to connect. – flatline Oct 17 '12 at 13:48
In my.cnf, I haven't mentioned any bind-address variable on it. There is no any term named as bind in my whole my.cnf. Should I need to mention on it. – Udhy Oct 17 '12 at 13:51
oh, I'm not that familiar with mysql server on windows, maybe there is somewhere else you should be looking at. A quick google search should do the trick. – flatline Oct 17 '12 at 14:03
**Any idea from anyone is most welcome. I stuck over here. I came to know that my Master 2 is not allowing my Master 1 to connect on port 3306 as I saw from Telnet. I am working with MySql in Windows Server 2008 R2. Please help me to figure it out, I try to google a lot but no any fruitful result yet :)** – Udhy Oct 18 '12 at 07:25

Error during Master to Master Replication of MySQL server

1 Answers1