Search code examples
mysqlnginxplesk

Plesk nginx Ubuntu 16 - external MySQL connection refused

I need external connection to MySQL. Using Plesk/Ubuntu 16/nginx. In firewall policy the port 3306 is allowed from any IP.

Is it somewhere else where it shall be allowed? If so, what file is needed to be edited?

Solution

  • You have to make changes to MySQL configuration, and allow remote access for the user.

    1. Open MySQL configuration file. For RHEL-based distributions - /etc/my.cnf, for Debian-based - /etc/mysql/my.cnf.

    2. Change bind-address parameter to bind-address = 0.0.0.0 and make sure that skip-networking parameter is not defined.

    3. Restart MySQL daemon:

    # service mysql restart

    Or:

    # service mysqld restart

    Or:

    # service mariadb restart

    The service name depends on the installed MySQL version. You can do all three if you are not sure. It will do no harm unless the error was made in my.cnf.

    4. Grant the access to remote IP address and login to MySQL. For example, if you want to allow access to database called database for user user with password password and remote IP address 203.0.113.2:

    GRANT ALL ON database.* TO user@'203.0.113.2' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

    To create a user that has access to all databases, execute the following query:

    GRANT ALL PRIVILEGES ON *.* TO 'user'@'203.0.113.2' IDENTIFIED BY 'password' REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
FLUSH PRIVILEGES;

    5. You can verify that MySQL server is listening on the correct IP address using netstat. Here is the desired output - 0.0.0.0 address:

    # netstat -anp | grep :3306
tcp6       0      0 0.0.0.0:3306            :::*                    LISTEN      2723/mysqld