The error message that every MySQL user knows

The solution is simple: copy & paste the error message, google it, and go to the related stackoverflow question with hundreds of votes.

But maybe let’s do something different this time. Don’t apply blindly a solution found online but take your time to dig deeper into the topic, and actually understand what is going on.

Sooner or later when working with a MySQL database, you will see this error message on your screen. It may be on day one of your journey as a software engineer, it may be as well ten years later after growing to a senior position in the industry.

Can’t connect to local MySQL server through socket

We’ve all seen it, right? Fix your connection parameters to the database and then you are all set.

But let’s stop here for a second.

Which socket the error message is about?
Why does the MySQL client want to connect through this socket?
I’ve never set anything up with any sockets?!

So let’s go! Read the article to understand four different ways of connecting to a MySQL database (TCP/IP, Unix socket, named pipe, shared memory), and to learn how to solve common connection issues.

Then you will be one step closer to becoming a more conscious software engineer who doesn’t only follow the instructions found over the Internet — but is a person who actually knows what happens under the hood.

Hint: on database.guru we publish high-quality MySQL articles and tutorials like this one. Subscribe to our mailing list and let us share our database expertise with you 🙌

So, first things first. When trying to connect to a MySQL server, the connection from the client can be established using four different protocols:

• TCP/IP
• Unix socket file
• named pipe
• shared memory

And this is what the whole story is about. Let’s explore these connection methods and see which one is applicable in your scenario (and learn some MySQL server configuration options on the way).

Connecting to MySQL with TCP/IP

Usually, when you want to connect to a MySQL database, you are being given a set of credentials like:

• database host,
• database port,
• username,
• password.

This is a clear indicator that the connection is supposed to be established via the TCP/IP protocol. Your client application will connect to the given network host (which can be either a domain name or an IP address) and to the given port (the default one being 3306). Then, it will try to authenticate using the given username and password.

Doing it from the command line would be something like:

mysql -h mydatabase.local -u john -p 

This is the only way of connecting to a MySQL server that is not running on your machine.

Also, connecting through TCP/IP is the most universal way — it is supported no matter which operating system is running on the server and on your machine. Connecting from a Windows client to a Linux server, or vice versa, or having any other combination of the client-server setup — it is all possible if you connect with TCP/IP.

TCP/IP connection also supports TLS/SSL encryption to ensure proper security.

Errors when connecting to MySQL with TCP/IP

If connecting with the TCP/IP protocol fails, there may be a few possible issues. Usually, you will see errors like the ones below. Let’s see how to deal with them:

ERROR 1045 (28000): Access denied for user ‘john’@’mydatabase.local’ (using password: YES)

ERROR 2005 (HY000): Unknown MySQL server host ‘mydatabase.local’ (22)

ERROR 2003 (HY000): Can’t connect to MySQL server on ‘mydatabase.local:3306’ (111)

You provided the wrong connection parameters

Make sure that you didn’t misspell the connection parameters like the username, the password, and the hostname. Also, verify that the given user actually exists on the MySQL server and is allowed to connect to the database:

mysql> SELECT * FROM mysql.user;

Connections to the database server are being blocked

Your firewall may block TCP connections on port 3306. In such a case, you need to adjust firewall rules and open this port on your machine.

If the MySQL server is running in the cloud, then such connection issues may be caused by virtual network settings. E.g. in the AWS cloud, you can allow or deny access to a specific database instance using security groups. Make sure that such settings are properly configured to allow the right set of client hosts to be able to connect.

MySQL skip_networking option blocks incoming connections

— with the skip_networking variable set to ON. You need to disable this option in order to be able to connect from other hosts than the one where the MySQL server is running.

To check what is the current value of this variable, execute the following query:

mysql> SHOW VARIABLES LIKE 'skip_networking';

MySQL bind_address option blocks incoming connections

— using the bind_address option.

E.g. if bind_address is set to 127.0.0.1 , then you will be able to connect to your MySQL database only from the same machine where the server is running.

To check what is the current value of this variable, run:

mysql> SHOW VARIABLES LIKE 'bind_address';

Connecting to MySQL with Unix socket

Another way of connecting to a MySQL server is to use the Unix socket file. And this is the case that often results in the well-known error message:

> mysql 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

This error usually happens when you run the MySQL client command ( mysql ) without any parameters, and on a Unix machine (Linux or macOS). Ok, so why then it’s trying to connect through a socket?

A Unix socket file connection is faster than TCP/IP.

Connecting through a socket it’s a way of exchanging data between different processes (applications) running on the same machine. As it doesn’t go through a network interface, it‘s more efficient than using the TCP/IP protocol.

So then, the MySQL client uses the socket file as the default way of connecting to the server — unless you explicitly specify connection parameters like the host and the port.

Socket file connection works:
(1) only for Unix machines
(2) only on the same host where the MySQL server is running.

Such connection is secure by default and there’s no need to enable encryption for it.

But there’s a catch…

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

You can see this error when you are trying to connect through TCP/IP to your local machine with the following command:

> mysql -h localhost

What to do if MySQL cannot connect to localhost

In such a case, the MySQL client interprets -h localhost still as an attempt to connect via a socket file. Usually, the solution is to enter the localhost IP address:

> mysql -h 127.0.0.1

Then, the MySQL client will properly interpret this value as a network host — and it will use the TCP/IP protocol to connect.

If the connection fails, you will immediately see the difference comparing to the previous error message — now it doesn’t complain about the socket file anymore:

> mysql -h 127.0.0.1
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1:3306' (61)

Connecting to MySQL with a named pipe

Named pipe is a way of exchanging data between applications running on the same machine — that runs on a Windows operating system. Looks similar to Unix socket files, right?

Named pipe connection works:
(1) only for Windows machines
(2) only on the same host where the MySQL server is running.

Although the named pipe transport does allow connections from other hosts, this feature is not supported by the MySQL server itself.

How to connect to localhost MySQL server with a named pipe

The Windows MySQL client will interpret the host . as an attempt to connect to a named pipe.

On the other hand, if you want to connect to a MySQL server using the TCP/IP protocol, you should specify 127.0.0.1 as the host.

What can go wrong with named pipe connections

MySQL named_pipe option blocks named pipe connections

— with the named_pipe server variable, which is OFF by default.

To check what is the current value of the variable, execute the following query:

mysql> SHOW VARIABLES LIKE 'named_pipe';

MySQL named_pipe_full_access_group option blocks named pipe connections

A MySQL system variable named_pipe_full_access_group determines which Windows user groups are allowed to connect with a named pipe. Make sure that a proper value is set there so that the connection from your user is allowed.

Connecting to MySQL with shared memory

Last but not least — the fourth option of connecting to a MySQL server: shared memory.

Shared memory connection works:
(1) only for Windows machines
(2) only on the same host where the MySQL server is running.

Such connection brings noticeable performance benefits comparing to using the TCP/IP protocol and it is secure by default.

What can go wrong with shared memory connections

MySQL shared_memory option blocks shared memory connections

— similar as the named_pipe server variable, there’s also the shared_memory variable which determines whether the MySQL server will allow clients to connect through shared memory.

To check what is the current value of the variable, execute the following query:

mysql> SHOW VARIABLES LIKE 'shared_memory';

The value is OFF by default.

MySQL lost connections using shared memory

There’s a significant disadvantage of connecting through the shared memory. If a connection from the client to the MySQL server is lost e.g. because of the fact that the client application died unexpectedly, then the server cannot detect it.

It may result in keeping some resources locked on the MySQL server-side and interfering with the queries from the next connections that are being established to the server.

To avoid such issues, make sure that interactive_timeout and wait_timeout values are set to probably much lower numbers than the default of eight hours.

Now I’m lost, what is the best way to connect to a MySQL server?

TLDR:

• if connecting to a remote host → TCP / IP
• if connecting to localhost on Linux / macOS → socket file or TCP/IP
• if connecting to localhost on Windows → named pipe or TCP / IP; you may consider shared memory if you need high performance and you are prepared to handle dead connections

Cheers!
Jacek Barecki @ database.guru