[stunnel-users] MySQL replication with stunnel, connection closed after some time

[Marcel Kirsch]

Hello Mike and Claus,

it is correct that there is a router (with NAT) between the slave server
and the internet. I tried the solution of Mike (socket =
l:SO_KEEPALIVE=1...), but it didn't work.
I googled SO_KEEPALIVE and discovered that it only sends a keep alive
packet every 2 hours by default. Unfortunately the connection is closed
after a shorter time (about 30 minutes). Do you know how it is possible
to set the keep alive time to a shorter value? I tried SO_KEEPALIVE=1:5
and hoped this could set the default time between keep alive packets to
5 minutes, but this didn't work.

PS I tried to make a proof of concept regarding the keep alive by
sending an insert to the master database every 5 minutes. This forced
the stunnel connection to send packets and the connection wasn't closed.
So now I only have to transform my high level keep alive (which is a
dirty hack of course) into a real keep alive.

Best regards
Marcel

> Hello,
>
> I have a problem regarding stunnel and MySQL replication.
> I use replication to keep our database synchronous with a second
> database, which stands in our building. The secure connection via
> stunnel works and "SHOW SLAVE STATUS;" shows, that the replication is
> working. Because the master database is rarely used at the moment,
> sometimes there are no changes (insert, update or delete) made for an
> hour. Unfortunately it seems that stunnel disconnects the connection
> between the databases after some time. The client database still thinks
> that it is connected to the master server, because it still is connected
> to port 3307 (which is stunnel of course). So now the changes made in
> the master database are no longer replicated. Is there any way to
> configure stunnel to keep the connection between the to servers alive
> even after an hour of zero traffic? I thought that TIMEOUTidle would be
> the answer to my problem, but it had no effect.
>
> I really hope you can help me and thanks for your suggestions in advance.
>
> Greetings,
> Marcel Kirsch
>
>
> This is my current configuration:
> Client:
>
> ; Certificate/key is needed in server mode and optional in client mode
> cert = /etc/stunnel/stunnel.pem
> key = /etc/stunnel/stunnel.pem
>
> ; Protocol version (all, SSLv2, SSLv3, TLSv1)
> sslVersion = SSLv3
>
> ; Some security enhancements for UNIX systems - comment them out on Win32
> chroot = /var/lib/stunnel4/
> setuid = stunnel4
> setgid = stunnel4
> ; PID is created inside chroot jail
> pid = /stunnel4.pid
>
> ; Some performance tunings
> socket = l:TCP_NODELAY=1
> socket = r:TCP_NODELAY=1
> ;compression = rle
> retry = yes
> TIMEOUTidle = 86400
>
> ; Some debugging stuff useful for troubleshooting
> debug = 3
> output = /var/log/stunnel4/stunnel.log
>
> ; Use it for client mode
> client = yes
>
> [mysqls]
> accept = 3307
> connect = 78.46.106.68:3307
>
>
> Server:
>
> client = no
> pid = /var/run/stunnel.pid
>
> #
> # debugging
> #
> debug = 7
> output = /etc/stunnel/stunnel.log
>
> #
> # Some performance tunings
> #
> # disable Nagle algorithm (a.k.a. tinygram prevention, see man 7 tcp)
> socket = l:TCP_NODELAY=1
> socket = r:TCP_NODELAY=1
>
> cert = /etc/stunnel/stunnel.pem
> retry = yes
> TIMEOUTidle = 86400
>
> [mysqls]
> accept = 3307
> connect = 127.0.0.1:mysql
>
> Log of client while connecting:
> 2009.10.09 09:28:58 LOG7[3729:3082963856]: connect_wait: connected
> 2009.10.09 09:28:58 LOG5[3729:3082963856]: mysqls connected remote
> server from 192.168.1.87:33206
> 2009.10.09 09:28:58 LOG7[3729:3082963856]: Remote FD=17 initialized
> 2009.10.09 09:28:58 LOG7[3729:3082963856]: TCP_NODELAY option set on
> remote socket
> 2009.10.09 09:28:58 LOG7[3729:3082963856]: SSL state (connect):
> before/connect initialization
> 2009.10.09 09:28:58 LOG7[3729:3082963856]: SSL state (connect): SSLv3
> write client hello A
> 2009.10.09 09:28:58 LOG7[3729:3082963856]: SSL state (connect): SSLv3
> read server hello A
> 2009.10.09 09:28:58 LOG7[3729:3082963856]: SSL state (connect): SSLv3
> read server certificate A
> 2009.10.09 09:28:58 LOG7[3729:3082963856]: SSL state (connect): SSLv3
> read server done A
> 2009.10.09 09:28:58 LOG7[3729:3082963856]: SSL state (connect): SSLv3
> write client key exchange A
> 2009.10.09 09:28:58 LOG7[3729:3082963856]: SSL state (connect): SSLv3
> write change cipher spec A
> 2009.10.09 09:28:58 LOG7[3729:3082963856]: SSL state (connect): SSLv3
> write finished A
> 2009.10.09 09:28:58 LOG7[3729:3082963856]: SSL state (connect): SSLv3
> flush data
> 2009.10.09 09:28:58 LOG7[3729:3082963856]: SSL state (connect): SSLv3
> read finished A
> 2009.10.09 09:28:58 LOG7[3729:3082963856]:    3 items in the session cache
> 2009.10.09 09:28:58 LOG7[3729:3082963856]:    3 client connects
> (SSL_connect())
> 2009.10.09 09:28:58 LOG7[3729:3082963856]:    3 client connects that
> finished
> 2009.10.09 09:28:58 LOG7[3729:3082963856]:    0 client renegotiations
> requested
> 2009.10.09 09:28:58 LOG7[3729:3082963856]:    0 server connects
> (SSL_accept())
> 2009.10.09 09:28:58 LOG7[3729:3082963856]:    0 server connects that
> finished
> 2009.10.09 09:28:58 LOG7[3729:3082963856]:    0 server renegotiations
> requested
> 2009.10.09 09:28:58 LOG7[3729:3082963856]:    0 session cache hits
> 2009.10.09 09:28:58 LOG7[3729:3082963856]:    0 session cache misses
> 2009.10.09 09:28:58 LOG7[3729:3082963856]:    0 session cache timeouts
> 2009.10.09 09:28:58 LOG6[3729:3082963856]: SSL connected: new session
> negotiated
> 2009.10.09 09:28:58 LOG6[3729:3082963856]: Negotiated ciphers:
> AES256-SHA SSLv3 Kx=RSA Au=RSA Enc=AES(256) Mac=SHA1
>
>
> Log of server while connecting:
> 2009.10.09 09:28:52 LOG7[31073:139652979042032]: mysqls accepted FD=14
> from 212.95.118.250:60651
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: mysqls started
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: FD 14 in non-blocking mode
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: TCP_NODELAY option set on
> local socket
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: Waiting for a libwrap process
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: Acquired libwrap process #0
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: Releasing libwrap process #0
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: Released libwrap process #0
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: mysqls permitted by libwrap
> from 212.95.118.250:60651
> 2009.10.09 09:28:52 LOG5[31073:1091389776]: mysqls accepted connection
> from 212.95.118.250:60651
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: SSL state (accept):
> before/accept initialization
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: SSL state (accept): SSLv3
> read client hello A
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: SSL state (accept): SSLv3
> write server hello A
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: SSL state (accept): SSLv3
> write certificate A
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: SSL state (accept): SSLv3
> write server done A
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: SSL state (accept): SSLv3
> flush data
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: SSL state (accept): SSLv3
> read client key exchange A
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: SSL state (accept): SSLv3
> read finished A
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: SSL state (accept): SSLv3
> write change cipher spec A
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: SSL state (accept): SSLv3
> write finished A
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: SSL state (accept): SSLv3
> flush data
> 2009.10.09 09:28:52 LOG7[31073:1091389776]:    3 items in the session cache
> 2009.10.09 09:28:52 LOG7[31073:1091389776]:    0 client connects
> (SSL_connect())
> 2009.10.09 09:28:52 LOG7[31073:1091389776]:    0 client connects that
> finished
> 2009.10.09 09:28:52 LOG7[31073:1091389776]:    0 client renegotiations
> requested
> 2009.10.09 09:28:52 LOG7[31073:1091389776]:   17 server connects
> (SSL_accept())
> 2009.10.09 09:28:52 LOG7[31073:1091389776]:   17 server connects that
> finished
> 2009.10.09 09:28:52 LOG7[31073:1091389776]:    0 server renegotiations
> requested
> 2009.10.09 09:28:52 LOG7[31073:1091389776]:    0 session cache hits
> 2009.10.09 09:28:52 LOG7[31073:1091389776]:    1 session cache misses
> 2009.10.09 09:28:52 LOG7[31073:1091389776]:   14 session cache timeouts
> 2009.10.09 09:28:52 LOG6[31073:1091389776]: SSL accepted: new session
> negotiated
> 2009.10.09 09:28:52 LOG6[31073:1091389776]: Negotiated ciphers:
> AES256-SHA SSLv3 Kx=RSA Au=RSA Enc=AES(256) Mac=SHA1
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: FD 15 in non-blocking mode
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: mysqls connecting 127.0.0.1:3306
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: connect_wait: waiting 10 seconds
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: connect_wait: connected
> 2009.10.09 09:28:52 LOG5[31073:1091389776]: mysqls connected remote
> server from 127.0.0.1:59705
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: Remote FD=15 initialized
> 2009.10.09 09:28:52 LOG7[31073:1091389776]: TCP_NODELAY option set on
> remote socket
> 2009.10.09 09:28:52 LOG7[31073:1098307920]: Socket closed on read
> 2009.10.09 09:28:52 LOG7[31073:1098307920]: SSL write shutdown
> 2009.10.09 09:28:52 LOG7[31073:1098307920]: SSL alert (write): warning:
> close notify
> 2009.10.09 09:28:52 LOG6[31073:1098307920]: SSL socket closed on
> SSL_shutdown
> 2009.10.09 09:28:52 LOG7[31073:1098307920]: Socket write shutdown
> 2009.10.09 09:28:52 LOG5[31073:1098307920]: Connection closed: 9600
> bytes sent to SSL, 185 bytes sent to socket
> 2009.10.09 09:28:52 LOG7[31073:1098307920]: mysqls finished (1 left)
>
>
> Log of server while disconnecting:
> 2009.10.09 09:53:48 LOG3[31073:1091389776]: SSL_read: Connection reset
> by peer (104)
> 2009.10.09 09:53:48 LOG5[31073:1091389776]: Connection reset: 1047 bytes
> sent to SSL, 185 bytes sent to socket
> 2009.10.09 09:53:48 LOG7[31073:1091389776]: mysqls finished (0 left)
>
>
> (There is no log of client while disconnecting)
>
> _______________________________________________
> stunnel-users mailing list
> stunnel-users at mirt.net
> http://stunnel.mirt.net/mailman/listinfo/stunnel-users
>