Configure MySQL on macOS

Database image.

Overview

This is a followup post to my earlier Configure Apache and PHP-FPM on macOS post. With the addition of MySQL to my Mac website development environment, I can now install Content Management Systems, such as WordPress that require a database system to function. My original post supports static website development (for example, Grav). My current website host, Pair Networks, uses MySQL versions 5.6 and 5.7 (seems to default to 5.6). I am creating a similar environment on my Mac. My Altoplace website uses MySQL 5.7, so this post will describe MySQL 5.7. This version is supported by WordPress.

We will again use Homebrew to install MySQL version 5.7. It is very easy to install a working database system with Homebrew. I will describe some tweaks that I made to my MySQL environment. I created a shell script that I use to start, stop, restart, and get process information about my running MySQL service. Homebrew provides a command (brew services ...) for starting and stopping a service, but I like having additional error checking and control that I can do in a shell script.

Installing MySQL 5.7 with the brew command

The following shows how to install MySQL 5.7 and how to start up the MySQL service, using the brew services command.

 1$ brew install mysql@5.7
 2
 3$ brew link mysql@5.7 --force --overwrite
 4
 5$ which mysql
 6/usr/local/bin/mysql
 7
 8$ brew services list
 9Name      Status  User   Plist
10httpd     started root   /Library/LaunchDaemons/homebrew.mxcl.httpd.plist
11mysql@5.7 stopped
12php       stopped
13php@7.4   started george /Users/george/Library/LaunchAgents/homebrew.mxcl.php@7.4.plist
14
15$ brew services start mysql@5.7
16==> Successfully started `mysql@5.7` (label: homebrew.mxcl.mysql@5.6)
17
18$ mysql -u root
19Welcome to the MySQL monitor.  Commands end with ; or g.
20Your MySQL connection id is 2
21Server version: 5.7.34 Homebrew
22
23Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.
24
25Oracle is a registered trademark of Oracle Corporation and/or its
26affiliates. Other names may be trademarks of their respective
27owners.
28
29Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
30
31mysql> q
32Bye
33$

The default Homebrew MySQL installation does not require a root password. While this may be fine for a local development environment, I recommend running mysql_secure_installation to create a root password and to secure your MySQL installation. After creating your root password, answer Y to all the remaining questions. After doing so, you will have to use your root password to connect to MySQL:

1$ mysql -u root
2ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
3
4$ mysql -u root -p
5Enter password:
6Welcome to the MySQL monitor.  Commands end with ; or g.
7Your MySQL connection id is 14
8Server version: 5.6.47 Homebrew
9 o o o

Using a Shell Script to Control the MySQL Service

I mention that I wrote a shell script, mydbctl, to control and monitor my MySQL service. The usage looks like:

 1$ mydbctl
 2Usage: mydbctl start|stop|restart|status
 3
 4$ mydbctl start
 5mysql started OK ...
 6
 7$ mydbctl status
 8USER               PID STARTED     ELAPSED      TIME NI      VSZ    RSS COMMAND
 9george           36070  4:16PM       00:08   0:00.02  0  4282236   1220 /bin/sh /usr/local/opt/mysql@5.7/bin/mysqld_safe --datadir=/usr/local/var/mysql
10george           36158  4:16PM       00:08   0:00.26  0  5118812 177464 /usr/local/opt/mysql@5.7/bin/mysqld --basedir=/usr/local/opt/mysql@5.7 --datadir=/usr/local/var/mysql --plugin-dir=/usr/local/opt/mysql@5.7/lib/plugin --log-error=imac1.err --pid-file=imac1.pid
11
12$ mydbctl restart
13waiting for mysql to stop 0 ...
14waiting for mysql to stop 1 ...
15mysql stopped OK ...
16mysql started OK ...
17
18$ mydbctl stop   
19waiting for mysql to stop 0 ...
20waiting for mysql to stop 1 ...
21mysql stopped OK ...

While this shell script supports the same actions as the brew services command, it actually verifies that the service stopped and/or started successfully. Here is my mydbctl shell script:

  1#!/bin/zsh
  2
  3# Usage: mydbctl start|stop|restart|status
  4
  5error_exit()
  6{
  7  echo -e "$1" 1>&2
  8  exit 1
  9}
 10
 11# Usage
 12if [[ ! $# -eq 1 || ! ($1 == "start" || $1 == "stop" || $1 == "restart" || $1 == "status") ]]
 13then
 14  error_exit "Usage: mydbctl start|stop|restart|status"
 15else
 16  action="$1"
 17fi
 18
 19pwait()
 20{
 21  process=$1
 22  action=$2
 23  count=0
 24
 25  if [[ $action == "stop" ]]
 26  then
 27    until ! pgrep -q $process || [[ $count -gt 5 ]]
 28    do
 29      echo "waiting for $process to stop $count ..."
 30      sleep 1
 31      ((count++))
 32    done
 33    if ! pgrep -q $process; then
 34      echo "$process stopped OK ..."
 35    else
 36      error_exit "$process failed to stop"
 37    fi
 38  else
 39    # action is start
 40    sleep 1 # give process time to die for configuration file errors ...
 41    until pgrep -q $process || [[ $count -gt 5 ]]
 42    do
 43      echo "waiting for $process to start $count ..."
 44      sleep 1
 45      ((count++))
 46    done
 47    if pgrep -q $process; then
 48      echo "$process started OK ..."
 49    else
 50      error_exit "$process failed to start"
 51    fi
 52  fi
 53}
 54
 55mydb_start()
 56{
 57  if pgrep -q mysqld; then
 58    echo "mysql is already started ..."
 59    exit 0
 60  fi
 61
 62  if [ ! -f "/usr/local/opt/mysql@5.7/homebrew.mxcl.mysql@5.7.plist" ]; then
 63    error_exit "Cannot start mysql -- homebrew.mxcl.mysql@5.7.plist is missing ..."
 64  fi
 65
 66  cp /usr/local/opt/mysql@5.7/homebrew.mxcl.mysql@5.7.plist ~/Library/LaunchAgents/
 67  if [ "$?" != "0" ]; then
 68    error_exit "homebrew.mxcl.mysql@5.7.plist copy failed ..."
 69  fi
 70
 71  launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql@5.7.plist > /dev/null 2>&1
 72  pwait mysql start
 73}
 74
 75mydb_stop()
 76{
 77  if ! pgrep -q mysqld; then
 78    echo "mysql is already stopped ..."
 79    exit 0
 80  fi
 81
 82  launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mysql@5.7.plist > /dev/null 2>&1
 83  pwait mysql stop
 84
 85  # Don't start automatically
 86  rm -f ~/Library/LaunchAgents/homebrew.mxcl.mysql@5.7.plist
 87}
 88
 89mydb_restart()
 90{
 91  # First, stop mysql ...
 92  if ! pgrep -q mysqld; then
 93    echo "mysql is already stopped, so just start it up ..."
 94    mydb_start
 95    exit 0
 96  fi
 97
 98  launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mysql@5.7.plist > /dev/null 2>&1
 99  pwait mysql stop
100
101  # Then, start mysql ...
102  launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql@5.7.plist > /dev/null 2>&1
103  pwait mysql start
104}
105
106mydb_status()
107{
108  # Get info about running mysql processes
109  ps -axo user,pid,start,etime,time,nice,vsz,rss,command |
110    egrep 'PID|mysql' | sed '/grep/d'
111}
112
113case $action in
114  "start")
115    mydb_start
116    ;;
117  "stop")
118    mydb_stop
119    ;;
120  "restart")
121    mydb_restart
122    ;;
123  "status")
124    mydb_status
125    ;;
126  *)
127    # Should never happen ...
128    error_exit "Invalid action ..."
129    ;;
130esac
131
132exit 0

There is additional error checking, including actually waiting for the MySQL service to start or stop. This shell script is completely compatible with the brew services actions, for example:

 1$ brew services list
 2Name      Status  User   Plist
 3dnsmasq   unknown root   /Library/LaunchDaemons/homebrew.mxcl.dnsmasq.plist
 4httpd     unknown root   /Library/LaunchDaemons/homebrew.mxcl.httpd.plist
 5mysql@5.7 started george /Users/george/Library/LaunchAgents/homebrew.mxcl.mysql@5.7.plist
 6php       stopped        
 7php@7.4   started george /Users/george/Library/LaunchAgents/homebrew.mxcl.php@7.4.plist
 8
 9$ brew services stop mysql@5.7
10Stopping `mysql@5.7`... (might take a while)
11==> Successfully stopped `mysql@5.7` (label: homebrew.mxcl.mysql@5.7)
12
13$ brew services list          
14Name      Status  User   Plist
15dnsmasq   unknown root   /Library/LaunchDaemons/homebrew.mxcl.dnsmasq.plist
16httpd     unknown root   /Library/LaunchDaemons/homebrew.mxcl.httpd.plist
17mysql@5.7 stopped        
18php       stopped        
19php@7.4   started george /Users/george/Library/LaunchAgents/homebrew.mxcl.php@7.4.plist
20
21$ mydbctl status
22USER               PID STARTED     ELAPSED      TIME NI      VSZ    RSS COMMAND

You can try both and decide what works for you. I have seen instances where the brew services restart action said that it was successful, but the MySQL service did not actually start up. The mydbctl script verifies that the service process is running (or not) before reporting success. (httpd & dnsmasq show an unknown status because they were started using sudo.)

MySQL Configuration Tweaks

This section is totally optional, but I wanted to make some very basic optimizations before I started creating databases. The MySQL installation creates a my.cnf configuration file at /usr/local/etc. The default content is:

1# Default Homebrew MySQL server config
2[mysqld]
3# Only allow connections from localhost
4bind-address = 127.0.0.1

I added the following lines after spending some time researching some basic MySQL optimizations. You can do your own research and/or try out these settings. So far, they have been working well for me. However, I have not stressed out my database installations yet. I added the following lines:

1# My additions
2max_allowed_packet = 64M
3innodb_buffer_pool_size = 1024M
4innodb_log_file_size = 128M
5innodb_buffer_pool_instances = 1
6
7[mysqldump]
8max_allowed_packet = 64M

Of course, be sure to restart the MySQL service after making any changes to this file. I made several very basic tweaks. The default max allowed packet is very low (4M); I picked a typical value that I have often seen used in various posts. I increased the total in RAM MySQL cache size from 128M to 1024M. I understand that the log file size (times 2) should be 25% of the cache size. Learning how to use MySQL is a big learning curve for me; as my knowledge grows, I will update this post.

Encrypt MySQL user/password credentials

There are times when you may want to execute mysql in a shell script. For example, I have written a shell script called mydbdrop to delete all the tables in a database. You might want to do this, for example, before reinstalling a fresh copy of WordPress. You do not want to hardcode or use your database password in a shell script. You also do not want to be prompted for the password, especially if mysql is being executed in a loop to drop each database table. The solution is to use the mysql_config_editor utility to store authentication credentials in an obfuscated login path file named ~/.mylogin.cnf. You execute mysql_config_editor once with the set action to create an alias (called a login-path) for your user/password/host. Here is an example:

 1
 2$ mysql_config_editor set --login-path=ap509adm --host=localhost --user=root --password    
 3Enter password: 
 4
 5$ ls -l ~/.mylogin.cnf
 6-rw------- 1 george  staff  136 May 14 16:40 /Users/george/.mylogin.cnf
 7
 8$ mysql_config_editor print --all                                                      
 9[ap509adm]
10user = root
11password = *****
12host = localhost

The ap509adm alias is called a login-path. I can now use my login-path to connect to mysql without having to specify my user/password/host information. My user credentials are stored in a binary file that's only readable or writeable by the user. Here is an example of how to use it:

 1$ mysql --login-path=ap509adm
 2Welcome to the MySQL monitor.  Commands end with ; or g.
 3Your MySQL connection id is 3
 4Server version: 5.7.34 Homebrew
 5
 6Copyright (c) 2000, 2021, Oracle and/or its affiliates.
 7
 8Oracle is a registered trademark of Oracle Corporation and/or its
 9affiliates. Other names may be trademarks of their respective
10owners.
11
12Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
13
14mysql> q
15Bye

You can add the credentials for other databases to the same ~/.mylogin.cnf file. Just specify a unique login-path or alias for it. Here is an example of how I use mysql in a shell script, mydbdrop, to drop all the tables in a database:

 1$ mydbdrop
 2Usage: mydbdrop login-path DB
 3 Drop all tables from an MySQL database.
 4
 5$ mydbdrop ap509adm wp509db
 6Deleting wp_commentmeta table from wp509db database...
 7Deleting wp_comments table from wp509db database...
 8Deleting wp_links table from wp509db database...
 9Deleting wp_options table from wp509db database...
10Deleting wp_postmeta table from wp509db database...
11Deleting wp_posts table from wp509db database...
12Deleting wp_term_relationships table from wp509db database...
13Deleting wp_term_taxonomy table from wp509db database...
14Deleting wp_termmeta table from wp509db database...
15Deleting wp_terms table from wp509db database...
16Deleting wp_usermeta table from wp509db database...
17Deleting wp_users table from wp509db database...

I don't have to recreate my database. I can delete all the data by dropping all the tables. This can be a dangerous script to use. Be sure you know what your are doing and always have database backups just in case. After dropping all the tables, I am ready to do, for example, a fresh install of WordPress. If this sounds useful to you, here is my mydbdrop script:

 1#!/bin/zsh
 2
 3# mydbdrop login-path DB
 4mylp="$1"
 5mydb="$2"
 6
 7# Usage
 8if [ ! $# -eq 2 ]
 9then
10  echo "Usage: mydbdrop login-path DB"
11  echo " Drop all tables from an MySQL database."
12  exit 0
13fi
14
15tables=$(mysql --login-path=$mylp $mydb -sN -e 'show tables')
16if [ $? -ne 0 ]
17then
18  echo "Error - Cannot connect to mysql server usingn given login-path or database does not exits!"
19  exit 1
20fi
21
22# make sure one or more tables exists
23if [ -z ${tables} ]
24then
25  echo "Warning - No table found in $mydb database!"
26  exit 0
27fi
28
29# Delete all the tables in the DB
30for t in ${=tables}
31do
32  echo "Deleting $t table from $mydb database..."
33  mysql --login-path=$mylp $mydb -e "drop table $t" > /dev/null 2>&1
34  if [ $? -ne 0 ]
35  then
36   echo "Error - Could not delete table $t!"
37   exit 1
38  fi
39done

Final Thoughts

At this point, you should have a working MySQL environment and are ready to start creating databases, for example a WordPress database, on your local Mac machine. I haven't tried MySQL version 8.0 yet. But the installation process should be similar. Homebrew defaults to version 8.0 (when doing brew install mysql). I am going to continue getting my feet wet with version 5.7 for now, since I am still in the learning mode and that is the version being used by this website, Altoplace.

Now that I have a complete MAPM stack (macOS, Apache, PHP, and MySQL) environment, I will soon write a post about how to install and use WordPress on a Mac machine.