Configure MySQL on macOS Catalina

Overview

This is a followup post to my earlier Configure Apache and PHP-FPM on macOS Catalina 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, so this post will describe my experience with MySQL version 5.6. This version is still supported by WordPress. Also, a major purpose for Altoplace is learning about website creation. I will start with MySQL version 5.6 to learn about database development. Later, I will move up to more current versions (including MariaDB).

We will again use Homebrew to install MySQL version 5.6. 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.6 with the brew command

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

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:

Using a Shell Script to Control the MySQL Service

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

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 m5ctl shell script:

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:

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 m5ctl script verifies that the service process is running (or not) before reporting success.

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 contents looks (as I recall) something like:

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:

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 m5drop 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:

The gwadmin 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:

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, m5drop, to drop all the tables in a 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 m5drop script:

Conclusions

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 versions 5.7 or 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.6 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.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.