Unable to connect to local MySQL server via homebrew socket

Question

I recently tried installing MySQL with homebrew ( brew install mysql ), and when I try to start it, I get the following error:

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

No /tmp/mysql.sock or /var/lib/mysql.sock .

I searched and did not find the mysql.sock file.

How can i fix this?

Answer 1

When you started the server through

mysql.server start

you should see the socket in /tmp/mysql.sock. However, the system seems to expect this in /var/mysql/mysql.sock. To fix this, you must create a symbolic link in / var / mysql:

sudo mkdir /var/mysql

sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

This solved it for me. Now my phpMyAdmin successfully works with localhost and 127.0.0.1.

The loan goes to Henry

Answer 2

It looks like your mysql server is not running. Usually I run the stop command and run it again:

 mysqld stop mysql.server start 

Same error and this works for me.

Answer 3

Try connecting using "127.0.0.1" instead of "localhost".

Answer 4

1) If you see "mysql stopped" when you run the command below;

 brew services list 

2) and if you can start mysql using the command below;

 mysql server start 

it means; mysql may start manually, but does not start automatically when the operating system starts. Adding mysql to services will solve this problem. To do this, you can run the command below;

 brew services start mysql 

After that, you can restart the operating system and try connecting to mysql to see if it starts automatically. I did the same and stopped getting errors below;

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

Hope this helps.

Answer 5

I have several directories from another mysql (8.0) installation that have not been deleted.

I solved this by doing the following:

Uninstall MySQL first

 brew uninstall mysql@5.6 

Delete folders / files that have not been deleted

 rm -rf /usr/local/var/mysql rm /usr/local/etc/my.cnf 

Reinstall mysql and bind it

 brew install mysql@5.6 brew link --force mysql@5.6 

Turn on and start the service

 brew services start mysql@5.6 

Answer 6

The file /tmp/mysql.sock is probably named /tmp/mysql.sock because it is located in a temporary folder. A named pipe is a special file that will never be stored permanently.

If we create two programs and want one program to send a message to another program, we could create a text file. We have one program that writes something to a text file, and another program reads what our other program wrote. What a channel is, except that it does not write the file to our computer’s hard drive, IE does not store the file permanently (as we do when we create the file and save it).

A socket is exactly the same as a pipe. The difference is that sockets are usually used over the network - between computers. A socket sends information to another computer or receives information from another computer. Both channels and sockets use a temporary file for sharing so that they can "communicate".

It is difficult to determine which MySql uses in this case. It doesn't matter though.

The mysql.server start command should start a "server" (program), executing an endless loop, which will create this special file and wait for changes ( listen records).

After this, a common problem may be that MySql does not have permission to create a file on your computer, so you may have to grant it root privileges.

 sudo mysql.server start 

Answer 7

After installing macos mojave, I had to erase the mysql folder in /usr/local/var/mysql and then reinstall it using brew install mysql otherwise everything related to permissions would occur everywhere.

Answer 8

I got the same error and it helped me:

 $ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents $launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist $mysql -uroot mysql> 

Answer 9

Since I spent quite a lot of time trying to solve this problem, and always came back to this page when I was looking for this error, I will leave my solution here, hoping that someone will save the time I lost. Although in my case I use mariadb, not MySql, you can still adapt this solution to your needs.

My problem

same thing, but my settings are slightly different (mariadb instead of mysql):

Set mariadb with homegrown

 $ brew install mariadb 

Launched a demon

 $ brew services start mariadb 

Tried to connect and got the above error

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

My decision

find out which my.cnf files are used by mysql (as suggested in this comment ):

 $ mysql --verbose --help | grep my.cnf /usr/local/etc/my.cnf ~/.my.cnf order of preference, my.cnf, $MYSQL_TCP_PORT, 

check where the Unix socket file works (almost as described here ):

 $ netstat -ln | grep mariadb .... /usr/local/mariadb/data/mariadb.sock 

(you may want to grep mysql instead of mariadb)

Add the found socket file to ~/.my.cnf (if necessary, create it) (provided that ~/.my.cnf was specified when mysql --verbose... started mysql --verbose... -command from above):

 [client] socket = /usr/local/mariadb/data/mariadb.sock 

Restart your mariadb:

 $ brew services restart mariadb 

After that, I could start mysql and got:

 $ mysql -uroot ERROR 1698 (28000): Access denied for user 'root'@'localhost' 

Therefore, instead, I ran the command with superuser privileges and after entering the password I received:

 $ sudo mysql -uroot MariaDB [(none)]> 

---

Notes:

1. I’m not quite sure about the groups in which you want to add the socket, at first I had it [client-server], but then I thought that [client] should be enough. I changed this and it still works.

2. When starting mariadb_config | grep socket mariadb_config | grep socket mariadb_config | grep socket mariadb_config | grep socket I get: --socket [/tmp/mysql.sock] which is a bit confusing since it seems that /usr/local/mariadb/data/mariadb.sock is the actual place (at least on my machine )

3. I wonder where I can configure /usr/local/mariadb/data/mariadb.sock so that it really is /tmp/mysql.sock so that I can use the default settings instead of editing my .my.cnf (but I'm too tired to understand this ...)

4. At some point, I also did the things mentioned in other answers before coming up with this.

Answer 10

I ran into the same problem on my mac and solved it by following the following tutorials

https://mariadb.com/resources/blog/installing-mariadb-10116-mac-os-x-homebrew

But remember to kill or uninstall the old version before proceeding.

Teams

 brew uninstall mariadb xcode-select --install ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)" - See more at: https://mariadb.com/resources/blog/installing-mariadb-10116-mac-os-x-homebrew#sthash.XQoxRoJp.dpuf brew doctor brew update brew info mariadb brew install mariadb mysql_install_db mysql.server start 

Answer 11

Just to add to these answers, in my case I did not have a local mySQL server, it worked inside the docker container. Therefore, the socket file does not exist and will not be accessible to the mysql client.

The sock file is created by mysqld, and mysql uses this to communicate with it. However, if your mySql server is not running locally, it does not require a sock file.

By specifying the hostname / ip, a sock file is not required, for example.

 mysql --host=127.0.0.1 --port=3306 --user=xyz --password=xyz 

Answer 12

You need to run mysql_install_db - the easiest way is if you are in the installation directory:

 $ cd /usr/local/Cellar/mysql/<version>/ $ mysql_install_db 

Alternatively, you can mysql_install_db a basedir parameter as follows:

 $ mysql_install_db --basedir="$(brew --prefix mysql)" 

Answer 13

just to complete this thread. therefore, MAMP (PRO) is used quite often

the way here

 /Applications/MAMP/tmp/mysql/mysql.sock 

Answer 14

A socket is not created if you restart mysqld too quickly after it stops:

 brew services stop mysql;brew services start mysql ls /tmp/mysql.sock // socket not created 

Waiting 3 seconds before restarting mysql fixes this:

 brew services stop mysql;sleep 3;brew services start mysql sleep 1.5 // Note that it takes a second after mysql starts to create the socket ls /tmp/mysql.sock // socket created! 

Answer 15

I manually started mysql in the system settings panel, initializing the database and then starting it. This solved my problem.

Answer 16

After the restart, I could not connect to the local mariadb, the search also led me to this page, and I wanted to share my solution with you.

I noticed that the my.cnf.d directory in / usr / local / etc / is missing.

This is a known bug with homebrew, which is described and resolved there. https://github.com/Homebrew/homebrew-core/issues/36801

quick way to fix: mkdir / usr / local / etc / my.cnf.d

Answer 17

When running mysql_secure_installation and entering a new password, I got:

---

Error: cannot connect to local MySQL server through socket '/tmp/mysql.sock' (2)

---

With this answer, I noticed the following:

 netstat -ln | grep mysql 

This did not return anything, and I realized that it was not a .sock file.

So, I added the following to my my.cnf file (either in /etc/my.cnf , or in my case / usr / local / etc / my. CNF ).

Under:

 [mysqld] socket=/tmp/mysql.sock 

Under:

 [client] socket=/tmp/mysql.sock 

This is based on this post .

Then stop / start mysql again and try mysql_secure_installation again , which finally allowed me to enter a new root password and continue with other settings.

I hope this helps someone. Dude, I hate this ...

Answer 18

In my case, the culprit was found in the log files:

 $ tail /usr/local/var/mysql/<hostname>.lan.err 2019-09-19 7:32:21 0 [ERROR] InnoDB: redo log file './ib_logfile0' exists. Creating system tablespace with existing redo log files is not recommended. Please delete all redo log files before creating new system tablespace. 2019-09-19 7:32:21 0 [ERROR] InnoDB: Database creation was aborted with error Generic error. You may need to delete the ibdata1 file before trying to start up again. 

So I renamed ib_logfile0 to get rid of the error (later I had to do the same with ib_logfile1 ).

 mv /usr/local/var/mysql/ib_logfile0 /usr/local/var/mysql/ib_logfile0_bak mv /usr/local/var/mysql/ib_logfile1 /usr/local/var/mysql/ib_logfile1_bak brew services restart mariadb 

Answer 19

rm -rf / usr / local / var / mysql / ib_logfile *