Installing MySQL on Mac OS X

Posted by Matt Thommes on June 14, 2009 | Post type: Gain

Earlier we mentioned how to enable PHP on Mac OS X. MySQL is often a crucial ally to PHP, when developing applications locally. Unfortunately MySQL is not installed by default on Mac OS X client machines. Luckily, the setup process is well-detailed and easy to accomplish.

The instructions are outlined at Marc Liyanage's site, and I will talk briefly about each step, while pointing out additional things I experience while installing MySQL on OS X.

MySQL download

Marc's site suggests downloading MySQL version 4.1, which is not the latest version by a long shot. Perhaps he prefers that version, or he hasn't got around to updating the link on his site. Rather than going to the download link he suggests, you may be better off visiting the main downloads page, dev.mysql.com/downloads/, which automatically provides downloads for the latest version.

Choose "Download" under "MySQL Community Server":

Screenshot of MySQL.com

Then pick the Mac OS X "package format":

Screenshot of MySQL.com

Since I am running Mac OS 10.5 on my Intel MacBook, the appropriate download is "Mac OS X 10.5 (x86)":

Screenshot of MySQL.com

Install location

Once downloaded, click on the package and go through the install process.

When finished installing, you can see where MySQL was physically installed by visiting the Finder and choosing Go > Go to Folder...:

Screenshot of Mac OS X Finder

Type /usr/local/ in the text box, then hit Go. You should see two MySQL folders - one is the main folder (the one that is named "mysql-5.1...."), and the other is a reference/shortcut to the main folder (the one named "mysql"):

Screenshot of Mac OS X Finder

Connecting to MySQL

This sets things up initially, but we still have to tweak some settings to make things work properly.

Open a Terminal window, which is found under Applications > Utilities:

Screenshot of Mac OS X Finder

We're going to navigate to the install folder mentioned above, but using Terminal instead of the Finder. Type cd /usr/local/mysql in Terminal, then hit Return:

Screenshot of Mac OS X Terminal

Now type sudo chown -R mysql data/, and enter your OS password when prompted.

Then type sudo echo, hit Return, then type sudo ./bin/mysqld_safe &, and hit Return again.

Finally, type /usr/local/mysql/bin/mysql test, and hit Return. This should log you directly in to the MySQL monitor, using the "test" database:

Screenshot of Mac OS X Terminal

You've just connected to MySQL!

Feel free to issue MySQL commands on the "test" database to get a feel for using the MySQL monitor via Terminal:

Screenshot of Mac OS X Terminal

Adjust MySQL root password

An important thing to do immediately is adjust the MySQL root password, to ensure the security of your databases.

First, exit the MySQL monitor by typing exit;, then hit Return.

Next, type:

/usr/local/mysql/bin/mysqladmin -u root password new_password_here

... but replace new_password_here with your new password. This ensures that no one can connect without supplying a password - the one you just set.

To change the root password after it's already been set, type this command:

/usr/local/mysql/bin/mysqladmin -u root -p password new_password_here

It will then prompt you for the old password before it can change the password to the new one.

Create Terminal shortcut to access MySQL monitor

Anytime you want to log back in to MySQL via Terminal, type:

/usr/local/mysql/bin/mysql -u root -p

... then enter your password when prompted.

You'll notice this is a lot to type every time you simply want to connect to MySQL via Terminal. It would be nice to avoid having to type /usr/local/mysql/bin every time.

To create a shortcut to that location, type this command:

echo 'export PATH=/usr/local/mysql/bin:$PATH' >> ~/.bash_profile

Now, when you want to access MySQL via Terminal, all you have to type is:

mysql -u root -p.

Accessing MySQL from outside Terminal

Accessing MySQL from the Terminal directly is not exactly the most ideal way to interact with your databases. If you're writing web applications, you'll need to access MySQL via a scripting language, such as Rails or PHP.

Just use the appropriate values when connecting to MySQL via scripts:

  • Host: localhost
  • Username: root
  • Password: your_password_here
  • Database: your_database_here

Socket error

Sometimes this error will prevent you from connecting to MySQL:

Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2)

I've noticed two reasons this error could show up:

  1. Your scripting language (PHP, for example) can't find the location of mysql.sock.
  2. MySQL is not running.

For #1, there is a small fix that can correct this. Type the following commands into Terminal:

cd /var
sudo mkdir mysql
cd mysql
sudo ln -s /private/tmp/mysql.sock mysql.sock

These commands assume that the directory /var/mysql/ does not yet exist (it will create it using these commands).

It will then move the sock file to a spot where a scripting language (specifically PHP) looks for it.

Restart MySQL server after reboot or crash

For #2, this is a simple fix. Often MySQL will not be running after OS X crashes, and is rebooted, for example.

To start up the MySQL server again, type these commands into Terminal (same as further up when initially setting things up):

cd /usr/local/mysql
sudo echo
sudo ./bin/mysqld_safe &
/usr/local/mysql/bin/mysql test

This starts the MySQL server again, and logs you in to the test database.

Final things

If everything is working right, you may also want to install a front-end app like phpMyAdmin for easier database administration.

About the author(s)

Matt Thommes is an independent publishing enthusiast, mobile blogger, content creator, informative writer, web developer from a suburb of Chicago. Never one to conform, Matt intends to promote the effect the web has on our lives, in an effort to intensify, instruct, and clarify all that is happening around us.

Comments

Note: Comments may be viewed by authors, but if you have a more specific question you'd like to ask them, please email matt.thommes@paininthetech.com.

# sriram venkit at 6/21/2009 6:42 pm cst

Hey Matt, great post. For some reason the short cut does not work for me. I am on Leopard.

Quick Link to this comment: http://TTIP.me/c5182

# Matt at 6/22/2009 4:58 pm cst

Hmm... maybe I had the syntax wrong or something. Let me know if you still can't get it to work.

Quick Link to this comment: http://TTIP.me/c5185

# sriram venkit at 6/22/2009 5:55 pm cst

MyMacBook:~ macky$ echo 'setenv PATH /usr/local/mysql/bin:$PATH' >> ~/.tcshrc MyMacBook:~ macky$ mysql -u root -p -bash: mysql: command not found MyMacBook:~ macky$

That is what I did. And I don't know why it doesn't work. I'm a total noob.

Quick Link to this comment: http://TTIP.me/c5186

# Matt at 6/22/2009 6:15 pm cst

Was MySQL installed successfully for you?

Quick Link to this comment: http://TTIP.me/c5187

# sriram venkit at 6/22/2009 6:51 pm cst

Yes, I was able to login, create database, tables. (thanks Matt)

Quick Link to this comment: http://TTIP.me/c5188

# Matt at 6/23/2009 3:42 pm cst

Woops, I think I had the wrong command in there. It should be:

echo 'export PATH=/usr/local/mysql/bin:$PATH' >> ~/.bash_profile

Quick Link to this comment: http://TTIP.me/c5189

# sriram venkit at 6/23/2009 4:23 pm cst

Matt thanks a lot for helping me out here. Unfortunately this is what I got this time

MyMacBook:~ macky$ echo 'export PATH=/usr/local/mysql/bin.$PATH' >> ~/.bash_profile MyMacBook:~ macky$ mysql -u root -p -bash: mysql: command not found MyMacBook:~ macky$

Quick Link to this comment: http://TTIP.me/c5190

# Matt at 6/23/2009 6:07 pm cst

It's saying "mysql: command not found"? I think that means you are not in the right directory.

In Terminal, you can try going to the mysql install folder before issuing any other commands:

cd /usr/local/mysql

Then issue the shortcut command, and see if that gives you better results.

Let me know.

Quick Link to this comment: http://TTIP.me/c5191

# sriram venkit at 6/23/2009 7:53 pm cst

It worked! yay! Thank you so much for your patience.

Quick Link to this comment: http://TTIP.me/c5192

# Paul at 6/28/2009 9:38 pm cst

thanks for the article, the documentation on sun's mysql site is horrible. you may want to inform people to run the command:

source ~/.bash_profile

in order to reload their profile.

Question, why does the MySql documentation say that it needs a mysql user on OS X and that 10.2+ should automatically have one. I have found neither to be true unless I am not supposed to see the mysql user under system preferences --> accounts?

Quick Link to this comment: http://TTIP.me/c5200

# eloone at 7/15/2009 7:22 pm cst

Thank you so much, I've been wandering over the internet looking for a great and simple install tutorial like yours but could only find crappy tutorials from...2000 or so and nothing worked on my machine it was overly complicated, but your post is really very helpful, THANKS !!

Quick Link to this comment: http://TTIP.me/c5223

# Sharon at 7/21/2009 2:50 pm cst

Matt - this is a really great article. I'm having the exact same issue that Sriram was. I tried your trick of using cd /usr/local/mysql - and I had no luck.

I tried backing up to ~ and / and starting from there, and still no luck. This is the return I receive from the terminal:

[MyLaptop:/usr/local/mysql] snow% echo 'export PATH=/usr/local/mysql/bin:$PATH'>> ~/.bash_profile [MyLaptop:/usr/local/mysql] snow% mysql -u root -p mysql: Command not found.

Quick Link to this comment: http://TTIP.me/c5229

# Matt at 7/21/2009 3:06 pm cst

Hi Sharon, ... well.. "Command not found" typically tells us that it can't find the mysql executable file.. and usually means we're not in the right directory when trying to call mysql.

Are you able to log into mysql using the long form? IE: /usr/local/mysql/bin/mysql -u root -p

If that works, we know mysql is in the right directory, it's just a matter of figuring out the shortcut statement.

Quick Link to this comment: http://TTIP.me/c5230

# Sharon at 7/21/2009 3:11 pm cst

yep - I followed your instructions and have no problems whatsoever logging into the mysql. I knot this is going to squish this up - but here it is:

[MyLaptop:/usr/local/mysql] snow% /usr/local/mysql/bin/mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 13 Server version: 5.4.1-beta MySQL Community Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql>

I'm running OS X 10.5.7 and I installed the beta version of mysql 5.4.1

Quick Link to this comment: http://TTIP.me/c5231

# koss at 7/27/2009 5:58 pm cst

SHARON, I had the same problem as you! I did all the steps from this tutorial but i didnt have luck to get it work!

BUT, the useful hint was already given by the nice guy Paul on 6/28 !

All you have to do is to reload your bash profile by

source ~/.bash_profile

You've done it right, I' pretty sure, but I think the tutorial should also include this useful line for newabies like me :))))

Thanks guys for the great teamwork!

Quick Link to this comment: http://TTIP.me/c5239

# Janaki at 7/31/2009 12:09 pm cst

All was going well until I try to use the test. I get this error: -bash: /usr/local/mysql/bin/mysql: cannot execute binary file

Here's what I entered after my password: Cristn-Janaki-Rathods-Computer:/usr/local/mysql cristinjanakirathod$ echo 'export PATH=/usr/local/mysql/bin:$PATH' >> ~/.bash_profile Cristn-Janaki-Rathods-Computer:/usr/local/mysql cristinjanakirathod$ /usr/local/mysql/bin/mysql test -bash: /usr/local/mysql/bin/mysql: cannot execute binary file Cristn-Janaki-Rathods-Computer:/usr/local/mysql cristinjanakirathod$ source ~/.bash_profile Cristn-Janaki-Rathods-Computer:/usr/local/mysql cristinjanakirathod$ /usr/local/mysql/bin/mysql test -bash: /usr/local/mysql/bin/mysql: cannot execute binary file

What am I missing?

Thanks so much for this! It certainly is a departure from the usual easy use of Macs!

Quick Link to this comment: http://TTIP.me/c5245

# Matt at 7/31/2009 2:18 pm cst

What happens when you type in:

/usr/local/mysql/bin/mysql -u root -p

This command should try to log you in to MySQL as well.

Quick Link to this comment: http://TTIP.me/c5246

# Janaki at 7/31/2009 2:59 pm cst

This is the result:

Cristn-Janaki-Rathods-Computer:/usr/local/mysql cristinjanakirathod$ /usr/local/mysql/bin/mysql -u root -p -bash: /usr/local/mysql/bin/mysql: cannot execute binary file Cristn-Janaki-Rathods-Computer:/usr/local/mysql cristinjanakirathod$

Any ides Matt? I am OS 10.4.11 on an Intel using mysql-5.1.36-osx10.4-powerpc-64bit.dmg

It all seems to be installed correctly.

Thanks for your time! I am just learning MySQL and need to get to the MySQL Monitor to do my homework.

Quick Link to this comment: http://TTIP.me/c5247

# Matt at 7/31/2009 3:05 pm cst

You downloaded the package format for "Mac OS X 10.4, (PowerPC, 64-bit)", right? Cuz my screenshot above shows a different one to choose. Just making sure :-)

Quick Link to this comment: http://TTIP.me/c5248

# Janaki at 7/31/2009 3:19 pm cst

Yes, 10.4 64 bit, except the one your show is 5.1.35 and I downloaded 5.1.36. Would that be the problem?

Quick Link to this comment: http://TTIP.me/c5249

# Janaki at 7/31/2009 4:00 pm cst

The files in this directory are all unix executable files:

/usr/local/mysql/bin/mysql

Quick Link to this comment: http://TTIP.me/c5250

# Josh at 8/23/2009 9:45 am cst

Great article -- helped me get my SQL up and running. Thanks!

Quick Link to this comment: http://TTIP.me/c5275

# tim at 8/28/2009 1:05 pm cst

Hi Matt, Thanks for the instructions, especially about the socket error! I also wrote up a short article after my experience installing and uninstalling mysql, check it out if you like: http://obscuredclarity.blogspot.com/2009/08/install-mysql-on-mac-os-x.html http://obscuredclarity.blogspot.com/2009/08/uninstall-mysql-on-mac-os-x.html

Quick Link to this comment: http://TTIP.me/c5283

# Matt at 8/28/2009 6:02 pm cst

Great post Tim! Especially important is a step I forgot - installing the MySQL startup item. Since I forgot to do this, I now manually start up MySQL after a reboot.

Quick Link to this comment: http://TTIP.me/c5284

# Billy at 10/25/2009 2:40 pm cst

Here is an article that shows you how to develop php/mysql sites on a mac: http://totalnoob.wordpress.com/2009/10/25/beginners-guide-to-developing-php-and-mysql-sites-on-a-mac/

Quick Link to this comment: http://TTIP.me/c5386

# Jack Kinsella at 1/11/2010 5:16 pm cst

Wow thanks! SUCH a life saver.

Anyone not getting the shortcut to work echo 'export PATH=/usr/local/mysql/bin:$PATH' >> ~/.bash_profile should try restarting terminal - it worked for me.

Quick Link to this comment: http://TTIP.me/c5488

# heather at 1/31/2010 8:36 pm cst

thank you thank you thank you! you made this very easy. I started with a different tutorial but got stuck. Yours was very helpful in picking up where I left off!

Quick Link to this comment: http://TTIP.me/c5522

# DJ at 2/22/2010 6:30 pm cst

Hello....I followed the steps in this posting and everything worked perfect for me. After a few days of learning PHP/MySQL, I turned off my computer. When I turned it back on and returned to my PHP/MySQL studies I was shocked when I received '/var/mysql/mysql.sock' (61) from one of my PHP scripts that had been working fine. I followed both steps mentioned above for fixing this error but no such luck. Please help. I was having such a good time learning this stuff. PS - I had this problem when I first installed MySQL and the above steps worked like a charm. Why is it doing this again and why are the steps not working? Thanks

Quick Link to this comment: http://TTIP.me/c5548

# Matt at 2/22/2010 6:43 pm cst
Hi DJ - please make sure MySQL is running, and if that doesn't fix it, see the sub-section above titled "Socket error." Let us know if we can help with anything.

Quick Link to this comment: http://TTIP.me/c5549

# DJ at 2/22/2010 7:05 pm cst

Hi Matt - MySQL appears to be running. Im able to log in and create databases, tables etc... I followed the "Socket Error" steps above and I'm still getting the same error. Thanks for the post, by the way. It took me forever to find a good post with easy steps for installing MySQL.

Quick Link to this comment: http://TTIP.me/c5550

# Matt at 2/24/2010 3:10 pm cst

DJ, if it's not one of those two things mentioned above, I'm at a loss for helping. Maybe someone else knows. I've only ever needed to use one of those two suggestions to resolve the "socket" error - most cases it's MySQL not running, after I reboot my Mac. Hopefully you find an answer somewhere.

Quick Link to this comment: http://TTIP.me/c5552

# DJ at 2/24/2010 5:34 pm cst

Thanks Matt. Ill keep searching and trying. If I figure it out, Ill post my findings.

Quick Link to this comment: http://TTIP.me/c5553