Unable to connect to MySQL database from vps using localhost

Question

I have two environments: my local machine Mac and linux on VPS. The problem is that I can not connect using localhost to my db from VPS. On my local machine everything works fine.

Local:

mysql -u root mydb -h 127.0.0.1 --password=password --port=2345 //OK
mysql -u root mydb -h 0.0.0.0 --password=password --port=2345   //OK
mysql -u root mydb -h localhost --password=password --port=2345 //OK

Even using public ip of VPS I'm able to connect to remote db from local machine.

VPS:

mysql -u root mydb -h 127.0.0.1 --password=password --port=2345 //OK
mysql -u root mydb -h 0.0.0.0 --password=password --port=2345   //OK
mysql -u root mydb -h localhost --password=password --port=2345 //KO <-

Error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I used the following docker file to create my image.

DockerFile:

FROM mysql:8.0
COPY *.sql /docker-entrypoint-initdb.d/

docker-compose.yaml:

version: '3.7'
services:
  ls:
    image: ***/db-container:v6 # <-my image
    container_name: db-container
    restart: always
    environment:
      MYSQL_DATABASE: 'mydb'
      MYSQL_USER: 'user'
      MYSQL_PASSWORD: 'password'
      MYSQL_ROOT_PASSWORD: 'password'
    ports:
      # <Port exposed> : < MySQL Port running inside container>
      - '2345:3306'
    expose:
      # Opens port 3306 on the container
      - '3306'
    volumes:
      - db-container:/var/lib/mysql
volumes:
  db-container:

Query inside container:

mysql> SELECT host, user FROM mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| %         | user             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+


mysql> SELECT * FROM performance_schema.host_cache; 
Empty set (0.02 sec)

For the connection that fails (KO), please document the error message seen. — Marc Alff, Nov 18 '19 at 08:53
Here is a possible solution to your problem: https://stackoverflow.com/questions/33001750/connect-to-mysql-in-a-docker-container-from-the-host — Armino Popp, Nov 19 '19 at 16:58
Did you try to use the IP of the docker container instead of 127.0.0.1?... like 10.0.1.12:2345 ? @Alexey — Akhil, Nov 25 '19 at 08:17

Rick James · Answer 1 · 2019-11-18T19:49:04.370

3

"localhost" says to use a socket. But VPS probably allows only TCP/IP connections, not "socket".

edited Nov 18 '19 at 19:49

answered Nov 18 '19 at 19:00

Rick James

135,179
13
127
222

@Alexey Are you using Docker on your local machine as well? – Lightness Races in Orbit Nov 18 '19 at 19:44
yes, my local machine(MacBook) in the question is written as LOCAL and remote machine(Ubuntu) is mentioned as VPS. I execute the same command to connect to mysql and have different results. – Alexey Nov 18 '19 at 19:46
OK, maybe it is the VPS that denying "socket" access. – Rick James Nov 18 '19 at 19:48

score 2 · Answer 2 · answered Nov 24 '19 at 13:33

I guess on local machine, you are not using docker. However, on vps, you are.

On you local, mysql is installed on the OS itself, hence, it has exposed its socket file at /var/run/mysqld/mysqld.sock. However, on VPS, the socket is inside docker.

The highlight here is the difference between localhost and 127.0.0.1 in reference to mysql. On unix based systems(including mac), localhost connects via socket, while 127.0.0.1 connects via TCP/IP.

Try adding following under docker-compose volumes section. See if it works then.

volumes:
    - /var/run/mysqld/mysqld.sock:/var/run/mysqld/mysqld.sock

score 1 · Answer 3 · answered Nov 18 '19 at 08:52

1

There are many different root causes for a connection denied.

Since it is possible to execute queries inside the container, the best course of action is to inspect the performance_schema.host_cache table:

SELECT * FROM performance_schema.host_cache;

It should point more precisely at the problem.

Doc: https://dev.mysql.com/doc/mysql-perfschema-excerpt/8.0/en/host-cache-table.html

answered Nov 18 '19 at 08:52

Marc Alff

8,227
33
59

mysql> SELECT * FROM performance_schema.host_cache; returns: Empty set (0.00 sec) – Alexey Nov 18 '19 at 19:44
See @rick james answer. The failure happens in the client, who can not even connect to the server, so the host_cache table will show nothing. – Marc Alff Nov 18 '19 at 19:47
But why does it work on local machine(both local and remote use the same docker image and docker-compose file),the same query returns the empty results as well on the local machine – Alexey Nov 18 '19 at 19:56
Are you sure the localhost connection from the mac goes to the correct server ? A UNIX (not TCP/IP) connection using /var/run/mysqld/mysqld.sock will point to a server running on the same box, and ignore the TCP/IP port number. Docker knows nothing about /var/run/mysqld/mysqld.sock and will not be involved here. – Marc Alff Nov 18 '19 at 20:02
yes, I'm able to connect to MySQL inside docker container and see all my tables. – Alexey Nov 19 '19 at 08:10

Unable to connect to MySQL database from vps using localhost

3 Answers3