Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am trying to set up replication on a MySQL server over an SSH tunnel. I've set up a port forward on the slave from port 7777 at localhost to port 3306 on the master, and I can connect to my replication account using

mysql -h 127.0.0.1 -P 7777 -u replicate -p 

However, when I set the master configuration on the slave (using host 127.0.0.1, user replicate, the correct password [double-checked it], and port 7777), it has an error connecting to the master with code 2003, which seems to be associated with login problems. I checked the permissions, and 'replicate'@'127.0.0.1' has replication slave on *.*. Why can I connect with a client, but replication is failing.

EDIT:

The master server runs MySQL 5.1.66 on Ubuntu 10.04; the slave runs MySQL 5.5.28 on Fedora 16.

mysql --ssl --help

results in:

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- -----------------------------
auto-rehash                       TRUE
character-sets-dir                (No default value)
column-type-info                  FALSE
comments                          FALSE
compress                          FALSE
debug-check                       FALSE
debug-info                        FALSE
database                          (No default value)
default-character-set             latin1
delimiter                         ;
vertical                          FALSE
force                             FALSE
named-commands                    FALSE
ignore-spaces                     FALSE
local-infile                      FALSE
no-beep                           FALSE
host                              (No default value)
html                              FALSE
xml                               FALSE
line-numbers                      TRUE
unbuffered                        FALSE
column-names                      TRUE
sigint-ignore                     FALSE
port                              3306
prompt                            mysql>
quick                             FALSE
raw                               FALSE
reconnect                         TRUE
socket                            /var/run/mysqld/mysqld.sock
ssl                               TRUE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-verify-server-cert            FALSE
table                             FALSE
user                              (No default value)
safe-updates                      FALSE
i-am-a-dummy                      FALSE
connect_timeout                   0
max_allowed_packet                16777216
net_buffer_length                 16384
select_limit                      1000
max_join_size                     1000000
secure-auth                       FALSE
show-warnings                     FALSE

Slave status:

mysql> show slave status\G
--------------
show slave status
--------------

*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 127.0.0.1
                  Master_User: replicate
                  Master_Port: 7777
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 397
               Relay_Log_File: slave-relay-bin.000004
                Relay_Log_Pos: 107
        Relay_Master_Log_File: mysql-bin.000002
             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 'replicate@127.0.0.1:7777' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
1 row in set (0.00 sec)

EDIT:

SELinux is enabled on the server, and is in enforce mode.

share|improve this question
    
I believe, on the slave machine, after you setup the ssh tunnel, you should be using mysql -h 127.0.0.1 -P 7777 -u replicate -p to connect to master. Does that work? –  John Siu Dec 30 '12 at 1:33
    
That's what I meant, not -P 3306. -P 7777 does work, but the replication still does not. –  cpast Dec 30 '12 at 1:52
    
On the Master machine, does mysql -h 127.0.0.1 -P 3306 -u replicate -p work?? –  John Siu Dec 30 '12 at 1:54
    
That command does work. –  cpast Dec 30 '12 at 2:08
    
Tyr following (1) On master telnet localhost 3306 (2) On Slave telnet localhost 7777. Post the output. No need to login. –  John Siu Dec 30 '12 at 2:21
show 2 more comments

migrated from superuser.com Dec 29 '12 at 22:10

This question came from our site for computer enthusiasts and power users.

1 Answer

up vote 1 down vote accepted

Very likely your mysqld connection to a 7777 port is blocked by selinux. To allow that, do following

semanage port -a -t mysqld_port_t -p tcp 7777

To check if it is added to mysqld allowed port

semanage port -l | grep mysqld_port_t
share|improve this answer
    
I don't see any SSL-related options enables in my.cnf. When I actually connect from master with mysql -h 127.0.0.1 -P 3306 --ssl -u replicate -p and type \s, it says SSL: Not in use. The only place it seems to indicate that SSL might be used is in the variables list from --help. –  cpast Dec 30 '12 at 5:41
    
What OS are you using? (redhat/Centos/Ubuntu/Debian??) Also post output of mysql -v -h 127.0.0.1 -P 7777 -u replicate -p. Let see if that give more hint. –  John Siu Dec 30 '12 at 6:28
    
Master is on Ubuntu 10.04, slave on Fedora 16. –  cpast Dec 30 '12 at 6:38
    
What about output of mysql -v -h 127.0.0.1 -P 7777 -u replicate -p? –  John Siu Dec 30 '12 at 6:48
    
What do you mean by "post the output"? When I run it on the slave server, it logs in successfully to the master server. It shows trademark boilerplate, but the only non-generic info shown is Server version: 5.1.66-0ubuntu0.10.04.3-log (Ubuntu) and reading history-file /home/cpast/.mysql_history. It also brings up a mysql prompt. –  cpast Dec 30 '12 at 6:54
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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