Today was a hellish day. Late in the afternoon when we’re supposed to be winding down and heading out one of our shared webhosting servers started freaking out. No changes to any software or settings — nothing to suspect — but mysql-proxy kept crashing. We need this utility because we have a lot of old customers with configs from a long time ago when someone thought it was OK to have the database on the webserver.
We’ve never had much luck with mysql-proxy as we’ve seen it crash its fair amount of times or not start properly on boot, but this was unending. Core dumps weren’t giving anything useful; logs were no better. Here’s the MySQL-Proxy alternative that we should have implemented ages ago. Note, we’re running FreeBSD, so adapt to your own OS/Linux distro:
What you need:
net/socat
net/haproxy
FreeBSD’s rc.conf:
# mysql_proxy alternative
socat_enable="YES"
socat_flags="UNIX-LISTEN:/tmp/mysql.sock,fork,reuseaddr,unlink-early,mode=777 TCP:127.0.0.1:3306"
haproxy_enable="YES"
HAProxy’s config:
global
log 127.0.0.1 local0
maxconn 4096
daemon
#debug
#quiet
#
defaults
log global
mode tcp
option tcplog
option dontlognull
option tcp-smart-accept
option tcp-smart-connect
retries 3
maxconn 200
#
listen mysql :3306
mode tcp
option mysql-check
balance roundrobin
server mysql1 1.2.3.4:3306 check port 3306
Ultimately, HAProxy manages the connections very well and socat fixes any old clients trying to talk over the /tmp/mysql.sock file.
Enjoy.
Leave a Reply