MySQL

2023-11-08

MySQL is a free relational database, now owned by Oracle.

Installing MySQL

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),
on 10.12.6 Sierra (MySQL 5.7.19 and MySQL 5.7.24).
March 2018 on 10.13.3 High Sierra (MySQL 5.7.19 and MySQL 8.0.13).
January 2019 on 10.14.1 Mojave (MySQL 8.0.14 and MySQL 8.0.17).
September 2021 8.0.26 for Big Sur (macOS 11.6).
February 2023 8.0.32 for Ventura (macOS 13.2).

March 2023: Installing MySQL 8.0.32 on Ventura succeeded, installing MySQL with Homebrew. First I renamed .my.cnf temporarily. Then I executed brew install mysql to install the software. I executed mysql_secure_installation and specified the root password. It asked me if I wanted to activate "validate password component" but I said no to that, and to anonymous uers, and to remote users, and to a test database. I let it reload privilege tables and was done. All I had to do was to restore .my.cnf, reload my database dump -- and everything was there.

On a second Ventura machine, I had problems installing MySQL and succeeded in fixing them. Renamed .my.cnf temporarily, ran brew install mysql. All appeared fine, but when I invoked mysql_secure_installation I got the message 

  ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

This was because MySQL was not running. I typed the command mysql.server start and it said 

  Starting MySQL
  . SUCCESS!

I then executed mysql_secure_installation and it correctly connected to the server, asked for a new root password, etc.

Installing MySQL using Homebrew doesn't seem to include the MySQL settings pane for  ► System Settings... ► MySQL.

2021: Installing 8.0.26 on Big Sur succeeded. I got it from the mysql.com community downloads web site. It asked me to supply a password for 'root'. I had problems installing DBD::mysql in CPAN. I was able to fix them. See the CPAN page.

2019: Installing 8.0.13 on Mojave appeared to work. It asked me to supply a password for 'root'. However, there were problems installing DBD::mysql in CPAN. I was able to fix them. A later install on Mojave with 8.0.17 appeared to work.

June 2018: Oracle provides version 8.0.11 for OS X 10.13 High Sierra. 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.

July 2017:I installed MySQL 8.0.11 on Sierra and it did not work: it got a linkage error. Had to install 5.7.24 instead.

Steps

Setting up Auto Start

In El Capitan, Sierra, High Sierra, and Mojave, the MySQL preference pane works again, so you can use it to start MySQL on system restart. See below for Yosemite directions.

Cleaning Up

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.

.my.cnf

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

    [mysqldump]
    user=root
    password=WHATEVER
    host=localhost
    [client]
    user=root
    password=WHATEVER
    host=localhost
    database=DBNAME

If you have only one database, you can put that value in the file also, with database=DBNAME.

Problems

Big Sur

For Big Sur, I downloaded and installed MySQL 8.0.26. This installed a System Preferences control panel. I clicked "initialize database" (asked me to set root's database password) and then "start MySQL" (required system admin password) and MySQL started. I was able to set the password for the root user and reload the database with no trouble.

When I tried to install DBD::mysql in CPAN, I had several problems. One was a linkage error dyld: Library not loaded: @rpath/libmysqlclient.21.dylib, which I fixed by making a symlink.

Then, I got an error 186 running the CPAN tests. This means "unable to write." I checked: the disk was not full. I looked at the Data directory for MySQL, and all the data files in that directory were owned by my userid instead of mysql:mysql. Not sure what I did to cause this... but fixing it solved the problem.

Mojave

For Mojave, I downloaded and installed MySQL 8.0.13. This installed a System Preferences control panel. I clicked "initialize database" (asked me to set root's database password) and then "start MySQL" (required system admin password) and MySQL did not start. This may have been related to an antivirus product. I issued the Terminal command sudo launchctl load -w /Library/LaunchDaemons/com.oracle.oss.mysql.mysql.plist to start MySQL, and it started OK, and restarted after reboot just fine.

Sierra

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: https://weblog.rubyonrails.org/2009/8/30/upgrading-to-snow-leopard. I skipped the Ruby parts.

Hanging at Shutdown

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.

Installation Problems

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.

Default SQL Mode Changed Again

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.

El Capitan

Install problems

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.

Unable to Connect

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. In  ► System Preferences... ► Network ► Ethernet ► Advanced I changed "Configure IPV6" to link-local only and restarted. MySQL 5.7.13 was running and I could contact it.

Syntax Change

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.

Default SQL Mode Changed

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.

Yosemite

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.

Mountain Lion

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.

Home | FAQ © 2010-2023, Tom Van Vleck updated 2023-11-08 08:27