Can not connect to MySQL server on 'IP (4)

Question

Can not connect to MySQL server on 'IP (4)

Navigation

#1 by (1 votes)

1

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 applications (legacy) that does not use any framework, and therefore uses the natively PHP to connect to MySQL, proceed 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, so it still uses the mysql driver instead of mysqli. Even in our most modern applications with frameworks, the problem also occurs.

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

The content of the MySQL my.cnf file 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

php mysql servidor

asked by anonymous 26.02.2015 / 21:50

1 answer

Global Filters in Entity Framework select field text separated by comma return lines

score 1 · Answer 1

Well, the specified problem is too broad, and may be a misconfigured configuration on the server until network instability issues.

I will give a present example in our daily situation, in a local test situation we rarely encounter network problems or connection failures / loss 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 bit of theoretical reference now, according to the official MySQL documentation this problem occurs due to:

The error (2002) Can not 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   you have not been blocked by a firewall or port blocking service.

The error (2003) Can not 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 not connect to ... usually means no there is no MySQL server running on the system or that you are using a Unix socket or the TCP / IP port incorrectly. You you must also verify that the TCP / IP port used for connection is not is blocked by a firewall or other blocking service.

The error (2003) basically says the same thing.

What we did then to try to diagnose the problem was to make a kind of fault tolerance mechanism in our applications, so that, in the event of a certain error (2002, 2003, among others) during the connection, we notified that there was a loss of connection to the user that for a given number of retries at specified time intervals we reconnect.

In the end our number of errors of this type has decreased dramatically, so that's the tip.