MySQL trava se a conexão vem de fora da LAN

Navegue suas respostas
3

Eu tenho um servidor MySQL funcionando muito bem se eu acessá-lo de sua LAN local (192.168.100.0/24). Se eu tentar acessar o hin de outra rede local (192.168.113.0/24 neste caso), ele fica travado por um tempo muito longo antes de entregar o resultado. SHOW PROCESSLIST; mostra esse processo em Sleep , State empty.

Se eu strace -p deste processo eu recebo a seguinte saída (23512 é o PID do processo mysqld correspondente): 

Process 23512 attached - interrupt to quit
restart_syscall(<... resuming interrupted call ...>) = 1
fcntl(10, F_GETFL)                      = 0x2 (flags O_RDWR)
fcntl(10, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
accept(10, {sa_family=AF_INET, sin_port=htons(51696), sin_addr=inet_addr("192.168.113.4")}, [16]) = 33
fcntl(10, F_SETFL, O_RDWR)              = 0
rt_sigaction(SIGCHLD, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART, 0x7f9ce7ca34f0}, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART, 0x7f9ce7ca34f0}, ) = 0
getpeername(33, {sa_family=AF_INET, sin_port=htons(51696), sin_addr=inet_addr("192.168.113.4")}, [16]) = 0
getsockname(33, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.100.190")}, [16]) = 0
open("/etc/hosts.allow", O_RDONLY)      = 64
fstat(64, {st_mode=S_IFREG|0644, st_size=580, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f9ce9839000
read(64, "# /etc/hosts.allow: list of host"..., 4096) = 580
read(64, "", 4096)                      = 0
close(64)                               = 0
munmap(0x7f9ce9839000, 4096)            = 0
open("/etc/hosts.deny", O_RDONLY)       = 64
fstat(64, {st_mode=S_IFREG|0644, st_size=880, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f9ce9839000
read(64, "# /etc/hosts.deny: list of hosts"..., 4096) = 880
read(64, "", 4096)                      = 0
close(64)                               = 0
munmap(0x7f9ce9839000, 4096)            = 0
getsockname(33, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.100.190")}, [16]) = 0
fcntl(33, F_SETFL, O_RDONLY)            = 0
fcntl(33, F_GETFL)                      = 0x2 (flags O_RDWR)
setsockopt(33, SOL_SOCKET, SO_RCVTIMEO, "
14:49:44.103107 IP cassandra-test.mysql > 192.168.X.6.64626: Flags [S.], seq 1434117703, ack 1793610733, win 14600, options [mss 1460,nop,nop,sackOK,nop,wscale 7], length 0
14:49:44.135187 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [P.], seq 106:145, ack 182, win 4345, length 39
14:49:44.135293 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [P.], seq 182:293, ack 145, win 115, length 111
14:49:44.167025 IP 192.168.X.6.64624 > cassandra-test.mysql: Flags [.], ack 444, win 4280, length 0
14:49:44.168933 IP 192.168.X.6.64626 > cassandra-test.mysql: Flags [.], ack 1, win 4390, length 0
14:49:44.169088 IP cassandra-test.mysql > 192.168.X.6.64626: Flags [P.], seq 1:89, ack 1, win 115, length 88
14:49:44.169672 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [P.], seq 145:171, ack 293, win 4317, length 26
14:49:44.169726 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [P.], seq 293:419, ack 171, win 115, length 126
14:49:44.275111 IP 192.168.X.6.64626 > cassandra-test.mysql: Flags [P.], seq 1:74, ack 89, win 4368, length 73
14:49:44.275131 IP cassandra-test.mysql > 192.168.X.6.64626: Flags [.], ack 74, win 115, length 0
14:49:44.275149 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [P.], seq 171:180, ack 419, win 4286, length 9
14:49:44.275189 IP cassandra-test.mysql > 192.168.X.6.64626: Flags [P.], seq 89:100, ack 74, win 115, length 11
14:49:44.275264 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [P.], seq 180:185, ack 419, win 4286, length 5
14:49:44.275281 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [.], ack 185, win 115, length 0
14:49:44.275295 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [F.], seq 419, ack 185, win 115, length 0
14:49:44.275650 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [F.], seq 185, ack 419, win 4286, length 0
14:49:44.275660 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [.], ack 186, win 115, length 0
14:49:44.275910 IP 192.168.X.6.64627 > cassandra-test.mysql: Flags [S], seq 2336421549, win 8192, options [mss 1351,nop,wscale 2,nop,nop,sackOK], length 0
14:49:44.275921 IP cassandra-test.mysql > 192.168.X.6.64627: Flags [S.], seq 3289359778, ack 2336421550, win 14600, options [mss 1460,nop,nop,sackOK,nop,wscale 7], length 0

Process 23512 attached - interrupt to quit
restart_syscall(<... resuming interrupted call ...>) = 1
fcntl(10, F_GETFL)                      = 0x2 (flags O_RDWR)
fcntl(10, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
accept(10, {sa_family=AF_INET, sin_port=htons(51696), sin_addr=inet_addr("192.168.113.4")}, [16]) = 33
fcntl(10, F_SETFL, O_RDWR)              = 0
rt_sigaction(SIGCHLD, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART, 0x7f9ce7ca34f0}, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART, 0x7f9ce7ca34f0}, ) = 0
getpeername(33, {sa_family=AF_INET, sin_port=htons(51696), sin_addr=inet_addr("192.168.113.4")}, [16]) = 0
getsockname(33, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.100.190")}, [16]) = 0
open("/etc/hosts.allow", O_RDONLY)      = 64
fstat(64, {st_mode=S_IFREG|0644, st_size=580, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f9ce9839000
read(64, "# /etc/hosts.allow: list of host"..., 4096) = 580
read(64, "", 4096)                      = 0
close(64)                               = 0
munmap(0x7f9ce9839000, 4096)            = 0
open("/etc/hosts.deny", O_RDONLY)       = 64
fstat(64, {st_mode=S_IFREG|0644, st_size=880, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f9ce9839000
read(64, "# /etc/hosts.deny: list of hosts"..., 4096) = 880
read(64, "", 4096)                      = 0
close(64)                               = 0
munmap(0x7f9ce9839000, 4096)            = 0
getsockname(33, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.100.190")}, [16]) = 0
fcntl(33, F_SETFL, O_RDONLY)            = 0
fcntl(33, F_GETFL)                      = 0x2 (flags O_RDWR)
setsockopt(33, SOL_SOCKET, SO_RCVTIMEO, "
14:49:44.103107 IP cassandra-test.mysql > 192.168.X.6.64626: Flags [S.], seq 1434117703, ack 1793610733, win 14600, options [mss 1460,nop,nop,sackOK,nop,wscale 7], length 0
14:49:44.135187 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [P.], seq 106:145, ack 182, win 4345, length 39
14:49:44.135293 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [P.], seq 182:293, ack 145, win 115, length 111
14:49:44.167025 IP 192.168.X.6.64624 > cassandra-test.mysql: Flags [.], ack 444, win 4280, length 0
14:49:44.168933 IP 192.168.X.6.64626 > cassandra-test.mysql: Flags [.], ack 1, win 4390, length 0
14:49:44.169088 IP cassandra-test.mysql > 192.168.X.6.64626: Flags [P.], seq 1:89, ack 1, win 115, length 88
14:49:44.169672 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [P.], seq 145:171, ack 293, win 4317, length 26
14:49:44.169726 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [P.], seq 293:419, ack 171, win 115, length 126
14:49:44.275111 IP 192.168.X.6.64626 > cassandra-test.mysql: Flags [P.], seq 1:74, ack 89, win 4368, length 73
14:49:44.275131 IP cassandra-test.mysql > 192.168.X.6.64626: Flags [.], ack 74, win 115, length 0
14:49:44.275149 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [P.], seq 171:180, ack 419, win 4286, length 9
14:49:44.275189 IP cassandra-test.mysql > 192.168.X.6.64626: Flags [P.], seq 89:100, ack 74, win 115, length 11
14:49:44.275264 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [P.], seq 180:185, ack 419, win 4286, length 5
14:49:44.275281 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [.], ack 185, win 115, length 0
14:49:44.275295 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [F.], seq 419, ack 185, win 115, length 0
14:49:44.275650 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [F.], seq 185, ack 419, win 4286, length 0
14:49:44.275660 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [.], ack 186, win 115, length 0
14:49:44.275910 IP 192.168.X.6.64627 > cassandra-test.mysql: Flags [S], seq 2336421549, win 8192, options [mss 1351,nop,wscale 2,nop,nop,sackOK], length 0
14:49:44.275921 IP cassandra-test.mysql > 192.168.X.6.64627: Flags [S.], seq 3289359778, ack 2336421550, win 14600, options [mss 1460,nop,nop,sackOK,nop,wscale 7], length 0

%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%", 16) = 0
setsockopt(33, SOL_SOCKET, SO_SNDTIMEO, "<%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%", 16) = 0
fcntl(33, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
setsockopt(33, SOL_IP, IP_TOS, [8], 4)  = 0
setsockopt(33, SOL_TCP, TCP_NODELAY, [1], 4) = 0
futex(0x7f9cea5c9564, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0x7f9cea5c9560, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
futex(0x7f9cea5c6fe0, FUTEX_WAKE_PRIVATE, 1) = 1
poll([{fd=10, events=POLLIN}, {fd=12, events=POLLIN}], 2, -1) = 1 ([{fd=10, revents=POLLIN}])
fcntl(10, F_GETFL)                      = 0x2 (flags O_RDWR)
fcntl(10, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
accept(10, {sa_family=AF_INET, sin_port=htons(51697), sin_addr=inet_addr("192.168.113.4")}, [16]) = 31
fcntl(10, F_SETFL, O_RDWR)              = 0
rt_sigaction(SIGCHLD, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART, 0x7f9ce7ca34f0}, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART, 0x7f9ce7ca34f0}, ) = 0
getpeername(31, {sa_family=AF_INET, sin_port=htons(51697), sin_addr=inet_addr("192.168.113.4")}, [16]) = 0
getsockname(31, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.100.190")}, [16]) = 0
open("/etc/hosts.allow", O_RDONLY)      = 33
fstat(33, {st_mode=S_IFREG|0644, st_size=580, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f9ce9839000
read(33, "# /etc/hosts.allow: list of host"..., 4096) = 580
read(33, "", 4096)                      = 0
close(33)                               = 0
munmap(0x7f9ce9839000, 4096)            = 0
open("/etc/hosts.deny", O_RDONLY)       = 33
fstat(33, {st_mode=S_IFREG|0644, st_size=880, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f9ce9839000
read(33, "# /etc/hosts.deny: list of hosts"..., 4096) = 880
read(33, "", 4096)                      = 0
close(33)                               = 0
munmap(0x7f9ce9839000, 4096)            = 0
getsockname(31, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.100.190")}, [16]) = 0
fcntl(31, F_SETFL, O_RDONLY)            = 0
fcntl(31, F_GETFL)                      = 0x2 (flags O_RDWR)
setsockopt(31, SOL_SOCKET, SO_RCVTIMEO, "%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%", 16) = 0
setsockopt(31, SOL_SOCKET, SO_SNDTIMEO, "<%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%", 16) = 0
fcntl(31, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
setsockopt(31, SOL_IP, IP_TOS, [8], 4)  = 0
setsockopt(31, SOL_TCP, TCP_NODELAY, [1], 4) = 0
futex(0x7f9cea5c9564, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0x7f9cea5c9560, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
futex(0x7f9cea5c6fe0, FUTEX_WAKE_PRIVATE, 1) = 1
poll([{fd=10, events=POLLIN}, {fd=12, events=POLLIN}], 2, -1^C <unfinished ...>
Process 23512 detached

%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%", 16) = 0
setsockopt(33, SOL_SOCKET, SO_SNDTIMEO, "<%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%", 16) = 0
fcntl(33, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
setsockopt(33, SOL_IP, IP_TOS, [8], 4)  = 0
setsockopt(33, SOL_TCP, TCP_NODELAY, [1], 4) = 0
futex(0x7f9cea5c9564, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0x7f9cea5c9560, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
futex(0x7f9cea5c6fe0, FUTEX_WAKE_PRIVATE, 1) = 1
poll([{fd=10, events=POLLIN}, {fd=12, events=POLLIN}], 2, -1) = 1 ([{fd=10, revents=POLLIN}])
fcntl(10, F_GETFL)                      = 0x2 (flags O_RDWR)
fcntl(10, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
accept(10, {sa_family=AF_INET, sin_port=htons(51697), sin_addr=inet_addr("192.168.113.4")}, [16]) = 31
fcntl(10, F_SETFL, O_RDWR)              = 0
rt_sigaction(SIGCHLD, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART, 0x7f9ce7ca34f0}, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART, 0x7f9ce7ca34f0}, ) = 0
getpeername(31, {sa_family=AF_INET, sin_port=htons(51697), sin_addr=inet_addr("192.168.113.4")}, [16]) = 0
getsockname(31, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.100.190")}, [16]) = 0
open("/etc/hosts.allow", O_RDONLY)      = 33
fstat(33, {st_mode=S_IFREG|0644, st_size=580, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f9ce9839000
read(33, "# /etc/hosts.allow: list of host"..., 4096) = 580
read(33, "", 4096)                      = 0
close(33)                               = 0
munmap(0x7f9ce9839000, 4096)            = 0
open("/etc/hosts.deny", O_RDONLY)       = 33
fstat(33, {st_mode=S_IFREG|0644, st_size=880, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f9ce9839000
read(33, "# /etc/hosts.deny: list of hosts"..., 4096) = 880
read(33, "", 4096)                      = 0
close(33)                               = 0
munmap(0x7f9ce9839000, 4096)            = 0
getsockname(31, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.100.190")}, [16]) = 0
fcntl(31, F_SETFL, O_RDONLY)            = 0
fcntl(31, F_GETFL)                      = 0x2 (flags O_RDWR)
setsockopt(31, SOL_SOCKET, SO_RCVTIMEO, "%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%", 16) = 0
setsockopt(31, SOL_SOCKET, SO_SNDTIMEO, "<%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%%pre%", 16) = 0
fcntl(31, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
setsockopt(31, SOL_IP, IP_TOS, [8], 4)  = 0
setsockopt(31, SOL_TCP, TCP_NODELAY, [1], 4) = 0
futex(0x7f9cea5c9564, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0x7f9cea5c9560, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
futex(0x7f9cea5c6fe0, FUTEX_WAKE_PRIVATE, 1) = 1
poll([{fd=10, events=POLLIN}, {fd=12, events=POLLIN}], 2, -1^C <unfinished ...>
Process 23512 detached

Esta saída se repete até que a resposta seja enviada. Pode levar até 15 minutos até que a solicitação seja atendida. Na LAN local é uma questão de milissegundos.

Por que isso e como posso depurar isso ainda mais?

[Editar] tcpdump mostra uma tonelada disso:

%pre%     
por Subito 16.10.2013 / 13:59

2 respostas

1

Na imagem do tcpdump, pode-se ver a conexão de conexões e / ou freqüentes conexões de reinicialização. Verifique se o limite máximo de conexão não foi atingido.

    
por 24.10.2013 / 21:58
0

Subito,

Você tentou adicionar skip-name-resolve no seu arquivo /etc/my.cnf ? 

[mysqld]
skip-name-resolve
    
por 24.10.2013 / 13:52

Tags

Como atualizar-grub em um sistema executando overlayroot? O contêiner LXC só pode acessar o host via bridge