Configure MySQL on macOS
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 8.x. I am creating a similar environment on my Mac. My Altoplace website uses MySQL 8.x, so this post will describe using MySQL 8.x.
This post also describes how to use a shell script to control the MySQL service, some additional MySQL configuration tweaks, and how to encrypt MySQL user/password credentials.
I will again use Homebrew to install MySQL version 8.x.
Installing MySQL 8.x with the brew command
The following shows how to install MySQL 8.x and how to start up the MySQL service, using the brew services command.
$ brew install mysql
$ which mysql
/opt/homebrew/bin/mysql
$ mysql -V
mysql Ver 8.3.0 for macos14.2 on arm64 (Homebrew)
$ brew services start mysql
==> Successfully started `mysql` (label: homebrew.mxcl.mysql)
$ brew services list
Name Status User File
mysql started george ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.3.0 Homebrew
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
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> \q
Bye
$ brew services stop mysql
Stopping `mysql`... (might take a while)
==> Successfully stopped `mysql` (label: homebrew.mxcl.mysql)
As of this writing, Homebrew installs MySQL version 8.3. The default Homebrew MySQL installation does not require a root password. While this may be fine for a local development environment, you can run 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.
$ brew services start mysql
==> Successfully started `mysql` (label: homebrew.mxcl.mysql)
$ mysql_secure_installation
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: N
Please set the password for root here.
New password:
Re-enter new password:
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Success.
All done!
After doing so, you will have to use your root password to connect to MySQL:
$ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.3.0 Homebrew
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:
$ mydbctl
Usage: mydbctl start|stop|restart|status
$ mydbctl start
mysql started OK ...
$ mydbctl status
USER PID STARTED ELAPSED TIME NI VSZ RSS COMMAND
george 6671 11:59AM 00:10 0:00.03 0 408515856 2384 /bin/sh /opt/homebrew/opt/mysql/bin/mysqld_safe --datadir=/opt/homebrew/var/mysql
george 6772 11:59AM 00:10 0:00.31 0 410339696 444432 /opt/homebrew/opt/mysql/bin/mysqld --basedir=/opt/homebrew/opt/mysql --datadir=/opt/homebrew/var/mysql --plugin-dir=/opt/homebrew/opt/mysql/lib/plugin --log-error=mac1.mtco.com.err --pid-file=mac1.mtco.com.pid
$ mydbctl restart
waiting for mysql to stop 0 ...
waiting for mysql to stop 1 ...
mysql stopped OK ...
mysql started OK ...
$ mydbctl stop
waiting for mysql to stop 0 ...
waiting for mysql to stop 1 ...
mysql 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. Create an executable file named mydbctl in your user bin directory and add the following content:
#!/bin/zsh
# Usage: mydbctl start|stop|restart|status
error_exit()
{
echo -e "$1" 1>&2
exit 1
}
# Usage
if [[ ! $# -eq 1 || ! ($1 == "start" || $1 == "stop" || $1 == "restart" || $1 == "status") ]]
then
error_exit "Usage: mydbctl start|stop|restart|status"
else
action="$1"
fi
pwait()
{
process=$1
action=$2
count=0
if [[ $action == "stop" ]]
then
until ! pgrep -q $process || [[ $count -gt 5 ]]
do
echo "waiting for $process to stop $count ..."
sleep 1
((count++))
done
if ! pgrep -q $process; then
echo "$process stopped OK ..."
else
error_exit "$process failed to stop"
fi
else
# action is start
sleep 1 # give process time to die for configuration file errors ...
until pgrep -q $process || [[ $count -gt 5 ]]
do
echo "waiting for $process to start $count ..."
sleep 1
((count++))
done
if pgrep -q $process; then
echo "$process started OK ..."
else
error_exit "$process failed to start"
fi
fi
}
mydb_start()
{
if pgrep -q mysqld; then
echo "mysql is already started ..."
exit 0
fi
if [ ! -f "/opt/homebrew/opt/mysql/homebrew.mxcl.mysql.plist" ]; then
error_exit "Cannot start mysql -- homebrew.mxcl.mysql.plist is missing ..."
fi
cp /opt/homebrew/opt/mysql/homebrew.mxcl.mysql.plist ~/Library/LaunchAgents/
if [ "$?" != "0" ]; then
error_exit "homebrew.mxcl.mysql.plist copy failed ..."
fi
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist > /dev/null 2>&1
pwait mysql start
}
mydb_stop()
{
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist > /dev/null 2>&1
pwait mysql stop
# Don't start automatically
rm -f ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
}
mydb_restart()
{
# First, stop mysql ...
if ! pgrep -q mysqld; then
echo "mysql is already stopped, so just start it up ..."
mydb_start
exit 0
fi
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist > /dev/null 2>&1
pwait mysql stop
# Then, start mysql ...
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist > /dev/null 2>&1
pwait mysql start
}
mydb_status()
{
# Get info about running mysql processes
ps -axo user,pid,start,etime,time,nice,vsz,rss,command |\
egrep 'PID|mysql' | sed '/grep/d'
}
case $action in
"start")
mydb_start
;;
"stop")
mydb_stop
;;
"restart")
mydb_restart
;;
"status")
mydb_status
;;
*)
# Should never happen ...
error_exit "Invalid action ..."
;;
esac
exit 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.
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.
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 /opt/homebrew/etc/my.cnf. The default content is:
$ cat /opt/homebrew/etc/my.cnf
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
I updated my local user mysql configuration after spending some time researching 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. You can add the following lines to your ~/.my.cnf file:
[mysqld]
max_allowed_packet = 64M
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 128M
innodb_buffer_pool_instances = 1
[mysqldump]
max_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.
You can verify your configuration changes after restarting mysql with the show variables command. Start the mysql client and execute commands like:
mysql> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0.01 sec)
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:
$ mysql_config_editor set --login-path=ap509adm --host=localhost --user=root --password
Enter password:
$ ls -l ~/.mylogin.cnf
-rw------- 1 george staff 152 Nov 14 15:01 /Users/george/.mylogin.cnf
$ mysql_config_editor print --all
[ap509adm]
user = "root"
password = *****
host = "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:
$ mysql --login-path=ap509adm
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.3.0 Homebrew
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
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> \q
Bye
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:
$ mydbdrop
Usage: mydbdrop login-path DB
Drop all tables from a MySQL database.
$ mydbdrop ap509adm wp509db
Deleting wp_commentmeta table from wp509db database...
Deleting wp_comments table from wp509db database...
Deleting wp_links table from wp509db database...
Deleting wp_options table from wp509db database...
Deleting wp_postmeta table from wp509db database...
Deleting wp_posts table from wp509db database...
Deleting wp_term_relationships table from wp509db database...
Deleting wp_term_taxonomy table from wp509db database...
Deleting wp_termmeta table from wp509db database...
Deleting wp_terms table from wp509db database...
Deleting wp_usermeta table from wp509db database...
Deleting 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:
#!/bin/zsh
# mydbdrop login-path DB
mylp="$1"
mydb="$2"
# Usage
if [ ! $# -eq 2 ]
then
echo "Usage: mydbdrop login-path DB"
echo " Drop all tables from a MySQL database."
exit 0
fi
tables=$(mysql --login-path=$mylp $mydb -sN -e 'show tables')
if [ $? -ne 0 ]
then
echo "Error - Cannot connect to mysql server usingn given login-path or database does not exits!"
exit 1
fi
# make sure one or more tables exists
if [ -z ${tables} ]
then
echo "Warning - No table found in $mydb database!"
exit 0
fi
# Delete all the tables in the DB
for t in ${=tables}
do
echo "Deleting $t table from $mydb database..."
mysql --login-path=$mylp $mydb -e "drop table $t" > /dev/null 2>&1
if [ $? -ne 0 ]
then
echo "Error - Could not delete table $t!"
exit 1
fi
done
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.