How to install MySQL and add it as a service on OSX Yosemite

Standard

As I mentioned in a previous post, I’ve just received a new Macbook Air and I continued setting up my development environment. After setting up Apache, I moved on to MySQL and I was a little bit disoriented.

This guide is a note to my future self on how I did it and also to others out there who may be on the verge of getting lost (or worse, frustrated).

I downloaded MySQL Server 5.6 (Community Version) from the MySQL website ( I needed to sign in with my Oracle ID ). After downloading, I ran the installer and MySQL was installed. The README suggests creating aliases for mysql  and mysqladmin . However there are other commands that are helpful such as mysqldump . Instead, I updated my path to include  /usr/local/mysql/bin

You will need to open a new Terminal  window or run the command above for your path to update.

You can start the server and confirm that your MySQL installation is working fine by running this:

But that’s not how you want to setup your database server, is it? Having to start and restart manually anytime you need the database server. You’d want to install it as a service that starts and stops when you startup and shutdown your Macbook.

To install as a service, you can follow this link. In case the information is no longer available, I’ve copied the information below:

OS X uses launch daemons to automatically start, stop, and manage processes and applications such as MySQL. Using launch daemons is recommended over startup items on OS X.

Note

OS X 10.4 deprecated startup items in favour of launchd daemons, and as of OS X 10.10 (Yosemite), startup items do not function. For these reasons, using launchd daemons is preferred over startup items.

Here is an example launchd file that starts MySQL:

Adjust the ProgramArguments array according to your system, as for example your path to mysqld  might be different. After making the proper adjustments, do the following:

  • Save the XML as a file named  /Library/LaunchDaemons/com.mysql.mysql.plist
  • Adjust the file permissions using the Apple recommended owner “root”, owning group “wheel”, and file permissions “644”
  • Enable this new MySQL service

The MySQL daemon is now running, and automatically starts when your system is rebooted.

This is the post that led me on the path to this solution.

Backup Stored Procedures only on MySQL

Standard

I’ve needed to do this a few times in the past and I still needed to do it again yesterday. Hence, I decided to put it here for reference processes.

 This would export just the stored procedures to the specified file ( sp.sql )

Deleting duplicate rows from a MySQL table – except one!

Standard

Here’s the problem: we have a customer’s database and inasmuch as we’ve tried to eliminate duplicates, we still have them. Multiple Customer Relationship Managers have gone ahead to create customer records without first checking if they exist and in so doing we now have instances where a single customer could appear five to eight times in our database. This is simply not good enough.

We laid this problem at the doorstep of a Tech Intern at the office (while we focused on more important stuff, yeah right) but she couldn’t figure it out. That’s where I came into the picture.

Here’s the Solution

So we had a table of this form:

What we want to do here is to delete all duplicates and leave just one record.

Here’s the SQL statement to do it:

This query, when executed on the dummy table we have above would delete row 1 and row 4 (Google), leaving row 6. It would delete row 2, leaving row 8 (Yahoo) and it would delete row 5 leaving row 7 (MSN).

In essence, the last condition (highlighted) instructs MySQL to delete the records with the lower ids hence, keeping the record with the highest id value. You could change it to  AND s1.`id` > s2.`id`  to keep the record with the lowest id value instead.

Of course, you want to back up your database table before trying this out. Here’s a quick way I discovered some months back: