Can’t connect to Mysql server on 'IP (4)

Question

I have two servers with different purposes, one being a webserver running apache and php 5.3, and the other is a Mysql database server. The first uses the database in the second 24 hours a day, 7 days a week.

The problem is that we recently started having errors a few times a day, where PHP applications return the following error message to our users:

Can't connect to MySQL server on 'IP_DO_BANCO_DE_DADOS' (4)

We have several PHP applications in our Webserver, and the connection is executed according to the framework used. One of our (legacy) applications that makes no use of any framework, and therefore uses PHP natively to connect to Mysql, proceeds as follows:

function AbreBD($mysqli = false) {

        global $conexao, $selecao, $conexao_mysqli;

        $servidor = '192.168.101.20:3306';
        $usuarioBD = 'user';
        $senhaBD = 'user';

        $conexao = mysql_connect($servidor, $usuarioBD, $senhaBD);
        $selecao = mysql_select_db("bioextratus", $conexao);

        if ($mysqli) {
            $conexao_mysqli = new mysqli($servidor, $usuarioBD, $senhaBD, "bioextratus");
        }
    }

Remembering that the above code is for a legacy application, and so it still uses the mysql driver instead of mysqli. Even in our most modern frameworks applications the problem also occurs.

I believe the problem is the configuration of Mysql or network problem, although the Mysql service is always available, as well as communication between the two servers over the network.

The contents of the Mysql file my.cnf from our database server (without the comments):

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
skip-external-locking
explicit_defaults_for_timestamp = false
open_files_limit        =       102400

bind-address            = 0.0.0.0

key_buffer                      = 16M
key_buffer_size                 = 512M
innodb_buffer_pool_size         = 256M
innodb_additional_mem_pool_size = 8M
max_allowed_packet              = 16M
thread_stack                    = 196K
thread_cache_size               = 8
myisam-recover-options          = BACKUP
query_cache_limit               = 64M
query_cache_type                = 1
query_cache_size                = 256M
max_connections                 = 1024
max_user_connections            = 0
sort_buffer_size                = 2M

join_buffer_size                = 1M
tmp_table_size                  = 64M
max_heap_table_size             = 18M

log_error = /var/log/mysql/error.log

expire_logs_days        = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[isamchk]
key_buffer              = 12M

Answer 1

Well the specified problem is too wide, it can be a poorly performed configuration on the server until network instability problems.

I will give an example present in our daily, in local test situation we rarely find network problems or failures/loss in the connection due to the structure of our network that can be considered relatively stable. In the client the scenario changes, our applications report errors by email and it is not uncommon to receive connection problems of this type.

A little theoretical reference now, according to the official Mysql documentation this problem occurs due:

The error (2002) Can’t connect to ... normally Means that there is no Mysql server running on the system or that you are using an incorrect Unix socket file name or TCP/IP port number when trying to connect to the server. You should also check that the TCP/IP port you are using has not been blocked by a firewall or port blocking service.

The error (2003) Can’t connect to Mysql server on 'server' (10061) indicates that the network Connection has been refused. You should check that there is a Mysql server running, that it has network Connections enabled, and that the network port you specified is the one configured on the server.

Translating:

The error (2002) Can’t connect to ... usually means no there is no Mysql server running on the system or that you are using a Unix socket or TCP/IP port incorrectly. You should also check that the TCP/IP port used for connection does not is blocked by a firewall or other blocking service.

Error (2003) basically says the same thing.

What we did then to try to diagnose the problem was to perform a kind of mechanism of tolerance to failure in our applications, so that, if a certain error occurs (2002, 2003, among others) during the connection we notify that there was a loss of connection for the user that for a certain number of attempts at specified time intervals we perform the reconnection.

At the end our amount of errors of this type decreased drastically, so stay here the tip.