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)