Install MySQL 5.7 on macOS using Homebrew

MySQL_5-7_macOS.md

This is a fork of original gist https://gist.github.com/nrollr/3f57fc15ded7dddddcc4e82fe137b58e, with slight changes on pointing to 5.7 version branch, instead of 8 (latest default of MySQL in Hombrew).

Install MySQL 5.7 on macOS

This procedure explains how to install MySQL using Homebrew on macOS (Sierra 10.12 and up)

Install Homebrew

• Installing Homebrew is effortless, open Terminal and enter :
  $ /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
• Note: Homebrew will download and install Command Line Tools for Xcode 8.0 as part of the installation process.

Install MySQL

At this time of writing, Homebrew has MySQL version 8 as default, but as we're aiming to get 5.7, we'll need to append @5.7 to the default package key:

• Enter the following command : $ brew info mysql@5.7
• Expected output: mysql@5.7: stable 5.7.22 (bottled) [keg-only]

To install MySQL enter : $ brew install mysql@5.7

Additional configuration

Homebrew

• Install brew services first : $ brew tap homebrew/services

• Load and start the MySQL service : $ brew services start mysql@5.7.
  Expected output : Successfully started mysql (label: homebrew.mxcl.mysql)

• Check of the MySQL service has been loaded : $ brew services list ¹

• Force link 5.7 version - $ brew link mysql@5.7 --force

• Verify the installed MySQL instance : $ mysql -V.
  Expected output : Ver 14.14 Distrib 5.7.22, for osx10.13 (x86_64)

MySQL

Open Terminal and execute the following command to set the root password:
mysqladmin -u root password 'yourpassword'

Important : Use the single ‘quotes’ to surround the password and make sure to select a strong password!

Database Management

To manage your databases, I recommend using Sequel Pro, a MySQL management tool designed for macOS.
Current version available: 1.1.2

Comments

¹ The brew services start mysql@5.7 - instruction is equal to :

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

This and @greggles suggestion saved my day, thanks!

this is what I did to fix: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

• uninstalled MySQL
• when you run $ brew install mysql@5.7 make sure it installs with no Error/Warning
• I got error Warning: The post-install step did not complete successfully
  to fix the error I create /usr/local/var/mysql manually:
• $ mkdir /usr/local/var/mysql
• $ chmod 777 mkdir /usr/local/var/mysql
  and then
• $ brew postinstall mysql@5.7
  and then just follow up rest of instruction to start the MySQL service.

Worked for me. Thanks

If still someone facing the issue.

This fixed for me, Mac OS Catalina 10.15.6
https://xuri.me/2015/11/28/troubleshooting-homebrew-mysql-on-os-x.html

I did run

rm -rf /usr/local/var/mysql
brew postinstall mysql@5.7

got this error
0 [ERROR] unknown variable 'mysqlx-bind-address=127.0.0.1'

and removed the line with mysqlx-bind-address in /usr/local/etc/my.cnf and run

rm -rf /usr/local/var/mysql
brew postinstall mysql@5.7

in mac, you can run

locate my.cnf
# please remove all my.cnf found in above step
rm -rf /usr/local/var/mysql
mysqld --initialize-insecure
mysql -u root

it works for me

i keep running into a password error for root ,

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

i keep running into a password error for root ,

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

@michaelbrinkworth
Try running mysql -uroot -p.
You will receive a prompt for password.

Thanks!

had the same issue which @greggles solved. This medium article was simple enough to solve it for me without going to different links.

Thank you, this helped me fix the issue ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

thanks man it works perfectly

No idea why missed add the mysql path. I added by self.

$ mysql -V
-bash: mysql: command not found
$ echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.bash_profile
$ source ~/.bash_profile
$ mysql -V
mysql  Ver 14.14 Distrib 5.7.26, for osx10.14 (x86_64) using  EditLine wrapper

Thank you! ❤️

Great! Thx

After following these instructions, the mysql command results in the following error:

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/Cellar/mysql@5.7/5.7.22/lib/plugin/caching_sha2_password.so, 2): image not found

Authentication protocols changed from 5.7 to 8 versions of MySQL. Please refer to the link, it may help.
https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server?rq=1

@michaelbrinkworth @akshaydk you can also follow the steps here to either remove the root password or auto log-in with it to avoid having to run mysql -u root -p each time: https://medium.com/@benmorel/remove-the-mysql-root-password-ba3fcbe29870 (I did the latter since it was simpler and more secure).

As mentioned by other users above, this gist is missing a few important steps:

1. Adding the mysql directory to PATH after installation in order to run mysql command: https://gist.github.com/operatino/392614486ce4421063b9dece4dfe6c21#gistcomment-2950061
2. Either removing the root password or making a my.cnf file to automatically log in: https://medium.com/@benmorel/remove-the-mysql-root-password-ba3fcbe29870

Since version 5.7, MySQL is secure-by-default:

• a random root password is generated upon installation; you need to read this password from the server log
• you have to change this password the first time you connect
• you cannot use a blank password because of the validate_password plugin

root

very thanks~
helpful~

if you have socker(2) error on mac do the following first:

run the command: ($ brew install mysql )
start mysql server: ( $ mysql.server start )
secure mysql server with the password: ( $ mysql_secure_installation )

it worked for me, hope it works for you too :)

Still seeing the "Can't connect to local MySQL server..."

Tried Greggle's link to fully remove a previous install of MySQL 8, but after a restart the problem persists

# brew services start mysql@5.7
==> Successfully started `mysql@5.7` (label: homebrew.mxcl.mysql@5.7)

# launchctl list | grep mysql
-	0	homebrew.mxcl.mysql@5.7

Brew services is reporting the service started, but Launchctl is not showing a PID.

Here is the log output

lee@kezzico:~# tail /usr/local/var/mysql/kezzico.local.err 
2018-10-03T21:57:10.986146Z 0 [ERROR] Can't start server: can't check PID filepath: No such file or directory
2018-10-03T21:57:11.6NZ mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Finally got the service to start by creating a folder for the PID, and setting the permission on the folder to global read/write/x.

# sudo mkdir /var/run/mysqld
# chmod 777 /var/run/mysqld

Not sure why this is necessary. There may be security implications to this. Hopefully someone has a better solution.

Also you can change the mysql folder permissions to the current user.

sudo chown -Rv "$USER":admin /usr/local/var/mysql

Works great until I attempt to create a password for root:

$ mysqladmin -u root password 'yourpassword'

I get the following error:

... Failed! Error: Column count of mysql.user is wrong. Expected 45, found 43. Created with MySQL 50615, now running 50732. Please use mysql_upgrade to fix this error.

Any advice would be much appreciated?

Works great until I attempt to create a password for root:

$ mysqladmin -u root password 'yourpassword'

I get the following error:

... Failed! Error: Column count of mysql.user is wrong. Expected 45, found 43. Created with MySQL 50615, now running 50732. Please use mysql_upgrade to fix this error.

Any advice would be much appreciated?

run this command, it will prompt a form about setting up root password

mysql_secure_installation

Works great until I attempt to create a password for root:
$ mysqladmin -u root password 'yourpassword'
I get the following error:
... Failed! Error: Column count of mysql.user is wrong. Expected 45, found 43. Created with MySQL 50615, now running 50732. Please use mysql_upgrade to fix this error.
Any advice would be much appreciated?

run this command, it will prompt a form about setting up root password

mysql_secure_installation

Did the trick, thanks!

Thanks a lot!!!

I followed the instructions and did a clean install.
I got to the same "mysqladmin" point, but failed differently...

% mysqladmin -u root password '---redacted---'
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

To get past this, here's what I ended up having to do:

• Enter mysql and add a password for “root” user:

  • mysql -u root -p
  • It will prompt for password. THERE IS NO PASSWORD YET, SO JUST HIT ENTER
  • you should get the mysql> prompt

• At the mysql> prompt:

  • mysql> use mysql;
  • mysql> update user set authentication_string=password("<desired root password>") where user="root";
  • mysql> exit

• Restart the service to pick up the change:

  • brew services stop mysql@5.7
  • brew services start mysql@5.7

Now, when you do mysql -u root -p it will prompt for password and accept the new password you set above.

I had to do below steps after following the given instructions:-
sudo mkdir /usr/local/var/mysql
sudo chmod 777 /usr/local/var/mysql
brew services restart mysql@5.7

When setting up my Apple M1 Macbook Pro I was experiencing the issue of the following error message being thrown:

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

My fix was to modify the MySQL config file:

nano /usr/local/etc/my.cnf

Append the following lines:

tmpdir=/tmp
user=root

Then run:

brew services restart mysql@5.7

This fixed my issue, and I was then able to connect successfully with my DB client.

Thank you, It worked on macOS Monterey.

Not working for me on mac os m2 ventura.

I did start mysql@5.7 and gives me Successfully started mysql@5.7 (label: homebrew.mxcl.mysql@5.7) message. But, when I tried to view the list of services to check if it's really started, I got this status below which is stopped.

much good!