How do i connect to a local mysql server through socket?

Please fill out the detail below, it helps me investigate the bug:

Table of Contents Show

Cleaning the Backyard
Installing Mysql-server
Secure Mysql Installation
Making Sure Everything is Set
Using MySQL Workbench from Windows 10
How do I connect to MySQL locally?
Can't connect to local MySQL server through socket Mysqldump?
Can't connect to local MySQL server through socket RDS?
How do I connect to a local MySQL server from Workbench?

Driver :MySQL 5.7.23
DBngin build number: Version 3.1 (32)
macOS version:10.14.2
The steps to reproduce this issue:
When I connect the MySQL, suggesting this exception
Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock'

In addition, if I have a Mysql how local has been installed, How to let DBngin association

Issue

When the MySQL client connects to localhost, it attempts to use a socket file instead of TCP/IP. The socket file used is specified in /etc/mysql/my.cnf when the MySQL client is installed on the system. This is a MySQL socket file, which SingleStoreDB Cloud does not use by default. Therefore, connecting with localhost attempts to connect to MySQL and not SingleStoreDB Cloud.

Solutions

There are two solutions to solve this problem:

Specify 127.0.0.1 as the host instead of localhost. That is, mysql -h 127.0.0.1 -u root instead of mysql -h localhost -u root. If you omit the host (mysql -u root), the MySQL client will implicitly use localhost.
For SingleStoreDB Cloud, change the socket value in the /etc/mysql/my.cnf file to the location of your SingleStoreDB Cloud socket file as shown in the example below:

[client]
port          = 3306
socket        = /var/lib/memsql/data/memsql.sock

After sleepless nights trying to find a solution to this problem, I have finally come to one. Thus, I’ve decided to share it and I know I’ll need it again in the future (It’s not the first time I install MySQL in a recently formatted Windows 10 computer and face this problem).

Note that I’m running Debian in a Linux Subsystem for Windows 10 and I suppose it might work with any subsystem.

Cleaning the Backyard

First of all, let’s start from scratch, you want to make sure you don’t have any remaining MySQL garbage in your system:

sudo apt-get remove --purge mysql*

After that, check if everything is clean, run the following code, and hope nothing shows up

dpkg -l | grep mysql

If that doesn’t do the trick, like in my case I still had some installed…

Try to purge them individually, like so (php is just an innocent amidst the sinful, so leave it be) :

sudo apt-get remove --purge mysql-apt-config

And now, clean everything else

sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean

Installing Mysql-server

Now, we can start from scratch and install it. First, we have to download our configuration file using (be sure to get the latest file, mine was 8.15–1. you can find them here http://repo.mysql.com):

wget http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb

Once it is done, install it using:

sudo dpkg -i mysql-apt-config_0.8.15-1_all.deb

It will prompt you to choose a MySQL version, take mysql-5.7. I was taken by the greed of choosing 8.0, but it gave me more problems than I had wished for, then humbly get 5.7:

Now, we can properly install mysql-server:

sudo apt update && sudo apt install mysql-server

You’ll need to enter a password…make sure you’ll remember it ;)

Secure Mysql Installation

Now, we want to change some default settings that might not be so secure, then run:

sudo mysql_secure_installation

Enter root password (I’ve told you to remember it) and…

Here’s when my nightmares started and I stumbled with:

Error: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)

I noticed then that by default this service is not started, we have to start it, but using sudo service mysql start or sudo service mysqld start won’t start it, neither will sudo systemctl start mysql. They will just give you a bunch of problems, you should:

sudo /etc/init.d/mysql start

Now, we can again run:

sudo mysql_secure_installation

The settings I’ve used were:

Would you like to setup VALIDATE PASSWORD plugin? y
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Change the password for root?: n
Remove anonymous users? y
Disallow root login remotely? n
Remove test database and access to it? y
Reload privilege tables now? y

Making Sure Everything is Set

Now, before trying to connect to mysql, make sure we have a root has an authentication string. Enter MySQL Monitor:

mysql -u root  -p

And then enter:

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

Make sure you see something this

Now, if the authentication string is empty, we have to set up a new password for root. Still, in MySQL Monitor, run:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

And then reload grant tables:

mysql> FLUSH PRIVILEGES;

Now, check if our root user has an authentication_string:

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

Using MySQL Workbench from Windows 10

I like connecting to the database from Windows itself, and once I have installed workbench in Windows (https://dev.mysql.com/downloads/workbench/), it opens up without any connections.

Now we have to create a new connection, by clicking in the add button:

Add a name to the connection and leave the other settings as they are:

Now you can simply connect to the database in your Linux Subsystem from Windows.

How do I connect to MySQL locally?

To Connect to a MySQL Database.

Click Services tab..

Expand the Drivers node from the Database Explorer. ... .

Enter User Name and Password. ... .

Click OK to accept the credentials. ... .

Click OK to accept the default schema..

Right-click the MySQL Database URL in the Services window (Ctrl-5)..

Can't connect to local MySQL server through socket Mysqldump?

It means either the MySQL server is not installed/running, or the file mysql. sock doesn't exist in /var/lib/mysql/ . There are a couple of solutions for this error. Then try to connect again.

Can't connect to local MySQL server through socket RDS?

You could try using "127.0. 0.1" if the socket connector is not enabled/working. In that case, you should probably check if your MYSQL server is actually running. You can also force using a socket with the socket parameter (-S with /usr/bin/mysql) and force TCP/IP by providing a port (-P with /usr/bin/mysql.)

How do I connect to a local MySQL server from Workbench?

Launch the MySQL Workbench from the desktop. Click the Local instance MySQL80 button, and click Connect to begin the configuration process. When prompted, enter in the MySQL server root password which was created during the MySQL installation process. Optionally, you may check the Save password in vault check box.