39009a4f0b581c3668b0bc5f2f626e8883c63a03
By:
javadroid

Problem on remote MySQL DB server connection

April 5, 2017 705 views
Nginx MySQL PHP Frameworks Ubuntu 16.04

Hi Digital Oceans,
I've two ubuntu 16.04 server configured with this tutorial. The first one (Called A) is the MySQL DB server and the second one (Called B) is the Laravel based server. I wanna use server A as DB of server B, So in the server B, I've changed the bind-address to 0.0.0.0 and created same DB users on both servers, but when I tried to connect server A with this code:

mysql -h <server-A-IP> -u <user>

I got this error:

ERROR 2003 (HY000): Can't connect to MySQL server on '185.129.169.33' (111)

Could you help me ?

3 Answers

Just comment the bind address on my.cnf file and restart the mysql service.

One thing you can also check to bind your server IP address in my.cnf file and restart mysql services.

Please let me know in it help you.

@javadroid

On the database server, when you create a MySQL User and Database, you need to set the host for the user to that of the connecting server (i.e. the server you're running your Laravel app on).

For example, on the database sever, to create a user, we'd use:

grant all on dbname.* to 'dbuser'@'LARAVEL_SERVER_IP' identified by 'dbpass';

Where dbname is the database name, dbuser is the database username, dbpass is the database password, and LARAVEL_SERVER_IP is the IP of your server running the Laravel application.

@jtittle, @vijaykumarmaurya

Finally, I just found the result. On the database server, I create two users:

CREATE USER  'dbuser'@'localhost' identified by 'dbpass' ;

and

CREATE USER  'dbuser'@'%' identified by 'dbpass' ;

and then grants all privileges:

 GRANT ALL PRIVILEGES ON *.* TO 'dbuser'@'localhost' WITH GRANT OPTION;

and

GRANT ALL PRIVILEGES ON *.* TO 'dbuser'@'%' WITH GRANT OPTION;
  • @javadroid

    The command I listed in my previous response should work without any issues :-).

    I mention this as what you're using in the above:

    1). Grants a single user access to any/all databases;

    2). Grants open access to the database server to any host/IP (i.e. %).

    With that sort of configuration, you'd need to make sure the database server is locked down and setup to only allow connections from the servers you allow through the firewall.

    Unless there's a true need, a general rule of thumb is one user is granted access to one database and the user is limited to a specific host when and where possible.

Have another answer? Share your knowledge.