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.
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
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:
*** 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/
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:
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.
so i edited
to remove max_long_data_size. it’s now this:
# Use default MacPorts settings
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:
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
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
496 cat my.cnf
497 ll /opt/local/etc/mysql56/my.cnf
499 cd /opt/local/etc
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:
chown -R _mysql:_mysql /opt/local/var/db/mysql56
here’s how i successfully installed and tested it:
525 sudo port install mysql56-server
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:
how i enabled mysql 5.6 at startup on os x yosemite 10.10:
541 cd /Library/LaunchDaemons/
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).
546 sudo mv com.mysql.mysqld.plist com.mysql.mysqld.plist.bak # to disable it just in case it’s being used
549 sudo vi org.macports.mysql56-server.plist # i saw that it said this:
<key>Disabled</key><true/> so i commented it out:
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
552 cd /opt/local/var/db/mysql56
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:
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
576 ll mysql51
577 sudo cp -pv mysql51/ib* mysql56/
# 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
as well as in my db config files for my websites.
at this point, the databases were running properly.