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
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 '';
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