Home / server / Questions / 836617
In Process
Martini002
Asked: 2017-03-07 08:30:47 +0800 CST

MySQL server losing connections

  • 772

let me explain the history of this issue,

I am running Centos 7 on a dedicated server, when I installed it for the first time I did not make any partition because I do not know very well how to do it, and actually I think is not possible because I can not do any backup of the entire server in other machine.

So this is my disk:

[root@ns527667 ~]# lsblk
NAME   MAJ:MIN RM    SIZE RO TYPE MOUNTPOINT
sda      8:0    0    1.8T  0 disk 
└─sda1   8:1    0 1004.5K  0 part 
└─sda2   8:2    0   19.5G  0 part /
└─sda3   8:3    0    1.8T  0 part /home
└─sda4   8:4    0    511M  0 part [SWAP]

I installed MySQL server on / partition, but with the time all my DBs grew significantly, so I decided to move them to the /home partition, was nice. I forgot the space issue then. Before the move I was able to connect to my DB using localhost as DB host, but it starting to have a isssue with the connection, sometimes my apps lost connection, so I changed the DB host to 127.0.0.1 at that moment it fix the problem, but now sometimes it lost connection again. And some times the response time between query and result its high (0-5000ms aprox).

I run MySQLTunner and it suggested me to change some variables so I changed a lot of variables like:

SET GLOBAL max_connections = 500;
SET @@GLOBAL.long_query_time = 1;
SET GLOBAL tmp_table_size = 128000000;
SET GLOBAL max_heap_table_size = 128000000;
SET GLOBAL key_buffer_size = 32000000;
SET GLOBAL thread_cache_size = 20;
SET GLOBAL max_allowed_packet = 8000000;

Actually my connections rounds 150-200

I optimized or at least I think I did it on all my InnoDB

mysqlcheck --user=root --password="pass" -o --all-databases

This is my /etc/my.cnf

[mysqld]
datadir=/home/var/lib/mysql
socket=/home/var/lib/mysql/mysql.sock
interactive_timeout=86400
wait_timeout=86400
max_connections=1000
innodb_buffer_pool_size=256M
skip-name-resolve
slow-query-log = 1
slow-query-log-file = /home/var/lib/mysql/mysql-slow.log
long_query_time = 2
bind-address="127.0.0.1"
port=3306

symbolic-links=0

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
socket=/home/var/lib/mysql/mysql.sock
port=3306

I am using various java apps and they works very well in my personal machine, but in the server sometimes lost the connection with errors like this:

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

or

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Now I do not know what to do..

connection mysql centos7 lost

0 Answers