2

I have MySQL MASTER/SLAVE replication working on two test boxes (Centos 6.4 / MySQL 5.5.32) over LAN.

Securing the connection over ssh causes connection problems from the SLAVE machine:

(Sample of show slave status \G Output)

Last_IO_Errno: 2003 Last_IO_Error: error connecting to master 'rep@127.0.0.1:3305' - retry-time: 60

I have granted the replication user the relevant privileges on the master server with both 127.0.0.1 and the network IP.

I have forwarded the port from slave to master over SSH

ssh -f 192.168.0.128 -L 3305:192.168.0.128:3306 -N

I can connect to master MySQL from slave with

mysql -urep -ppassword -h127.0.0.1 -P3305

The master server setup would seem fine, as it works without a tunnel, and the tunnel seems fine, as I can connect to MySQL between the two.

Change Master Statement:

CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3305, MASTER_USER='rep', MASTER_PASSWORD='password';

Note: I know there are reasons to use SSL, instead of SSH, but I have reasons why SSH is a better choice for my setup.

Dom
  • 75
  • 1
  • 11
  • What parameters did you enter exactly in the CHANGE MASTER TO: statement? – NickW Aug 07 '13 at 10:07
  • CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3305, MASTER_USER='rep', MASTER_PASSWORD='password'; – Dom Aug 07 '13 at 10:15
  • anything in your master mysql log file? – NickW Aug 07 '13 at 10:20
  • You have added the binlog and log file position as well right? – NickW Aug 07 '13 at 10:23
  • Nothing in master mysql.log for several hours preceding my attempts. – Dom Aug 07 '13 at 10:33
  • I've tried with and without binlog and log file position. – Dom Aug 07 '13 at 10:38
  • Just as a test, if you create a 'repl@%' user, to see if for some odd reason it sees the replication connection as coming from somewhere else than 127.0.0.1 or the slave's ip? – NickW Aug 07 '13 at 10:38
  • I have tried that, but the error remains the same. – Dom Aug 07 '13 at 10:44
  • Could you show output of the "mysql> show grants for rep;" – ALex_hha Aug 07 '13 at 13:29
  • ---------------------------------+ | Grants for rep@% | +----------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%' IDENTIFIED BY PASSWORD '*657E646B9082070505D0291D1644F9B3BD3D58CC' | +----------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) – Dom Aug 07 '13 at 14:21
  • That doesn't display quite right here. The 'SLAVE ON . ' is actually 'SLAVE ON (asterisk).(asterisk)' – Dom Aug 07 '13 at 14:23
  • 1
    I can't submit my own answer, yet, but to save other people's time, it turns out the issue was SELinux not allowing MySQL on a non-standard port. – Dom Aug 07 '13 at 16:12

2 Answers2

4

Thanks for your help, guys, but it turns out the issue was SELinux not allowing MySQL on a non-standard port.

Dom
  • 75
  • 1
  • 11
1

You can use semanage to allow additional none standard port for mysqld

sudo /usr/sbin/semanage port -a -t mysqld_port_t -p tcp 3305

then you may verify using

sudo /usr/sbin/semanage port -l | grep mysql

you'll see the new port displayed along with the standard port 3306

mysqld_port_t tcp 3305, 3306, 63132-63164

Tek Jau
  • 31
  • 4