Set up MySQL database server

MySQL is an open-source relational database management system.

1 Install

1.1 If using macOS with MacPorts

Execute on the command line:

sudo port install mysql8-server

Select mysql8 as your preferred version of MySQL with:

sudo port select mysql mysql8

1.2 If using macOS with Homebrew

Execute on the command line:

brew install mysql

2 Start

Since MySQL is an essential part of our web apps, so we want it always running and started automatically after a reboot.

2.1 If using macOS with MacPorts

First initialize the server by executing on the command line:

sudo /opt/local/lib/mysql8/bin/mysqld --initialize --user=_mysql

Take note of the root password generated by the command as you will need it in the next step.

Now you can start the server with:

sudo port load mysql8-server

This will also start the server automatically after a reboot.

To stop the server and prevent it from running after a reboot, execute:

sudo port unload mysql8-server

2.2 If using macOS with Homebrew

Execute on the command line:

brew services start mysql

This will also start the server automatically after a reboot.

To stop the server and prevent it from running after a reboot, execute:

brew services stop mysql

3 Secure

3.1 Run MySQL secure installation

Improve the security of the installation by executing the following on the command line:

mysql_secure_installation

Follow the instructions to configure root as password for the root user. If MySQL was installed using MacPorts, enter the password generated at the initialization.

Additionally, follow the instructions to:

skip setting up VALIDATE PASSWORD component
remove anonymous users and test databases
disallow the remote login for root

That will be sufficient for local development needs.

4 Configure

4.1 If using macOS with MacPorts

Edit file /opt/local/etc/mysql8/my.cnf, comment out the line including the default MacPorts settings and add the following configuration:

[mysqld]
basedir="/opt/local"
bind-address=127.0.0.1
binlog_expire_logs_seconds=86400
socket=/opt/local/var/run/mysql/mysqld.sock
collation-server=utf8mb4_unicode_520_ci
character-set-server=utf8mb4
init-connect='SET NAMES utf8mb4'
mysql_native_password=ON

[client]
socket=/opt/local/var/run/mysql/mysqld.sock
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

Configuring path to the socket file explicitly will be useful if you need to use multiple versions of MySQL, as you will not need to update PHP configuration when changing between them. This will also require creating the directory where the socket file will be placed and setting up the correct permissions on it:

sudo mkdir /opt/local/var/run/mysql
sudo chown _mysql:_mysql /opt/local/var/run/mysql

Now reload the server with:

sudo port reload mysql8-server

4.2 If using macOS with Homebrew

Edit file /usr/local/etc/my.cnf and add the following line in the [mysqld] section:

binlog_expire_logs_seconds=86400

Now reload the server by executing on the command line:

sudo port reload mysql8-server

5 Create admin user

To avoid MySQL upgrade borking the database access by resetting the password authentication method, we will create a new user admin with password admin which will be used to access the server.

First, log into the server by executing the following on the command line:

mysql -uroot -p

Enter the password root when asked. If you set up everything correctly, you should arrive at the MySQL command-line client. Execute on the mysql> command line:

CREATE USER 'admin'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
FLUSH PRIVILEGES;

Now you can exit the MySQL command-line client by typing exit.

6 Test

Test that you can use your newly created admin user to access the command line by executing:

mysql -uadmin -p

Enter the password admin when asked. You should again arrive at the MySQL command-line client:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49

Copyright (c) 2000, 2020, 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>

7 Install a GUI client

You will probably also want a graphical UI client to work with the database server. For macOS, TablePlus is a good choice, offering unlimited free trial with reasonable limitations for light use.

Install your preferred GUI client and configure the connection to the server with the admin user. If the connection works, you’ve finished installing and configuring your MySQL server.