Another scratchpad post. This one is a reminder of what I did to repair MySQL on OSX Server after the upgrade from Mavericks to Yosemite kinda broke things.
I was working to solve two problems: intermittent “unable to connect to database” errors on all our WordPress sites, and the dreaded “unable to update PID” errors when starting and stopping MySQL.
- I think the “unable to connect” errors are caused by intruders trying to brute-force break the passwords on my (roughly 35) web sites. This problem can possibly be cured just by doing the “tuning” steps at the end of the cookbook.
- “Unable to update PID…” type problems are more symptomatic of a broken MySQL implementation and probably require the whole process.
None of these were terrible (a system-restart every few days kept things more or less in check) so I limped along for a while after upgrading from Mavericks to Yosemite, but it finally drove me crazy and I decided to upgrade MySQL and rebuild all the databases.
The cookbook
I tried several approaches and finally landed on one which I can reliably repeat in the future (as long as the good folks at Mac Mini Vault continue to provide their magnificent script).
backup:
I used backups from all sorts of places. I thought I was being a little over the top, but things went wrong and I was really glad to have all these safety nets. Here were the backups I had available:
- Time Machine backups of the /usr/local/mysql/ directory
- Pre-upgrade copies of the /usr/local/mysql/data/ directory (and their Time Machine backups)
- Historical (nightly) MYSQLDUMPs of all the databases (and their Time Machine backups). Use this command to write each database to a text file. I have a script that does all 35 of my databases at once, every night.
sudo mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
clear out the previous installation of MySQL:
This was by far the hardest part to get right. MySQL doesn’t have an “uninstall” script and reacts badly when little odds and ends are left over from previous installations. My OSX Server has been running MySQL since OSX Lion and there was a fair amount of cruft left behind that was causing some of the trouble. Here’s my current list of things to move or remove (although not all of them will exist on any given machine):
- move the old data directory (/usr/local/mysql/data/) to someplace else (yet another backup)
- rename the old base MySQL directory (this is the directory with the version-number that the mysql alias points to – I renamed rather than deleted as a backup)
- remove the /usr/local/mysql alias (it’s going to get recreated during the install, pointing at the new/correct base directory)
- move MySQL.com out of /Library/StartupItems/
- move My* out of /Library/PreferencePanes/
- move My* out of your account’s /Library/PreferencePanes/ (I had two mismatched ones of these, one lurking in /Users/admin/Library/PreferencePanes that was really old)
- edit /etc/hostconfig and remove the line MYSQLCOM=-YES- (If it’s still there — this was left over from the days when MySQL shipped as part of OSX Server)
- remove entries for receipts for mysql in /Library/Receipts/Install-history.plist (I edited the plist with a text editor to do this)
- remove receipts for mysql in /private/var/db/receipts/
- remove mysql-related scripts from /Librarly/LaunchDaemons/
- remove any aliases for mysql.sock from /var/mysql/, /etc/ and /private/var/mysql/ (I’ve had good luck leaving the directories in place and just deleting the aliases – ymmv)
- If the Mac Mini Vault (MMV) script has been run before, here are a couple more things:
- remove the MYSQL_PASSWORD item from the desktop
- remove MySQL-install.plist from your /Downloads/ directory
install MySQL using the MMV script:
NOTE: the folks who maintain the script only support it for a clean install of MySQL on a freshly-loaded version of OSX. So this cookbook is OUTSIDE the bounds of what they support — please don’t complain to them if things break. Instead thank them for sharing this script publicly, and consider buying some of their services.
Click HERE to read an introduction to the script on MMV’s site
Click HERE to read the important documentation of the script
Last step: change the MySQL root password:
sudo mysqladmin -u root -p'MMV-generated-password' password 'mypasswd'
MySQL should now be running properly. I restarted the server to make sure MySQL started up on a reboot. I also started and stopped MySQL a few times from the command line to make sure that the “unable to update PID…” problems were solved:
sudo /usr/local/mysql/support-files/mysql.server stop sudo /usr/local/mysql/support-files/mysql.server start
I do NOT TRUST the MySQL preference-pane that is installed in OSX System Preferences and don’t use it – that may have been another source of dreaded “failure to update PID…” errors. Just sayin’
import the databases:
I chose to rebuild my databases from the nightly dumps. I tried various versions of “moving the data directory back and using the Update command” and had a rough time with all of them. Besides, rebuilding the databases for the first time in many years seemed like a good housekeeping item. I have about 35 databases — it took about an hour. Note: change all the ‘mydb’ ‘myuser’ ‘mypasswd’ to values that match your environment.
- Log into mysql as MySQL’s root user:
mysql -u root -p'mypasswd'
- Create the databases in mysql using this command:
create database mydb;
- Create a user for each database in mysql using this command (btw Sequel Pro 1.0.2 is crashing when it creates a user — a known bug, just do it from the command line). Note: I’m assuming that you’re only using MySQL for WordPress sites like I am, and only need one user per database — this process will get a lot more tedious if you have multiple users per database.
grant all privileges on mydb.* to myuser@localhost identified by 'mypasswd';
- Import the text-dump of each database into the newly-created empty one using Sequel Pro — File > Import
tuning:
Two things have really helped with the brute-force attacks. Opening up MySQL a bit and changing a setting in PHP.
To give MySQL a little more oxygen, I followed the guidelines in a sample .cnf file that came with the Mac Mini Vault script. I slightly changed the settings, mostly to make them conform to MySQL standards (I’m not sure whether this matters).
- edit /usr/local/mysql/my.cnf and add these lines at the very bottom (these are just a starting point, feel free to fiddle with them a bit):
innodb_buffer_pool_size=2G skip-name-resolve max_connect_errors=100000 max_connections=500
- edit /private/etc/php.ini and turn off persistent links. Here’s the way my file looks right now:
; Allow or prevent persistent links. ; http://php.net/mysql.allow-persistent mysql.allow_persistent = Off
Image courtesy of Stuart Miles at FreeDigitalPhotos.net