MySQL is a free relational database, now owned by Oracle.
Here are instructions for setting up a 64-bit MySQL on Mac OS X, tested
August 2010 with 10.6.4 Snow Leopard (MySQL 5.1.49),
October 2012 with 10.8.2 Mountain Lion (MySQL 5.6.17),
April 2014 on 10.9.2 Mavericks (MySQL 5.6.25),
Oct 2015 on 10.10.5 Yosemite (MySQL 5.6.25),
May 2016 on 10.11.5 El Capitan (MySQL 5.7.13),
July 2017 on 10.12.6 Sierra (MySQL 5.7.19 and MySQL 8.0.11).
and March 2018 on 10.13.3 High Sierra (MySQL 5.7.19 and MySQL 8.0.11).
(10 Jun 2018) Oracle provides version 8.0.11 for OS X 10.13 High Sierra, and says it will work on Sierra also. (They previously provided 5.7.21.) Check the Release Notes for bug fixes and new features. I found that some queries that used to work failed with the new version: see below. Download the OSX version DMG file of the MySQL Community Server from mysql.com. When I upgraded to 8.0.11, I flailed about because I did not read the upgrading directions carefully. Be smarter than I was. (Note that there are many features of MySQL that I don't exercise and have not tested; test it for your needs.)
Installing 5.7.21 on High Sierra was quick and did not encounter any problems.
sudo mkdir /var/mysql sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock
In El Capitan, Sierra, and High Sierra, the MySQL preference pane works again, so you can use it to start MySQL on system restart. See below for Yosemite directions.
Your old MySQL database may still be on disk in a directory like /usr/local/mysql-VERSION/data/, and can be deleted at some point.
If you make a file named .my.cnf in your home directory, then you can use the mysql command without giving a password. Mine contains
[client] user=root password=WHATEVER host=localhost
If you have only one database, you can put that value in the file also, with database=DBNAME.
For Sierra, see Neil Gee's page for instructions. (I do not install or use PHP or PHPMyAdmin, for security reasons, so I skipped those parts.) I also found a page that describes one user's process: http://weblog.rubyonrails.org/2009/8/30/upgrading-to-snow-leopard. I skipped the Ruby parts.
When I initially installed Sierra, my machine inherited MySQL 5.7.13 from El Capitan, and it seemed to work. The only thing I noticed was that Sierra appeared to hang for about 30 minutes when shutting down. On a hunch, I opened the MySQL control panel and tried to stop the MySQL server; the control panel hung for about 30 minutes. Once MySQL had stopped, I could shut down the Mac quickly. I decided to install the latest MySQL.
When I installed MySQL 8.0.11 on Sierra, I was unable to access MySQL from Perl via DBD::mysql. I got the message
install_driver(mysql) failed: Can't load '/opt/local/lib/perl5/site_perl/5.26/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle' for module DBD::mysql: dlopen(/opt/local/lib/perl5/site_perl/5.26/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle, 1): Library not loaded: /usr/local/mysql/lib/libmysqlclient.20.dylib
In /usr/local/mysql-8.0.11-macos10.13-x86_64/lib/ I did ln -s libmysqlclient.21.dylib libmysqlclient.20.dylib because the .20 file did not exist. That worked better: I was able to use Perl to access MySQL.
Attempting to execute a SHOW TABLE STATUS command still failed, with the message
The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
I did mysql_upgrade -u root -p and that fixed SHOW TABLE STATUS.
Executing queries that had worked on MySQL 5.7.13, I got failure messages from 8.0.1 like
Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'thvv_userlist.wtcumref.refurl' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by.
Apparently MySQL had further strengthened the ONLY_FULL_GROUP_BY mode. I had to rewrite my queries to make them execute. My understanding is that MySQL was accepting ambiguous queries and executing somehow, not necessarily correctly. They changed to not accepting them. I couldn't find where this is documented, in the enormous list of changes.
Under El Capitan the install appeared to fail, because the old version was running. Stop MySQL before installing with sudo launchctl unload -w /Library/LaunchDaemons/com.mysql.mysql.plist . This command will also stop MySQL from being started by the system, so that you can control it from the System Preferences pane.
I had trouble getting MySQL to work. It worked for a while but then after a reboot I kept getting a socket error.
# mysqladmin status mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!
Searching the web was little help. Finally I looked in /usr/local/mysql/data/mysqld.local.err and noticed the messages
2016-06-05T22:18:39.011501Z 0 [Note] Server hostname (bind-address): '*'; port: 3306 2016-06-05T22:18:39.011603Z 0 [Note] IPv6 is available. 2016-06-05T22:18:39.011619Z 0 [Note] - '::' resolves to '::'; 2016-06-05T22:18:39.011683Z 0 [Note] Server socket created on IP: '::'.
This looked like a bad idea.. I was not using IPV6 anywhere else. InI changed "Configure IPV6" to link-local only and restarted. MySQL 5.7.13 was running and I could contact it.
You can no longer say
mysql dbname --exec='source temppic.sql;'
and must change this to
mysql dbname --execute='source temppic.sql;'
so I had to modify all my shell scripts and Perl programs that used --exec.
Executing queries that had worked on older versions of MySQL, I got messages like
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dbname.hits.systime' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
because MySQL has strengthened the ONLY_FULL_GROUP_BY mode and made it the default with version 5.7. (Basically, this was a MySQL-only feature that was not in standard SQL and whose interpretation was arguable.) A quick temporary fix is to execute the mysql command
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
omitting the ONLY_FULL_GROUP_BY mode (this will stick until you restart mysqld). You could also include this mode setting in the MySQL configuration to make it permanent.
A better long term fix is to recode the query that got the error: one way is to wrap the offending items with ANY_VALUE(), but this function is not backward compatible with older versions of MySQL. I recoded my queries to wrap the offending variables with an aggregation function such as MAX(), and that worked for most of them. For others, I actually had to think about what I was doing.
For Yosemite the Systen Preferences pane for MySQL did not work, so you had to set up a .plist file and point launchctl at it. See MySQL does not start in OSX Yosemite 10.10. This note is not quite accurate.. follow these steps: set up the .plist file as described in the note, and set its access; then do sudo launchctl load -w /Library/LaunchDaemons/com.mysql.mysql.plist . MySQL should start, and restart after a system restart.
In Mountain Lion, I got a failure message about dyld when trying to install the Perl DBD module. Fixed with cd /usr/local; sudo mkdir lib; cd lib; ln -s/usr/local/mysql/lib/*.dylib .
(Gotcha:) With MySQL 5.6.17 on Mountain Lion, some of my database files would not load, because the mysql command fails, instead of warning, when a field value is too long for the field. This problem occurs because STRICT mode is now the default. The fix is to turn STRICT mode off, or fix the data.