MySQL replication connection error on non-standard ports

or, “Why SELinux was the worst thing to happen to Linux since LinuxThreads.”

Setting up MySQL master-slave replication is pretty straightforward. I’ve done it plenty of times. Similarly, running multiple MySQL server instances on the same host using mysqld_multi, really painless. Give each instance its own server-id and port to listen on, and that’s it.

In this case, the server that was being replicated was listening on port 3307. I could connect to it just fine using the mysql client using “-P 3307” as the replication user. It was clearly possible for the two machines to communicate over TCP port 3307. But, then, why was the MySQL slave I/O thread failing to connect to the master?

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: x.x.x.x
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 60
...
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
...
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'repl@x.x.x.x:3307' - retry-time: 60  retries: 86400
...
1 row in set (0.00 sec)

Makes no sense, right? Right! Unless, of course, you’re on a machine–in this case, Red Hat Enterprise Linux 5.7–that has ever-so-helpfully screwed you by installing SELinux policies that restrict what ports mysqld can use, and has SELinux enabled by default.

How can you tell if SELinux is enabled and its policies are being enforced?

# grep 1 /selinux/enforce
1

If you get no output, then SELinux isn’t being enforced. But, if it is enabled, here’s how you can tell if mysqld‘s ability to make network connections is being restricted by a policy:

# semanage port -l | grep 3306
mysqld_port_t                  tcp      1186, 3306

You can tell if this is happening if you look in /var/log/audit/audit.log and see an entry like this:

type=AVC msg=audit(1320869122.773:56478): avc: denied { name_connect } for pid=2990 comm=”mysqld” dest=3307 scontext=user_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:port_t:s0 tclass=tcp_socket

(I bolded the elements to keep an eye out for.)

So, how do we remedy the situation? Personally, my first recommendation is to turn SELinux off. It is always more trouble than it’s worth. I’ve never heard of a single anecdote where it improved security or otherwise did anything useful.

Disable SELinux by editing /etc/selinux/config and change the line that reads “SELINUX=enforcing” to “SELINUX=disabled” and then reboot the system. There’s some caveats around disabling SELinux with respect to re-enabling it in the future, but you’re never going to do that if you have a choice, so who cares.

If you absolutely must leave SELinux enabled, because you’re actually burning in Hell and being tortured by some unreasonable and irrational organizational policy that requires it, then this is how you can add ports to the list of ports that mysqld will be allowed to use:

# semanage port -a -t mysqld_port_t -p tcp 3307

This command might take a few seconds to complete, and won’t generate any output if successful. You can check to see if it did what you wanted using the same command we used before, but this time the newly added port should be in the list, as well:

# semanage port -l | grep 3306
mysqld_port_t                  tcp      3307, 1186, 3306

To make sure this really did fix the problem, lets look at the MySQL replication status, again:

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: x.x.x.x
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 60
...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...
                Last_IO_Errno: 0
                Last_IO_Error: 
...
1 row in set (0.00 sec)

And there you have it, replication configured on TCP port 3307, connected successfully, waiting for the master to send it updates, like it should be.

Hopefully, this has helped save you time, aggravation and much head-scratching as you tried to figure out why MySQL replication wasn’t working on a port other than 3306.

Got any other good gotcha stories like this? Share them in the comments below.