Friday, 16 December 2016

Mac OSX setup MySQL server and MySQLWorkBench

OS X: 10.11.6
MySQL: V5.7.17
MySQLWorkBench: V6.2.3

The installation of MySQL is simple, just follow this guidance,

At the end of installation, it will pop up the below window to tell you the temporary password. Take note of this password. It will be used later.

This password can be used only one time. This is the difficulty, we need to take this chance to reset the password.

Supposing when you install MySQL server, you also installed the Preference Pane and Launched Support. After you installation, MySQL server should start automatically. Use OSX "Activity Monitor" to check if there is a process "mysqld". If it's runing, means the server is started. Otherwise, start the MySQL server manually.

After the server is running, we can connect to MySQL server now. Go to the installation path of MySQL, default location is at "/usr/local/mysql/bin". Use the following command to connect the server:
mysql -u root -p
After press return button, it will ask the password, key in the password at the end of installation.
Don't try any command like "sudo mysqld_safe --skip-grant-tables" that suggested online, it will start another MySQL server. You will lose connection of current one.

If the connection is succeeded, you should see the welcome message.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 224
Server version: 5.5.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


Now you can reset the password by this command.

SET PASSWORD = PASSWORD('your_new_password');

If it works, you will see.

Query OK, 0 rows affected, 1 warning (0.00 sec)

Otherwise, you will see.

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

Don't close the current Terminal window, otherwise you will lose connect with current server, and you will lose the only one chance to reset the password.

If you cannot make it, you may need to uninstall the MySQL server by following commands and install again.
  • sudo rm /usr/local/mysql
  • sudo rm -rf /usr/local/mysql*
  • sudo rm -rf /Library/StartupItems/MySQLCOM
  • sudo rm -rf /Library/PreferencePanes/My*
  • edit /etc/hostconfig and remove the line MYSQLCOM=-YES-
  • rm -rf ~/Library/PreferencePanes/My*
  • sudo rm -rf /Library/Receipts/mysql*
  • sudo rm -rf /Library/Receipts/MySQL*
  • sudo rm -rf /private/var/db/receipts/*mysql*
After resetting the password, you can start to install MySQLWorkBench, and connect the MySQL server.