[SOLVED] mysql replication via stunnel

packets · 10-22-2014, 09:51 PM

Hi Guys, need your opinion. I was able to setup mysql replication with no ssl and now I want to secure the traffic by using stunnel. However, mysql could not replication with the following error.

Code:

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting to reconnect after a failed master event read
                  Master_Host: localhost
                  Master_User: replica
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql.000019
          Read_Master_Log_Pos: 1650
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql.000019
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test_replication
          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: 1650
              Relay_Log_Space: 106
              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: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)

ERROR: 
No query specified

But when I do mysql -ureplica -p -h 127.0.0.1 -P 3307 I was able to login on the remote mysql. My setup is master to master replication but that should not be an issue. Here is my setup

stunnel client port is 3307 while the service is 3308 facing the Internet.

mysql 3306 <--> stunnel l:3307 r:3308 <--> INTERNET <--> r:3308 l:3307 <--> mysql 3306

BTW, the public ip is NATT'ed but looks like not an issue because manually connecting to the remote mysql using localhost is successful. It is just weird that mysql replication is failing.

packets · 10-22-2014, 10:03 PM

Got the solution. I should have GRANT PERMISSION the localhost.

Quote:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'localhost' identified by 'XXXXXX';