Setting up MySQL 5.7.* on MAMP Pro (v4.4)

Posted on 29th Mar 2018

Out of the box MAMP PRO v4.4 comes with a flavour of MySQL 5.6 (at the time of writing it's 5.6.38 specifically). Why they don't with 5.7 is beyond me (it was first released in 2013!) but that's by the by.

I wanted to live life on the edge and use the latest and greatest so here are my steps navigating some of the 'gotchas' and using some wizardry to get it playing nicely.

DISCLAIMER. MAMP do document a method to setup a separate instance of MySQL 5.7 that runs in parallel to their packaged 5.6 version, but you have to manage multiple ports and the databases get stored in separate places etc etc. Meh. Lets' update the version of MySQL MAMP uses out of the box and be done with it.

Firstly here was my setup: OS. Mac OS 10.12.6 MAMP PRO. 4.4.1

Step 1. Download and Install MySQL 5.7.*

Firstly we're going to need to get a version of MYSQL 5.7 that's compatible with our Operating System, backup our existing MAMP setup (you know, just in case) and migrate MAMP to use it.

Here's the bash script I used to do all the above in one fell swoop. I just saved in a file named 'mamp-migrate.sh' to my Desktop.

#!/bin/sh
 
wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-macos10.12-x86_64.tar.gz
tar xfvz mysql-5.7*
 
echo "stopping mamp"
sudo /Applications/MAMP/bin/stop.sh
sudo killall httpd mysqld
 
echo "creating backup"
sudo rsync -arv --progress /Applications/MAMP ~/Desktop/MAMP-Backup
 
echo "copy bin"
sudo rsync -arv --progress mysql-5.7.*/bin/* /Applications/MAMP/Library/bin/ --exclude=mysqld_multi --exclude=mysqld_safe 
 
echo "copy share"
sudo rsync -arv --progress mysql-5.7.*/share/* /Applications/MAMP/Library/share/
 
echo "fixing access (workaround)"
sudo chmod -R o+rw  /Applications/MAMP/db/mysql/
sudo chmod -R o+rw  /Applications/MAMP/tmp/mysql/
sudo chmod -R o+rw  "/Library/Application Support/appsolute/MAMP PRO/db/mysql/"
 
echo "starting mamp"
sudo /Applications/MAMP/bin/start.sh
 
echo "making symlink, enter sudo password"
sudo ln -s /Applications/MAMP/tmp/mysql/mysql.sock /tmp/mysql.sock
 
echo "migrate to new version"
/Applications/MAMP/Library/bin/mysql_upgrade -u root -p

To run this script just open up the Terminal application, cd to your Desktop, e.g. cd ~/Desktop and then run 

bash mamp-migrate.sh

If you're not running MAC OS 10.12 or want a different version of MySQL (the above script installs v5.7.20) there are a few other flavours of this script here that claim to work for other OS and MySQL versions.

Step 2. Check MAMP PRO is picking up MySQL 5.7

All being well you should be able to open up MAMP PRO, start the MySQL server and navigate to the MySQL settings (click on MySQL in the left sidebar). At the very top of it should read 'Version: 5.7.20' or whichever version of MYSQL 5.7 you ended up installing.

Step 3. my.cnf

Under the hood one huge change that tends to catch people out upgrading to MySQL 5.7 is that it defaults to include some options within the sql_mode setting (in MySQL v5.6 this setting was empty). One such inclusion within this setting is the 'ONLY_FULL_GROUP_BY' flag. There's a strong chance that if you're developing on a CMS or platform released pre-2017/2018 that this alone is going to break some elements of the installation or in some cases take a whole site down. Thankfully this should be fairly straightforward to mitigate if it is causing you headaches.

Within MAMP PRO open up File » Edit Template » MySQL (my.cnf) » 5.7.20 (or whichever version you installed). This will open up a text editor window. You should be able to see a [mysqld] line. Beneath that line add the following

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

This effectively retains all the default flags for the sql_mode setting except the ONLY_FULL_GROUP_BY flag. Hit CMD+s to save the file then close the text editor window. MAMP should restart MySQL to pickup the new settings. Try your website or application again to see if the new settings have been picked up.

If they haven'tand this was the case for me, read on...

Step 4. MAMP PRO my57.cnf template

Behind the scenes whenever the MySQL server on MAMP PRO is started it takes a template .cnf file and generates a my.cnf file from it that the server can them parse and execute. Step 3 above in theory is editing that template file but sometimes it seems that isn't the case. We can do this manually though by finding the correct template file in our filesystem. For me the template file in question is named my57.cnf and can be found here: 

/Applications/MAMP PRO/MAMP PRO.app/Contents/Resources/my57.cnf

You can either open this file in a text editor or use vim or nano etc to edit it within the Terminal. I copied the contents from the file in Step 3 into this my57.cnf file (which was completely empty), saved the file and restarted the MySQL server in MAMP PRO. Voila! My site came back online.

Here are the final contents of the my57.cnf file

# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /Library/Application Support/appsolute/MAMP PRO/db/mysql57) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
#port = 9999
socket = /Applications/MAMP/tmp/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
#port = 9999
socket = /Applications/MAMP/tmp/mysql/mysql.sock
key_buffer_size = 16M
max_allowed_packet = 16M
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION<!--
explicit_defaults_for_timestamp = 1
#table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
MAMP_bind-address_MAMP
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
MAMP_skip-networking_MAMP
# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
# Point the following paths to different dedicated disks
#tmpdir = /tmp/ 
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = "/Library/Application Support/appsolute/MAMP PRO/db/mysql57"
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = "/Library/Application Support/appsolute/MAMP PRO/db/mysql57"
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
# DONT REMOVE: MAMP PRO my57.cnf template compatibility version: 10