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

Question

I tried to solve this error by Googling but was not able to resolve it.

I am on Mac OS Catalina 10.15.4.

I installed MariaDB using HomeBrew but is not able to use the mysql command.

Below is the commands I ran and my output:

$ brew install mariadb
==> Downloading https://homebrew.bintray.com/bottles/mariadb-10.4.12.catalina.bottle.tar.gz
Already downloaded: /Users/a1646259/Library/Caches/Homebrew/downloads/3072e5af25f87508635402d401c601f32002e8ab956a37a9fd50f5656a2ffb80--mariadb-10.4.12.catalina.bottle.tar.gz
==> Pouring mariadb-10.4.12.catalina.bottle.tar.gz
==> /usr/local/Cellar/mariadb/10.4.12/bin/mysql_install_db --verbose --user=a1646259 --basedir=/usr/local/Cellar/mariadb/10.4.12 --datadir=/usr/local/var/mys
Last 15 lines from /Users/a1646259/Library/Logs/Homebrew/mariadb/post_install.01.mysql_install_db:

    shell> /usr/local/Cellar/mariadb/10.4.12/bin/mysql -u root mysql
    mysql> show tables;

Try 'mysqld --help' if you have problems with paths.  Using
--general-log gives you a log in /usr/local/var/mysql that may be helpful.

The latest information about mysql_install_db is available at
https://mariadb.com/kb/en/installing-system-tables-mysql_install_db
You can find the latest source at https://downloads.mariadb.org and
the maria-discuss email list at https://launchpad.net/~maria-discuss

Please check all of the above before submitting a bug report
at http://mariadb.org/jira

Warning: The post-install step did not complete successfully
You can try again using `brew postinstall mariadb`
==> Caveats
A "/etc/my.cnf" from another install may interfere with a Homebrew-built
server starting up correctly.

MySQL is configured to only allow connections from localhost by default

To have launchd start mariadb now and restart at login:
  brew services start mariadb
Or, if you don't want/need a background service you can just run:
  mysql.server start
==> Summary
🍺  /usr/local/Cellar/mariadb/10.4.12: 743 files, 168.2MB

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

$   mysql.server start
Starting MariaDB
200428 16:18:14 mysqld_safe Logging to '/usr/local/var/mysql/YU-CHIEHs-MacBook-Pro.local.err'.
200428 16:18:14 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
.../usr/local/bin/mysql.server: line 264: kill: (34819) - No such process
 ERROR!

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

$   brew services start mariadb
Service `mariadb` already started, use `brew services restart mariadb` to restart.

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

$ brew services restart mariadb
Stopping `mariadb`... (might take a while)
==> Successfully stopped `mariadb` (label: homebrew.mxcl.mariadb)
==> Successfully started `mariadb` (label: homebrew.mxcl.mariadb)

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

$ mysqld
2020-04-28 16:18:51 0 [Note] mysqld (mysqld 10.4.12-MariaDB) starting as process 35672 ...
2020-04-28 16:18:51 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2020-04-28 16:18:51 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-04-28 16:18:51 0 [Note] InnoDB: Uses event mutexes
2020-04-28 16:18:51 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-04-28 16:18:51 0 [Note] InnoDB: Number of pools: 1
2020-04-28 16:18:51 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-04-28 16:18:51 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-04-28 16:18:51 0 [Note] InnoDB: Completed initialization of buffer pool
2020-04-28 16:18:51 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-04-28 16:18:51 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-04-28 16:18:51 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-04-28 16:18:51 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-04-28 16:18:51 0 [Note] InnoDB: Waiting for purge to start
2020-04-28 16:18:51 0 [Note] InnoDB: 10.4.12 started; log sequence number 47136; transaction id 9
2020-04-28 16:18:51 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/var/mysql/ib_buffer_pool
2020-04-28 16:18:51 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-04-28 16:18:51 0 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
2020-04-28 16:18:51 0 [ERROR] mysqld: unknown variable 'mysqlx-bind-address=127.0.0.1'
2020-04-28 16:18:51 0 [ERROR] Aborting
2020-04-28 16:18:51 0 [Note] InnoDB: Buffer pool(s) load completed at 200428 16:18:51

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

$ brew services restart mariadb
Stopping `mariadb`... (might take a while)
==> Successfully stopped `mariadb` (label: homebrew.mxcl.mariadb)
==> Successfully started `mariadb` (label: homebrew.mxcl.mariadb)

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

$ ls /etc/my.cnf
ls: /etc/my.cnf: No such file or directory

$

Why can't I use mariadb and how do I fix this?

Have I missed anything?

Please let me know if I should provide more info.

Thanks!

Answer 1

I think this issue happens because I installed mysql through brew before and removed it:

brew install mysql
brew uninstall mysql

Installing mysql using brew creates this /usr/local/etc/my.cnf file:

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1

To install MariaDB and use it:

rm /usr/local/etc/my.cnf
brew uninstall mariadb
ps -ax | grep mysql
# and kill running mysql processes found above
brew install mariadb
mysql # should work now

Now, if you check /usr/local/etc/my.cnf again, you see:

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /usr/local/etc/my.cnf.d

which seems to be the correct one that MariaDB should be using.

After some investigation, it seems like this line is causing all these problem:

mysqlx-bind-address = 127.0.0.1

And according to MySQL's documentation about X Plugin, it seems like it's trying to only allow access from localhost when X Plugin is enabled.

I don't know why it caused me the problem, maybe it's because my computer doesn't have X Plugin installed. It would be nice if someone knows and can explain.