The MySQL version that will be installed through Homebrew for El Capitan is currently version 5.7.9. This is an upgrade from the 5.6.x versions that brew would install on previous OS X versions.

This new version of MySQL has some security features and syntax changes that may be confusing. Personally after installing through Homebrew I did not know the default password and wanted to set up the server to accept the root user with no password, for my development environment. Read on to see how I achieved this.

Getting a root user with no password

Run the following highlighted steps in the Terminal.

  • Lines prefixed with "$" are a terminal command.
  • Lines prefixed with "mysql >" are entered in the MySQL command line client.

Stop the server

$ mysql.server stop

Also unload the launchctl file, if you have one, to prevent the server restarting:

$ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

Starting Safe Server

Now that MySQL has stopped running, you can start the mysqld_safe process. We use the --skip-grant-tables option to prevent permission errors when trying to update the password later.

$ mysqld_safe --skip-grant-tables &

Reseting the password: Part 1

Next we will enter the MySQL command line and ask the server to change the remove the password for the "root" user.

$ mysql -u root

mysql> FLUSH PRIVILEGES;
mysql> use mysql;
mysql> UPDATE user SET authentication_string=PASSWORD('') WHERE User = 'root';
mysql> exit

You may notice that in MySQL 5.7.6+ the password field has been changed to authentication_string.

Reseting the password: Part 2

Now that the login password has been set, we can kill the safe server:

$ killall mysqld

Next restart the "real" MySQL server. This will take a few moments.

$ mysql.server start

Now we can log in to MySQL successfully as the root user with no password. Login with the following command (when prompted for the password, don't type anything, just hit enter):

$ mysql -u root -p
Enter password:

Now the process gets confusing. You are logged in as root successfully but try to run use mysql;, you see the following error:

mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Now you must set the password again using the new MySQL 5.7.6 method (ALTER USER):

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '';

Done!

Finally we are done. You can exit the MySQL CLI using the exit command and are back to the terminal. You can try to log in again and access your tables and not experience any errors.

If you wish you can now reload the launchctl plist for MySQL so that the server starts automatically when your computer does.

$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist