[MySQL]: Install MySQL on Mac

Installation Process

Download MySQL

Download MySQL Community Server from MySQL download & install the package.
When installing, remember the password!

Start the server

Open System Preference --> MySQL --> Start MySQL Server

Enable mysql command in terminal

Open .bash_profile with the following

$ vim ~/.bash_profile
# OR
$ open ~/.bash_profile

Add the following in .bash_profile

export PATH=${PATH}:/usr/local/mysql/bin/

Then, reopen the terminal to use mysql command

In terminal, run:

$ mysql -u root -p

Then, type in the password you've set during installation.

TIP: Consider creating alias for command from this post

Try show databases

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

If it doesn't work, run the following

ALTER USER 'root'@localhost' IDENTIFIED BY 'NEWPASSWORD';

Change mysql_native_password for Node.js mysql connection

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'

root: user name
localhost: your URL
password: your password

Set sql_mode='';

SET @@global.sql_mode='';

Avoids some errors instead of showing warnings

Setting a new password

In MySQL Terminal, type in the following:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW_USER_PASSWORD';
mysql> FLUSH PRIVILEGES;

Change root to other user name when needed.
FLUSH PRIVILEGES tells the server to reload the grant tables.

If ALTER USER doesn't work, modify the user table with

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('NEW_USER_PASSWORD')
WHERE User = 'user-name' AND Host = 'localhost';
FLUSH PRIVILEGES;

Lost mysql password?

Use FLUSH PRIVILEGES when you're logged into MySQL terminal. Otherwise:

$ mysql -u root
mysql> USE mysql;
mysql> UPDATE user SET password=PASSWORD("NEW_USER_PASSWORD") WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> quit