1. Install the latest available version

1.1. Check the latest available version

Before executing install command, let’s check what is the latest (on the moment of command execution) MySQL version available:

brew info mysql

You will get output in terminal like this:

mysql: stable 8.0.13 (bottled)
Open source relational database management system
https://dev.mysql.com/doc/refman/8.0/en/
Conflicts with:
  mariadb (because mysql, mariadb, and percona install the same binaries.)
  mariadb-connector-c (because both install plugins)
  mysql-cluster (because mysql, mariadb, and percona install the same binaries.)
  mysql-connector-c (because both install MySQL client libraries)
  percona-server (because mysql, mariadb, and percona install the same binaries.)
Not installed
From: https://github.com/Homebrew/homebrew-core/blob/master/Formula/mysql.rb
==> Dependencies
Build: cmake ✘
Required: openssl ✔
==> Requirements
Required: macOS >= 10.10 ✔
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start
==> Analytics
install: 65,040 (30 days), 215,860 (90 days), 888,496 (365 days)
install_on_request: 61,003 (30 days), 197,898 (90 days), 806,318 (365 days)
build_error: 0 (30 days)

Now you can see in line #1 that mysql has the latest version (April 2019) in brew stable 8.0.13.

1.2. Install formula (packet)

So if you are ok to install this version, just run in terminal:

brew install mysql

You will see the long output, but the last part will be like this:

==> mysql
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start

1.3. Run MySQL Community Server

To start MySQL Server just run:

brew services start mysql

1.4. Add aliases to .bash_profile

We can add aliases to our user’s .bash_profile to avoid typing long commands like brew services ...

Open .bash_profile:

nano ~/.bash_profile

Add next text to file and press `Ctrl + 0` to save changes:

alias mysql-start='brew services start mysql'
alias mysql-stop='brew services stop mysql'
alias mysql-restart='brew services restart mysql'

Reload user’s .bash_profile:

source ~/.bash_profile

Now you can use defined shortcuts: mysql-start, mysql-stop, mysql-restart to manage your MySQL Server.

1.5. Set default authentication plugin

MySQL 8.0 by default uses a specific authentication method called caching_sha2_password. We can check that using mysql client. Firstly we need to connect to installed MySQL Server:

mysql -uroot

Then we can execute SQL command to get details about root user:

SELECT * FROM mysql.user WHERE user='root';

Output:

+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host      | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| localhost | root | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | caching_sha2_password |                       | N                | 2019-04-06 09:18:16   |              NULL | N              | Y                | Y              |                   NULL |                NULL | NULL                     | NULL            |
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
1 row in set (0.00 sec)

If you scroll right a little bit, you can find column plugin with value caching_sha2_password.

Current authentication method may not be supported by database connection drives and you can get error connected to the unsupported authentication method. In such case we can fix it by setting the plugin to mysql_native_password.

Let’s connect to MySQL Server:

mysql -uroot

To change authentication method for exist user (root for example), we can execute SQL like:

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

Do not forget t change ‘password‘ to your custom one. By default root user has no password, so you can leave is empty like ”. But I really recommend creating a not empty one.

But what if we want to set mysql_native_password as default method. To tell truth this is quite easy to achieve.

We need to open MySQL Server config file my.cfg:

nano /usr/local/etc/my.cnf

and add there one key-value string:

default-authentication-plugin = mysql_native_password

So the result my.cnf file content will look like this:

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
default-authentication-plugin = mysql_native_password

Restart MySQL Server:

mysql-restart

Now when you will add a new user to a database it will have a mysql_native_password as an authentication method.

2. Install a specific version

2.1. Search for available versions

To install a specific version, for example 5.6 we should check what it’s formula (package) name in brew, to do that, just run this command in terminal:

brew search mysql

You will see something like this:

==> Formulae
automysqlbackup        mysql-client           mysql-connector-c++    mysql-utilities        mysql@5.7
mysql ✔                mysql-cluster          mysql-sandbox          mysql@5.5              mysqltuner
mysql++                mysql-connector-c      mysql-search-replace   mysql@5.6

==> Casks
homebrew/cask/mysql-connector-python   homebrew/cask/mysql-utilities          homebrew/cask/sqlpro-for-mysql
homebrew/cask/mysql-shell              homebrew/cask/navicat-for-mysql

In line #4 (scroll it horizontally if you don’t see it’s content) you can find a formula (package) with name mysql@5.6, so this is what we need.

Now we can install mysql version 5.6 with command:

brew install mysql@5.6

2.2. Add aliases to .bash_profile

In case with specific MySQL formula name (in our case mysql@5.6) we need to set aliases with that name. So in a result your aliases in .bash_profile will be like this:

alias mysql-start='brew services start mysql@5.6'
alias mysql-stop='brew services stop mysql@5.6'
alias mysql-restart='brew services restart mysql@5.6'

3. Create user and database

3.1. Create user

To create a database you need to use mysql client in a terminal. If you check output (clause 1.1, line 23-24):

To connect run:
    mysql -uroot

You can find out that mysql tells us how to connect to the server to execute SQL commands. So let’s connect to MySQL Server with user `root` using the previously mentioned command:

mysql -uroot

After command will be executed you will see the output like this:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.13 Homebrew

Copyright (c) 2000, 2019, 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
owners.

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

mysql>

Now we need to write some SQL to create a user. The command will look like this:

CREATE USER IF NOT EXISTS 'username'@'localhost' IDENTIFIED BY 'password';

Do not forget to change ‘username‘ and ‘password‘ with your custom ones. You can find more details about CREATE USER Syntax on official reference page.

3.2. Create database

To create database, execute:

CREATE DATABASE `dbname` CHARACTER SET utf8 COLLATE utf8_general_ci;

Do not forget to change ‘dbname‘ with your database name.

You can select different values for CHARACTER SET and COLLATE from the official reference page for CREATE DATABASE Syntax.

3.3. Grant privileges to a user

The user was created but it does not have any rights to access the database. In our case, we can grand user ALL privileges, so the user will be able to create, modify and delete database related objects like tables, indexes, primary keys etc.

GRANT ALL PRIVILEGES ON `dbname`.* TO 'username'@'localhost';

Do not forget to change ‘dbname‘ and ‘username‘ to your custom ones.

Done.

5. MySQL main file paths

• config file: /usr/local/etc/my.cnf;
• error log file: /usr/local/var/mysql/[hostname].err (host name is you macbook host name, to get it just execute in terminal hostname);
• bin folder: /usr/local/opt/mysql/bin;
• parent pid folder: /usr/local/var/mysql.

6. Official links

• MySQL 8.0 Reference Manual
• MySQL Community Server download page