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
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'
[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.