Reputation: 3265

Can't connect to local MySQL server through socket homebrew

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

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

There is no /tmp/mysql.sock nor a /var/lib/mysql.sock.

I've searched and haven't found any mysql.sock file.

How can I fix this?

Upvotes: 148

Answers (28)

Rizwan Ali

Reputation: 31

I got this error yesterday when trying to connect to my mysql database after getting it running. There is a lot of conflicting information on the web due to how you download mysql, versions, which OS you’re on, etc.

The steps below worked for me, and can probably work for you, too! However, you will need to install HomeBrew if you don’t have it already.

brew doctor
brew remove mysql

Above remove mysql will not remove databases, So you can easily run the above commands.

brew install mysql

Once you installed the latest version of mysql then try to see the folder of mysql installation by running

which mysql

It should show like this one "/opt/homebrew/bin/mysql" The final step should be writable permissions for the mysql var folder by running

chmod -R 0777 /opt/homebrew/var/mysql

This has resolved socket and PID error for me.

Thanks

Upvotes: 1

Penn Evam

Reputation: 383

If you install mysql via brew in Apple Silicon( M1/M2/M3/M4...) maybe you can try this steps

brew install [email protected]
brew postinstall [email protected] 
brew services start [email protected]
mysql -uroot -p

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.35 Homebrew

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.

if it still can't work

maybe check the log there

/opt/homebrew/var/mysql/

like /opt/homebrew/var/mysql/{yourName}.local.err

To delete and reinitialize the MySQL database (which will lead to the loss of all unbacked data), you can consider the following:

Stop the MySQL service (if it is running)

brew services stop [email protected]

Back up the current MySQL data directory (in case you need to roll back)

cp -R /opt/homebrew/var/mysql /path/to/backup_mysql_data/

Delete InnoDB-related system files

rm -f /opt/homebrew/var/mysql/ibdata1

rm -f /opt/homebrew/var/mysql/ib_logfile0

rm -f /opt/homebrew/var/mysql/ib_logfile1

find /opt/homebrew/var/mysql/ -name "*.ibd" -exec rm -f {} \;
After cleaning, re-initialize the MySQL data directory.

brew postinstall [email protected]
Start the MySQL service

brew services start [email protected]

Upvotes: 1

alxp

Reputation: 6331

This happened to me today 2023-02-23 after a homebrew update.

Homebrew had created a new my.cnf.default file, I backed up my old one and replaced it with this new default and restarted Mariadb and everything worked including the old root password.

Upvotes: 0

Emile

Reputation: 1

I just ran into this problem, I did some tricks but for me it didn't work. If you used macOS, here is what I did. We start by uninstalling mysql and then reinstalling it.

brew uninstall mysql
brew install mysql

Note: you must have homebrew installed

Upvotes: 0

Allison

Reputation: 2336

I also ran into this... it seemingly resulted from some leftover artifacts of multiple mysql installs on my dev machine. Every time I attempted to start or restart the mysql service it would crash. Ultimately, working through the err file helped me solve my issue.

Setup:

M1 Macbook Pro running OS 12 (Monterey)
Homebrew install of [email protected]

At various points during troubleshooting I tried uninstalling everything mysql in my Homebrew list, deleting /opt/homebrew/var/mysql, and reinstalling [email protected] to no avail. I also tried restarting my machine.

I was able to validate what was happening when the service failed to start by reviewing the err (e.g., MyComputerName.local.err) log that it was spitting out into /opt/homebrew/var/mysql/ when I tried to start/restart the service via Homebrew. In the beginning, I was seeing things like this that pointed to issues with the config:

mysqld: Table 'mysql.plugin' doesn't exist
TIMESTAMPZ 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
TIMESTAMPZ 0 [ERROR] unknown variable 'mysqlx-bind-address=127.0.0.1'
TIMESTAMPZ 0 [ERROR] Aborting

I deleted my.cnf and my.cnf.default in /opt/homebrew/etc and attempted to start the service again. Then, the failure changed to this:

TIMESTAMPZ 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
TIMESTAMPZ 0 [ERROR] Fatal error: Failed to initialize ACL/grant/time zones structures or failed to remove temporary table files.
TIMESTAMPZ 0 [ERROR] Aborting

At this point I noticed several mysql lock files in /opt/homebrew/var/homebrew/locks/ (mysql.formula.lock, [email protected], [email protected]); I deleted those files, as well as anything mysql in /opt/homebrew/var/homebrew/linked.

After that, I was able to start mysql without any issues.

Upvotes: 2

avantdev

Reputation: 2724

Warning, this will wipe your databases, take a backup if you wish to keep them

I had some directories left from another mysql(8.0) installation, that were not removed.

I solved this by doing the following:

First uninstall mysql

brew uninstall [email protected]

Delete the folders/files that were not removed

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

Reinstall mysql and link it

brew install [email protected]
brew link --force [email protected]

Enable and start the service

brew services start [email protected]

Upvotes: 107

Jaime Adalberto Lomeli

Reputation: 11

If brew does not complete "postinstall" I only have to use:

sudo chown -R $(whoami) /usr/local/*

then completed postinstall by:

brew postinstall [email protected]

next step just start [email protected] service.

It worked for me on MacOS Monterey.

Upvotes: 1

Vikas Bansal

Reputation: 2910

[LINUX]

Though answer is expected to be for MacOS only but in Linux we may face the same Error.

I was facing the same issue in Linux. I ran this command:

sudo /etc/init.d/mysql.server start

and I was able to run the MySQL server

Ref. https://gist.github.com/vinodpandey/1a4b5b8228f9a000ca236820185fc3bc

Upvotes: 0

Bonjiro

Reputation: 345

If "mysqld" IS running, it's possible your data is corrupted. Try running this:

mysqld

Read through the wall of data, and check if mysqld is reporting that the database is corrupted. Corruption can present in many unintuitive ways:

mysql -uroot returns "ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)".
mysql.server start returns "ERROR! The server quit without updating PID".
Sequel Pro and MySQL Workbench responds that they can't connect to MySQL on localhost or 127.0.0.1.

To recover your data, open my.cnf and add the following line in the [mysqld] section:

innodb_force_recovery=1

Restart mysqld:

$ brew services restart [email protected]

Now you can connect to it, but it’s in limited read-only mode.

If you're using InnoDB, run this to export all your data:

$ mysqldump -u root -p --all-databases --add-drop-database --add-drop-table > data-recovery.sql

The file is created in your ~ dir. It may take some time.

Once finished, remove innodb_force_recovery=1 from my.cnf, then restart mysql in normal mode:

$ brew services restart [email protected]

Drop all the databases. I did this using Sequel Pro. This deletes all your original data. Make sure your data-recovery.sql looks good before doing this. Also consider backing up /usr/local/var/mysql to be extra careful.

Then restore the databases, tables, and data with this:

$ mysql -uroot < ~/data-recovery.sql

This can be a long import/restoration process. Once complete, you’re good to go!

Thanks go to https://severalnines.com/database-blog/my-mysql-database-corrupted-what-do-i-do-now for the recovery instructions. The link has further instructions on MyISAM recovery.

Upvotes: 5

Berk

Reputation: 1329

If you are able to see "mysql stopped" when you run below command;
```
brew services list
```
and if you are able to start mysql with below command;
```
mysql server start
```

this means; mysql is able to start manually, but it doesn't start automatically when the operating system is started. Adding mysql to services will fix this problem. To do so, you can run below command;

brew services start mysql

After that, you may restart your operating system and try connecting to mysql to see if it started automatically. I did the same and stop receiving below error;

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

I hope this helps.

Upvotes: 24

Must Impress

Reputation: 347

When running mysql_secure_installation and entering the new password I got:

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

I noticed when trying the following from this answer:

netstat -ln | grep mysql

It didn't return anything, and I took that to mean that there wasn't 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 was based on this post.

Then stop/start mysql again and retried mysql_secure_installation which finally let me enter my new root password and continue with other setup preferences.

Upvotes: 5

FianGaming

Reputation: 31

This works for me:

brew upgrade mysql

Upvotes: 2

wesamly

Reputation: 1584

For me, I had installed mariadb long time ago, then installed [email protected].

When I executed mysql -uroot, I get the error: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Reading the answers:

I uninstalled mariadb
Deleted the folder /usr/local/var/mysql
Ran the command mysqld --initialize

Then I was able to mysql -uroot -p

Upvotes: -1

Richard Rodriguez

Reputation: 31

I had same problem. After trying all these methods without success I did the following:

tail -f the-mysql-or-maria-db-error-file.err

in another console:

brew services restart mariadb

I saw the following error:

"MAC HOMEBREW Crash recovery failed. Either correct the problem (if it's, for example, out of memory error) and restart, or delete tc log and start mysqld with"

So I changed the tc.log extesion to tc.log.txt and restart mariadb

brew services restart mariadb

and done!

Upvotes: 0

JBaczuk

Reputation: 14619

In my case, the culprit was found in the logfiles:

$ 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 (I had to do the same with ib_logfile1 afterwards).

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

Upvotes: 5

timo

Reputation: 21

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

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

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

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

Upvotes: 2

krish

Reputation: 1

I manually started mysql in the system preferences pane by initialising the database and then starting it. This solved my problem.

Upvotes: 0

Tyler Curtis Jowers

Reputation: 457

The file /tmp/mysql.sock is probably a Named-Pipe, since it's in a temporary folder. A named pipe is a Special-File that never gets permanently stored.

If we make two programs, and we want one program to send a message to another program, we could create a text file. We have one program write something in the text file and the other program read what our other program wrote. That's what a pipe is, except it doesn't write the file to our computer hard disk, IE doesn't permanently store the file (like we do when we create a file and save it.)

A Socket is the exact same as a Pipe. The difference is that Sockets are usually used over a network -- between computers. A Socket sends information to another computer, or receives information from another computer. Both Pipes and Sockets use a temporary file to share so that they can 'communicate'.

It's difficult to discern which one MySql is using in this case. Doesn't matter though.

The command mysql.server start should get the 'server' (program) running its infinite loop that will create that special-file and wait for changes (listen for writes).

After that, a common issue might be that the MySql program doesn't have permission to create a file on your machine, so you might have to give it root privileges

sudo mysql.server start

Upvotes: 8

GnrlBzik

Reputation: 3358

After installing macos mojave, had to wipe mysql folder under /usr/local/var/mysql and then reinstall via brew install mysql otherwise permission related things would come up all over the place.

Upvotes: 5

dingalapadum

Reputation: 2175

Since I spent quite some time trying to solve this and always came back to this page when looking for this error, I'll leave my solution here hoping that somebody saves the time I've lost. Although in my case I am using mariadb rather than MySql, you might still be able to adapt this solution to your needs.

My problem

is the same, but my setup is a bit different (mariadb instead of mysql):

Installed mariadb with homebrew

$ brew install mariadb

Started the daemon

$ brew services start mariadb

Tried to connect and got the above mentioned error

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

My solution

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 is running (almost as described here):

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

(you might want to grep mysql instead of mariadb)

Add the socket file you found to ~/.my.cnf (create the file if necessary)(assuming ~/.my.cnf was listed when running the mysql --verbose ...-command from above):

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

Restart your mariadb:

$ brew services restart mariadb

After this I could run mysql and got:

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

So I run the command with superuser privileges instead and after entering my password I got:

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

Notes:

I'm not quite sure about the groups where you have to add the socket, first I had it [client-server] but then I figured [client] should be enough. So I changed it and it still works.
When running 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)
I wonder where I can configure the /usr/local/mariadb/data/mariadb.sock to actually be /tmp/mysql.sockso I can use the default settings instead of having to edit my .my.cnf (but I'm too tired now to figure that out...)
At some point I also did things mentioned in other answers before coming up with this.

Upvotes: 10

alexdd55

Reputation: 1117

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

the path here is

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

Upvotes: 1

Wayne

Reputation: 3519

Just to add to these answers, In my case I had no local mySQL server, it was running inside a docker container. So the socket file does not exist and will not be accessible for the "mysql" client.

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

By specifying a host name/ip the sock file is not required e.g.

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

Upvotes: 3

Hany Sakr

Reputation: 2929

I faced 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 don't forget to kill or uninstall the old version before continuing.

Commands:

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

Upvotes: 3

Kelley Robinson

Reputation: 778

You'll need to run mysql_install_db - easiest way is if you're in the install directory:

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

Alternatively, you can feed mysql_install_db a basedir parameter like the following:

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

Upvotes: 2

gouravtiwari21

Reputation: 379

I got the same error and this is what helped me:

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

Upvotes: 3

AAGD

Reputation: 1414

When you got the server running via

mysql.server start

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