mysql client connect to nginx error 10060

Question

i use nginx for mysql the

http {
    include       /etc/nginx/mime.types;
    default_type  application/octet-stream;

    log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                      '$status $body_bytes_sent "$http_referer" '
                      '"$http_user_agent" "$http_x_forwarded_for"';

    access_log  /var/log/nginx/access.log  main;

    sendfile        on;
    #tcp_nopush     on;

    #keepalive_timeout  0;
    keepalive_timeout  65;

    #gzip  on;

    # Load config files from the /etc/nginx/conf.d directory
    # The default server is in conf.d/default.conf
    include /etc/nginx/conf.d/*.conf;
upstream stream_backend {
        server xx.xx.xx.4:3306;
        server xx.xx.xx.1:3306;
    }
   server {
        listen     3307;
        server_name xx.xx.xx.1;
    }
}

and i use mysql client connect to xx.xx.xx.1:3307 it report error

failed to connect to mysql at xx.xx.xx.1:3307 with user root

the nginx log is

Answer 1

Outside of your http section put something like this :

http {
[...]
}

stream {

    upstream stream_backend {
       server ip_mysql1:3306;
       server ip_mysql2:3306;
    }

    server {
       listen ip_nginx:3307;
            proxy_pass stream_backend;
    }
}

You can find a lot of options there to improve how you proxy behaves.

Answer 2

The reason for that is because you are using upstream directive for trying to load balance the MYSQL ( which should be based on TCP ).

This guide should help you :-

https://www.nginx.com/resources/admin-guide/tcp-load-balancing/

You need to write the upstream inside the stream { } block.