Wednesday, July 15, 2015

How to use MySQL 5.6 with MAMP 3 and MAMP PRO 3

Although MySQL 5.6 has been available since 2011 its market share is still much smaller than that of its older brother MySQL 5.5. That is why MAMP 3 and MAMP PRO 3 still bundle v5.5.

For those of you who absolutely require MySQL 5.6 to be part of their development environment - maybe you are a Magento developer - we have released a shell script that updates the MySQL component of MAMP and MAMP PRO to v5.6. The script requires at least MAMP and MAMP PRO 3.3, older versions are untested.

By the way, if you do not know why you would want to upgrade to MySQL 5.6, don't do it! Reverting the MySQL software back to 5.5.x is easy, just re-install MAMP 3 and MAMP PRO 3. Future updates of MAMP 3 and MAMP PRO 3 will also revert MySQL back to 5.5.x. But: Data you have entered using MySQL 5.6 will not be transitioned back to 5.5.

Let's get started
Step 1:
Download the script archive and unpack it. You will get a file called Install_MySQL_5.6.25.command.

Step 2:
Start the script by double-clicking it. This will open a shell window in that displays the output of the script.

The script gives some instructions and explanations, please read them carefully. You will also have to enter your Mac OS X admin password to allow for some file system modifications.

The script automatically creates a copy of your databases in /Applications/MAMP/db/mysql and /Library/Application Support/appsolute/MAMP PRO/db/mysql (you need them if you want to go back to MySQL 5.5), downloads MySQL 5.6.25 from our server and installs it into the MAMP folder.

Step 3:
This step is very important, please do not skip it. It varies depending on whether you are a MAMP or a MAMP PRO user.

If you are a MAMP user and have not changed the MySQL configuration file, you may now start the MAMP application, click Start Servers and select Upgrade MySQL databases from the menu Tools. Click Upgrade to convert the databases to the new format of MySQL 5.6.

If you have changed a MySQL configuration file my.cnf, please make sure that its content is compatible with MySQL 5.6. Otherwise MySQL might not be able to start.

If you are a MAMP PRO user and have modified the MySQL template you must also make sure its settings are compatible with the new database server. Type CMD-2 to open the MySQL template in the integrated template editor. You can use the find function (CMD-F) to quickly locate certain options.

For example, the variable table_cache is only compatible with MySQL 5.5 but breaks the newer version. You should either replace it with table_open_cache, deactivate it by putting a # character before its line or completely remove it from the config file.

After you have checked the MySQL template, start the servers and upgrade the databases with the command Upgrade MySQL databases from the menu Tools.

Back to the roots
If MySQL 5.6 fails to start, the first thing you should do is to look at the error entries in the error log. MAMP users find in at /Applications/MAMP/logs/mysql_error_log.err, MAMP PRO users only have to click on the right arrow next to log file area in the MySQL section to open it. The log file might contain helpful hints of what went wrong.

You can spot the aforementioned table_cache option, for example, because of the log entry

[ERROR] /Applications/MAMP/Library/bin/mysqld: unknown variable 'table_cache=64'

The safest way to check if you are really running MySQL 5.6 is to open a Terminal window and type

/Applications/MAMP/Library/bin/mysqld --version

It's easy to go back to MySQL 5.5 should the need arise. Simply re-install MAMP 3 and MAMP PRO 3. The installer takes care of restoring the databases of MySQL 5.5. As the installer simply restores the databases the shell script created during the installation of MySQL 5.6, all data you have entered using MySQL 5.6 will not be transitioned back to 5.5. Going to MySQL 5.6 is a one-way road data-wise.

Nota bene: The database restoration also will only work if you install MAMP 3.3, older versions only install the MySQL 5.5 binaries.