Posts tagged with “mysql”

MacPorts, MySQL 5 and the Launch Daemons

Posted on Thursday, July 16th, 2009 at 6:16pm by Rob Wilkerson

Update, 7/29/2009: In response to my question about this on StackOverflow, Mike Richards offered an infinitely better solution. Apparently MacPorts is effectively deprecating the mysql5 +server path in favor of a new mysql-server package. I can’t confirm this personally, but it sounds reasonable enough.

That sounds a little bit like a Harry Potter title, but the content isn’t nearly as entertaining. For the past year or two, I’ve been using a MySQL installed via MacPorts, the (pseudo-) apt repository for Mac ports (get it?) of Unix applications and utilities. MacPorts has been fantastic and I haven’t regretted the decision to move away from either OS X’s native MySQL install or from MAMP, an all-in-one solution that I had used previously. The last few times I’ve installed MySQL, though, I’ve noticed that I haven’t been able to get MySQL to start automatically when I login.

Following Chad Kieffer’s excellent tutorial for installing & configuring a MacPorts MySQL install, I would get myself to the point where I execute launchctl to load the plist file that will start MySQL automatically:

$ sudo launchctl load -w /Library/LaunchDaemons/org.macports.mysql5.plist

Unlike Chad, I want MySQL to start automatically. Admittedly, my work-life balance sucks; I’m more than likely doing something work-related if I’m sitting behind the keyboard. Given that, the server might as well be ready to respond, right? Except that the plist I’m trying to load…isn’t there to be loaded.

The first time that I did the install, the plist was there and loaded as expected, but the last 2 or 3 times that has not been the case. I don’t know what changed with the MacPorts bundle, but that plist simply isn’t there. Fortunately, I still have my old install around, so I faked it.

If anyone else is having the same issue, here’s how you too can fake it:

  1. Create a directory for the launch scripts.
    $ mkdir -p /opt/local/etc/LaunchDaemons/org.macports.mysql5
  2. Download the files that no longer get installed, mysql5.wrapper and org.macports.mysql5.plist. I’m making mine available since I don’t know where else to get them. Save both files to the directory you just created.
  3. Set the proper ownership and permissions.
    $ sudo chown root:wheel /opt/local/etc/LaunchDaemons/org.macports.mysql5/*
$ sudo chmod 755 /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper
$ sudo chmod 644 /opt/local/etc/LaunchDaemons/org.macports.mysql5/org.macports.mysql5.plist
  4. Create a soft link to the newly downloaded plist file in /Library/LaunchDaemons.
    $ cd /Library/LaunchDaemons
$ ln -s /opt/local/etc/LaunchDaemons/org.macports.mysql5/org.macports.mysql5.plist org.macports.mysql5.plist
  5. Load the plist file, as indicated in Chad’s instructions and duplicated above. For the sake of keeping it all in one place:
    $ sudo launchctl load -w /Library/LaunchDaemons/org.macports.mysql5.plist
  6. Reboot.
  7. Verify that MySQL has started.
    $ sudo ps -ef | grep mysql

You should see output that looks something like this:

    0    65     1   0   0:00.00 ??         0:00.00 /opt/local/bin/daemondo --label=mysql5 --start-cmd /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper start ; --stop-cmd /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper stop ; --restart-cmd /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper restart ; --pid=none
    0    85     1   0   0:00.01 ??         0:00.01 /bin/sh /opt/local/lib/mysql5/bin/mysqld_safe --datadir=/opt/local/var/db/mysql5 --pid-file=/opt/local/var/db/mysql5/rob17.local.pid
   74   111    85   0   0:07.48 ??         0:19.75 /opt/local/libexec/mysqld --basedir=/opt/local --datadir=/opt/local/var/db/mysql5 --user=mysql --pid-file=/opt/local/var/db/mysql5/rob17.local.pid --socket=/tmp/mysql.sock
  501  3370  3145   0   0:00.00 ttys003    0:00.00 grep mysql

If you do, then you’re golden. If you don’t, then you probably made a mistake. If the mistake is mine, please let me know in the comments and I’ll make the appropriate adjustments.

, , , 4 Comments

Sync MySQL Databases

Posted on Saturday, September 20th, 2008 at 12:24pm by Rob Wilkerson

I run three different environments for robwilkerson.org. I have local development environment where I update Chyrp, the blogging software that runs the site, tweak parts of my theme and introduce new modules to the configuration. I also have a staging environment where I ensure that changes made in dev look and work okay in an environment that closely resembles my final environment, production.

Something I’ve long wanted to do is to keep the staging and production MySQL databases sync’d up so that I can create an even closer resemblance between the environments and get a better feel for the impact of the changes I make as they move up the stack. This morning I finally set about implementing this process. The process itself is pretty straightforward and looks like this:

  1. Export the production database to a SQL script
  2. In the exported script, replace any references to the production database name with the staging database name
  3. Execute the script against the staging database

The script to execute that process looks like this: 

mysqldump -umyusername \
           -pmypassword \
           --opt \
           --no-create-db \
           --complete-insert \
           --databases production-db-name | \
sed s/production-db-name/staging-db-name/ | \
mysql -umyusername \
      -pmypassword \
      -D staging-db-name

Finally, I created a cron job to run this command every night at midnight. In my setup, the production and databases exist on the same host. If they didn’t, I’d need to add the -h flag to at least one of the mysql commands. Similarly, this command is executed on the same machine as those databases which eliminates the need for the -h flag on either command.

I wrote this primarily to supplement my own memory, but I offer it to you at no cost. I’m not a MySQL DBA, nor do I care to become one in the near future; if there’s an easier or better way, I’d love to hear about it.

, 0 Comments