Upgrading from OS X 10.9 Mavericks to OS X 10.10 Yosemite and from MySQL 5.1 to MySQL 5.6 Using MacPorts

Intro:
I do web development using PHP, MySQL, and Apache on my Mac.  I maintain my web development software using MacPorts.  In December 2014, I had been running OS X 10.9 Mavericks and MySQL 5.1 server.  OS X 10.10 Yosemite had come out a couple months earlier, and I decided it was time to upgrade.

I ran into a lot of problems when doing the upgrade but eventually got them sorted out by relying on info other people had posted online and by going through a lot of trial and error.  I initially wrote this document for myself just after the upgrade in early December 2014, but I figured it could help others as well and finally now got around to setting up a blog and posting an explanation of how I got the MySQL upgrade to work..

BTW, the reason I was running MySQL 5.1 before was that my webhost is 1and1, and they currently use MySQL 5.1.  i wanted to keep my development and server environments similar to avoid version issues.  it looks like MySQL 5.1 (mysql51-server) is no longer supported on OS X 10.10 Yosemite.  so i don’t really have a choice but to upgrade.  i’ll just need to do a little extra testing on a test site hosted on 1and1.

—-

Note:
i should have backed up my databases using mysqldump before upgrading from mavericks to yosemite, but i didn’t.  please do that next time before starting.

—-

Aliases i have set up in ~/.env (on ksh) that i used below:

alias ll=’ls -l’
alias stopmysql=’sudo launchctl unload -w /Library/LaunchDaemons/org.macports.mysql56\-server.plist’
alias startmysql=’sudo launchctl load -w /Library/LaunchDaemons/org.macports.mysql56\-server.plist’

—-

mysql51-server no longer supported by Yosemite.
installed mysql56-server via macports but connecting to it gave me a socket error:
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/opt/local/var/run/mysql56/mysqld.sock’ (2)
.  spent a several hours trying to figure it out.  eventually looked in Console (type Console into Spotlight on your Mac to find it) and found that the mysql56-server daemon wasn’t staying up.

—-

this is one of the things i took care of while trying to fix the socket error (this is part of the output of ‘sudo port install php55-mysql’):

To use mysqlnd with a local MySQL server, edit
/opt/local/etc/php55/php.ini and set
mysql.default_socket,
mysqli.default_socket and
pdo_mysql.default_socket
to the path to your MySQL server’s socket file.

For mysql5, use /opt/local/var/run/mysql5/mysqld.sock
For mysql51, use /opt/local/var/run/mysql51/mysqld.sock
For mysql55, use /opt/local/var/run/mysql55/mysqld.sock
For mysql56, use /opt/local/var/run/mysql56/mysqld.sock
For mariadb, use /opt/local/var/run/mariadb/mysqld.sock
For percona, use /opt/local/var/run/percona/mysqld.sock

—-

as mentioned, i spent a few hours and still got socket errors.  this page was helpful:

http://superuser.com/questions/630080/mysql-5-6-server-via-macports-wont-start

*** Figured it out. Opened LaunchDaemon plist file to see the exact command which would run. Ran that manually to see the startup output and eventually came up with the solution:

sudo mysql_install_db5 –datadir=/opt/local/var/db/mysql56
sudo chown -R _mysql:_mysql /opt/local/var/db/mysql56/
sudo mysql_upgrade

If you need help tracking down startup errors, open Console and messages containing “mysql”. In my case the process was quickly closing, causing an infinite loop of retries. *** To dig deeper, open this file to see how it’s launched:

/Library/LaunchDaemons/org.macports.mysql56-server.plist

From there we can see it basically runs

/opt/local/bin/daemondo –label=mysql56-server –start-cmd /opt/local/lib/mysql56/bin/mysqld –user=_mysql

So at the command line try

sudo -u _mysql /opt/local/lib/mysql56/bin/mysqld

*** and watch the startup output.

—–

when i watched the startup output, i saw there was an error about the ‘max_long_data_size’ server var being deprecated (used for uploading large files).

this page says it’s deprecated in 5.5.  became a fatal error in version 5.6.

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_long_data_size

so i edited
/opt/local/etc/mysql56/my.cnf
to remove max_long_data_size.  it’s now this:
# Use default MacPorts settings
!include /opt/local/etc/mysql56/macports-default.cnf

[mysqld]
max_allowed_packet=4M

—-

was still having problems.  found this page (which is for yum, not macports, but is close enough) that led me to try to start from scratch:

http://stackoverflow.com/questions/9083408/fatal-error-cant-open-and-lock-privilege-tables-table-mysql-host-doesnt-ex

Uninstalled mysql using yum remove mysql*

Recursively deleted /usr/bin/mysql and /var/lib/mysql

Also deleted the file /etc/my.cnf.rmp

Used ps -e to check the processes to make sure mysql wasn’t still running.

Rebooted server with reboot

Ran yum install mysql-server. This also seems to install the mysql client as a dependency.

gave mysql ownership and group priveleges with chown -R mysql /var/lib/mysql and chgrp -R mysql /var/lib/mysql

Used service mysqld start to start MySQL Daemon.

—-

here’s how to start from scratch / get a clean slate:

474     sudo port uninstall mysql56-server mysql56
475     port installed | grep mys # to verify no more mysql installed
480     cd /opt/local/var/db
481     ll
482     ll mysql56
483     sudo mv mysql56 mysql56.bak # make sure there’s no /opt/local/var/db/mysql56 directory after this.
492     cd /opt/local/etc
493     cd mysql56
494     ll
496     cat my.cnf
497     ll /opt/local/etc/mysql56/my.cnf
498     ll
499     cd /opt/local/etc
500     ll
502     sudo mv mysql56 mysql56.bak # make sure there’s no mysql56 config after this.

—-

i then installed mysql56-server and soon ran into permission errors.  then found this article, which said how to fix the permissions:

http://stackoverflow.com/questions/27325754/setup-mysql-5-6-with-macports

chown -R _mysql:_mysql /opt/local/var/db/mysql56

—-

here’s how i successfully installed and tested it:

525     sudo port install mysql56-server
526     c
507     sudo chown -R mysql /opt/local/etc/mysql56/
508     sudo chgrp -R mysql /opt/local/etc/mysql56/
509     sudo -u _mysql /opt/local/lib/mysql56/bin/mysql_install_db
528     /opt/local/lib/mysql56/bin/mysqladmin -u root password ‘*****’ # may be able to just leave out the ‘*****’ and enter it at the prompt.

# to start mysql server:
529     cd /opt/local ; sudo /opt/local/lib/mysql56/bin/mysqld_safe &

# to test mysql:
/opt/local/lib/mysql56/bin/mysql -u root -p

that worked (i.e. i was able to connect without a socket error).
i restarted my computer and tried to connect again.  but mysql didn’t run on startup.

found this article but didn’t quite use it:

http://stackoverflow.com/questions/26476391/how-to-auto-load-mysql-on-startup-on-osx-yosemite

how i enabled mysql 5.6 at startup on os x yosemite 10.10:
541     cd /Library/LaunchDaemons/
542     ll
543     cat com.mysql.mysqld.plist # this file was from July 5, 2009.  probably an old mysql installation from when i first got a macbook pro laptop in june 2009 (since replaced with a MBA).
545     ll
546     sudo mv com.mysql.mysqld.plist com.mysql.mysqld.plist.bak # to disable it just in case it’s being used
547     ll
549     sudo vi org.macports.mysql56-server.plist  # i saw that it said this:
<key>Disabled</key><true/> so i commented it out:
<!–<key>Disabled</key><true/>–>

restarted my computer and then verified mysql was running at startup.  checked using phpmyadmin and also
/opt/local/lib/mysql56/bin/mysql -u root -p

—-

now that mysql 5.6 was running, i needed to move my databases from my mysql 5.1 installation to my new mysql 5.6 installation.  i should have backed up my databases using mysqldump before upgrading from mavericks to yosemite, but i didn’t.  however, i did still have the mysql 5.1 db files in
/opt/local/var/db/mysql51 (and did have them backed up via time machine and crashplan)

as a test, i moved a db folder from
/opt/local/var/db/mysql51 to
/opt/local/var/db/mysql56

552    cd /opt/local/var/db/mysql56
553    ll
554    ll ../mysql51
555    sudo mv ../mysql51/mydb1 .

i now saw the database in phpmyadmin, but it gave me an error that the tables didn’t exist.

i then found this article:
—-

http://stackoverflow.com/questions/4260546/mysql-table-does-not-exist-error-but-it-does-exist

This just happened to me and after a while I found the answer on a blog article, and wanted to put it here as well.

If you copy the MySQL data directory from /var/lib/mysql to /path/to/new/dir, but only copy the database folders (i.e. mysql, wpdb, ecommerce, etc) AND you do have innodb tables, your innodb tables will show up in ‘show tables’ but queries on them (select and describe) will fail, with the error Mysql error: table db.tableName doesn’t exist. You’ll see the .frm file in the db directory, and wonder why.

For innodb tables, it’s important to copy over the ib* files, which in my case were ibdata1, ib_logfile0, and ib_logfile1. Once I did the transfer making sure to copy those over, everything worked as expected.

If your my.cnf file contains “innodb_file_per_table” the .ibd file will be present in the db directory but you still need the ib* files.

—-

here’s where i moved the ib* files:
552    cd /opt/local/var/db
571    stopmysql
576    ll mysql51
577    sudo cp -pv mysql51/ib* mysql56/
578    startmysql

# now verified i could see all tables through phpmyadmin

# next i copied existing dbs to mysql56:
580    cd mysql51
# use -p option to copy timestamps.
593    sudo cp -Rpfv mydb1 mydb2 ../mysql56/

—-

either at this point or sometime earlier, i could see that mysql was no longer giving me missing socket errors, but now it gave me connection refused errors.  to fix this, instead of connecting to 127.0.0.1, i switched the connection info to connect to localhost.  had to update this in
/opt/local/www/phpmyadmin/config.inc.php
as well as in my db config files for my websites.

at this point, the databases were running properly.