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

[Lund, Claus]

I don't think Stunnel is in charge of the disconnect. It's probably a router/firewall in the path or maybe an OS thing at either end. I'd say go look for the keep-alive type settings in those places...


-----Original Message-----
From: stunnel-users-bounces at mirt.net [mailto:stunnel-users-bounces at mirt.net] On Behalf Of Marcel Kirsch
Sent: Thursday, October 15, 2009 9:21 AM
To: stunnel-users at mirt.net
Subject: [stunnel-users] MySQL replication with stunnel, connection closed after some time

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