Jes Challand
Jes Challand

Reputation: 11

mysqlsh failing with "MySQL Error 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'" (file in /var/run/mysqld)

Virtual newbie to MySQL so, if you see something that doesn't look right to you, please feel free to tell me and also, if you think I haven't provided a key piece of the puzzle, please say.

MySQL server 5.7 mysqlsh 8.0.23 ubuntu 22.04 Virtual Box 7.0.14

My MySQL server appears to be up:

jes@ubuntu2:~$ ps -ef | grep mysqld | grep -v grep
mysql      55368       1  0 10:54 ?        00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Server is using socket file /var/run/mysqld/mysqld.sock

jes@ubuntu2:~$ mysqladmin version -uroot -p
Enter password: 
mysqladmin  Ver 8.42 Distrib 5.7.42, for Linux on x86_64
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Server version      5.7.42
Protocol version    10
Connection      Localhost via UNIX socket
UNIX socket     /var/run/mysqld/mysqld.sock
Uptime:         15 min 24 sec
Threads: 1  Questions: 6  Slow queries: 0  Opens: 106  Flush tables: 1  Open tables: 99  Queries per second avg: 0.006

Socket file exists:

jes@ubuntu2:~$ ls -l /var/run/mysqld
total 8
-rw-r----- 1 mysql mysql 6 Feb  9 10:54 mysqld.pid
srwxrwxrwx 1 mysql mysql 0 Feb  9 10:54 mysqld.sock
-rw------- 1 mysql mysql 6 Feb  9 10:54 mysqld.sock.lock

Server is also accessible via TCP:

jes@ubuntu2:~$ mysqladmin -h `hostname` --port=3306 version -uroot -p
Enter password: 
mysqladmin  Ver 8.42 Distrib 5.7.42, for Linux on x86_64
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Server version      5.7.42
Protocol version    10
Connection      ubuntu2 via TCP/IP
TCP port        3306
Uptime:         18 min 19 sec
Threads: 1  Questions: 8  Slow queries: 0  Opens: 106  Flush tables: 1  Open tables: 99  Queries per second avg: 0.007

Connection via mysql client works OK:

jes@ubuntu2:~$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.42 MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

Connection via MySQL Workbench also works OK

Connection via MySQL Shell fails as below:

jes@ubuntu2:~$ mysqlsh
Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
MySQL Shell 8.0.23
Copyright (c) 2016, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
mysql-py> \sql
Switching to SQL mode... Commands end with ;

mysql-sql> \connect root@localhost
Creating a session to 'root@localhost'
Please provide the password for 'root@localhost': **************
MySQL Error 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
mysql-sql> 

my.cnf contains these entries:

jes@ubuntu2:~$ cat /etc/mysql/my.cnf | grep -v '#'
!includedir /etc/mysql/conf.d/

!includedir /etc/mysql/mysql.conf.d/

[mysqld]
port                           = 3306
socket                         = /var/run/mysqld/mysqld.sock

[client]
port                           = 3306
socket                         = /var/run/mysqld/mysqld.sock

/var/log/mysql/error.log contains:

2024-02-09T09:54:29.806778Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2024-02-09T09:54:29.806804Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2024-02-09T09:54:29.806846Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2024-02-09T09:54:29.818352Z 0 [Note] Event Scheduler: Loaded 0 events
2024-02-09T09:54:29.818858Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.42'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server (GPL)
2024-02-09T09:58:25.984367Z 2 [Note] Access denied for user 'jes'@'localhost' (using password: NO)

So why, if everything I can see is pointing to my socket file being in /var/run/mysqld, is mysqlsh looking for it in /tmp?

Thanks in advance for any guidance anyone is able to offer.

Upvotes: 1

Views: 445

Answers (0)

Related Questions