Installing MYSQL on OSX Lion Server

November 28th, 2011

It is a fact that Apple has migrated itself away from MYSQL. It is also a fact that most people who continue to buy Apple Servers have been using MYSQL for some time and have websites or other content that sill relies on this technology. Just because it is not endorsed or pre-configured by Apple however does not mean that it can not be used. On the contrary installing and configuring MYSQL to run on an OSX Lion server is moderately easy and gives greater insight as to how MYSQL works (If your a novice to intermediate MYSQL user like me). Lets get started with a brief walkthrough of how to install MYSQL on an OSX Lion Server.

Installation & Configuration

1. Download and install the 64-bit 10.6+ version of MYSQL installer package together with the startup files here.

http://dev.mysql.com/downloads/mysql/

2. Mount the Disk Image (I mean open/double-click the DMG file) and install MySQL server by double-clicking the PKG file (in my case mysql-5.5.14-osx10.6-x86_64.pkg) and follow onscreen instructions. ( It will ask for Master password, as it installs MySQL server in /usr/local )

Current latest version is 5.5.14 which I’ll be using to install on my server.

Open the DMG and you will see that the first item is the MySQL software, the 2nd item allows MySQL to start when the Mac is booted and the third is a System Preference that allows start/stop operation and a preference to enable it to start on boot. Run all of these.

Once the installs are done you can start the mysql server right from the System Preferences which has a new preference in the “Other” category called “MySQL” click start and now it is running.

To find the MySQL version from the terminal, type at the prompt

/usr/local/mysql/bin/mysql -v

If you got the error: ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’

then mysql was not started, go back to the System Preference and start the database.

3. Run the following commands

cd /usr/local/mysql
cp /usr/local/mysql/support-files/my-small.cnf /private/etc/my.cnf
open -e /private/etc/my.cnf

replace “/tmp/mysql.sock” with “/var/mysql/mysql.sock” at two places near the top.
Create a folder called “mysql” (if you don’t already have one) in the /var directory with the right permissions:

cd /var
mkdir mysql
sudo chown -R mysql mysql 
sudo chmod 775 mysql

This command will circumvent the dreaded mysql 2002 socket error.

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

4. Create your alias, this is important so that you can run MYSQL queries through the terminal.

alias mysql /usr/local/mysql/bin/mysql
alias mysqladmin /usr/local/mysql/bin/mysqladmin

optionally you can edit the ~/.profile file to make your aliases (This should be done as root)

pico ~/.profile

then add this line below

export PATH=/usr/local/mysql/bin:$PATH

*Please note /usr/local/mysql is only symlink to /usr/local/mysql-5.5.14-osx10.6-x86_64 which means when you upgrade to new version symlink will be changed to point to new version but won’t be deleting the older version. However you need to copy your data directory to new location to make sure your existing databases are intact post upgrade.

5. Set the master MYSQL password, there are 2 ways to do this one is a regular way and the other provides additional security and disables all other access

Regular Way

mysqladmin -u root password 'yourpasswordhere'

** use the single quotes. Then when login to mysql to test your password

mysql -u root -pyourpasswordhere

Secure Way

sudo mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):

Go ahead and just hit enter if this is a new installation and no password currently exists, follow the prompts to set up a new root password – this is a root password just for mysql separate from the root password of OS X you should set this.

You also get asked about:

  • Removing anonymous users?
  • Disallow root login remotely?
  • Remove test database and access to it?
  • Reload privilege tables now?
  • If this is a new installation you can just answer yes to the questions.

Once the root user and password is set, you have to interact with mysql with the username and password, so access via command line is (note that there is no space between -p and the password)

mysql -u root -p[password]

Now that you have MYSQL running you need to start an instance or a main profile for MYSQL to run. I have found the easiest way to do this is to install PHPMYADMIN and since most people in my opinion (Again novice to intermediate MYSQL user here) use this great tool to navigate around MYSQL on a daily basis. Here is a brief walkthrough on how to install and configure PHPMYADMIN on 10.7 Lion Server

Installation & Configuration

1. Change the socket location in your PHP configuration by editing the php.ini file. You need to do a search and replace here. Search and replace all instances of

/var/mysql/mysql.sock

with

/tmp/mysql.sock

Once done you should be able to run the following command and it should reflect the new updated values you just applied.

grep .default_socket /etc/php.ini

while editing the php.ini file you need to comment out or enable the following extensions.

extension=php_mysql.dll
extension=php_mysqli.dll

To check your work again you can run this command to ensure they are enabled.

grep mysql /etc/php.ini|grep ext

Once done restart Apache

sudo apachectl restart

2. Download PHPMYADMIN to the default web directory in Lion

http://www.phpmyadmin.net/home_page/index.php

The full path is

/Library/Server/Web/Data/Sites/Default

I put my PHPMYADMIN in a folder called PHP so

/Library/Server/Web/Data/Sites/Default/PHP

and I could then browse to it by going to

http://server.domain.name/PHP/

this is assuming that you have already configured or turned on web services which I will not go into here since it is a very basic step. I will write a more in depth article and how to on the complexities of running an 10.7 web server in the future however.

Run this command on the PHP Config folder

chmod o+w /Library/Server/Web/Data/Sites/Default/PHP/config

3. Now we are ready to run the set up by going to

http://localhost/PHP/setup

The new server to be configured is the localhost, click new server and then the only other configurations are the local mysql user and the password.

Add in the username, by default “root” is assumed, add in the password, click on save and you are returned to the previous screen.

Make sure you click on save, then a config.inc.php is now in the /config directory, move this file to the root level of /phpmyadmin and then remove the empty /config directory.

Now going to http://localhost/PHP/ will now allow you to interact with your mysql databases.

I hope that you all found this article and walkthrough educational, as always please feel free to interact with me by posting questions and comments and I will answer them as best as I can. If you feel like any of this is wrong or could be improved upon also please leave a comment below, thanks!


Do you need system administration assistance? Find me over at LinkedIn and friend me! I give help for a small monetary donation and good feedback. If you like what you are reading please consider subscribing to the RSS feed for comments on this post. If you have feedback you can leave a response, or trackback from your own site.

Comments


Comment by Will A.

This is a very complete walkthrough, thanks!

Reply

Comment by Bo Pickard

Thanks for this guide. After upgrading to Lion, my test server went to hell with the 2002 error. Even though I’ve had to deal with this before, I never quite get what’s going wrong or how to fix it.

Thanks, Bo

Reply

Comment by daniel

thank you!

Reply

Comment by max

I tried this walkthrough but must have gone wrong somewhere, how do I return to the defaults so I can try again, thanks, m

Reply

By Jon Brown

Before doing any server modifications you should always have a full system backup you can restore to. You should be able to start from the beginning and all the steps will overwrite your previous actions if you feel like you missed a step.

Reply

Comment by Brian

I believe that one of your steps is backwards. At least it worked when i did the opposite of what you said.

“1. Change the socket location in your PHP configuration by editing the php.ini file. You need to do a search and replace here. Search and replace all instances of

/var/mysql/mysql.sock

with

/tmp/mysql.sock

Once done you should be able to run the following command and it should reflect the new updated values you just applied.”

I left my php.ini with /var/mysql/mysql.sock and it works perfect. Thanks for the tutorial!

Reply

Comment by Brian

Another trick to avoid needing root.. if you are having trouble editing “locked” files, instead of opening them in textedit, open them in nano.
lion users will most likely need to do this.

sudo nano /etc/my.cnf

Reply

By Jon Brown

I prefer sudo pico /etc/my.cnf but yes I almost never use text edit or login as root unless the server itself is behind a hardware firewall.

Reply

Comment by Michael Mahoney

This is great, and I have been successful in configuring with this article, but when I’m finished, the command ‘which mysql’ still reports ‘/usr/bin/mysql’. Why would this be?

Also, I think you may have a couple of errors above. The alias syntax is, I think, ‘alias=”path”‘, right? Also, I think you mean to reverse the order in the ‘ln -s’ command.

Reply

By Jon Brown

The only thing that I can think of is that you need to create the alias to mysql. If you have done this then it does not really matter what you get as the result to the “Which” command as long as the server and all of its technologies (Apache, PHP etc…) are in fact pointing to the correct instance or for Lion the only instance of MYSQL.

4. Create your alias, this is important so that you can run MYSQL queries through the terminal.

alias mysql /usr/local/mysql/bin/mysql
alias mysqladmin /usr/local/mysql/bin/mysqladmin

optionally you can edit the ~/.profile file to make your aliases (This should be done as root)

pico ~/.profile

then add this line below

export PATH=/usr/local/mysql/bin:$PATH

*Please note /usr/local/mysql is only symlink to /usr/local/mysql-5.5.14-osx10.6-x86_64 which means when you upgrade to new version symlink will be changed to point to new version but won’t be deleting the older version. However you need to copy your data directory to new location to make sure your existing databases are intact post upgrade.

To answer your question the syntax for Lion Server for creating a MYSQL alias is

alias

so it is alias mysql /usr/local/mysql/bin/mysql if this does not work then you should use the secondary method of adding the path to the .profile file. Note these instructions only work on 10.7 server.

Reply

Trackback by Prvi bosansko-hercegovački forum za web dizajn i web razvoj...

[...]Installing MYSQL on 10.7 Server | Musings of an Apple Systems Administrator[...]…

Comment by I do wrong ? Mric

Hello,
I don’t understand what I do wrong, when I want to change root password, mysql won’t to connect : error message is : mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘root’@’localhost’ (using password: NO)’

Reply

By Jon Brown

Did you try to set the master password by running the command below? That might help.

sudo mysql_secure_installation

Reply

Comment by Art Busbey

Real noobie here. I started to follow your procedure for installing and running MYSQL on Lion Server. After installing when I am in Step 3 and try to copy the .cnf file I am told Permission Denied when try CP my-small.cnf to my.cnf. I am the only administrator on this server yet am told Permission Denied. Any hints on what I might do to solve this short of logging in as root?

Reply

By Jon Brown

The entire walkthrough above is being done as root. However if you don’t want to login as root through the GUI, then you need to run

sudo -s

then authenticate, then when you are at a true bash script prompt you can proceed.

Reply

By Art Busbey

Many thanks!

Reply

Comment by Art Busbey

Successfully root in bash, but interestingly when I open -e /private/etc/my.cnf and textedit launches (set to work in plaintext) I am not allowed to unlock the my.cnf file to save the edited version. Sorry for my inexperience here but any suggestions?

Reply

By Jon Brown

you can use

sudo pico /etc/my.cnf

to edit the file through terminal or

sudo nano /etc/my.cnf

in order to use TextEdit you must login through the main login window as root then you can edit files as root using GUI tools such as TextEdit.

Reply

Comment by Art Busbey

One more question, I hope. I decided to install as root since it would seem to cause the fewest problems. So as root I have followed the sequence on two different machines now. However, I keep getting stopped at the alias command. When I issue alias mysql /usr/local/mysql/bin/mysql or
alias mysqladmin /usr/local/mysql/bin/mysqladmin the shell comes back and says that neither of the files can be found. So,
alias mysql /usr/local/mysql/bin/mysql
results in the following message:

alias mysql=’/usr/local/mysql/bin/mysql’
-bash: alias: /usr/local/mysql/bin/mysql: not found

yet mysql is there in the path just as it is supposed to be.

I must be doing something stupid wrong.. any suggestions?

Reply

By Jon Brown

Try this In a terminal type: pico ~/.profile

then paste the line below:
export PATH=/usr/local/mysql/bin:$PATH

and save. Restart your terminal (close and open it) and do a: echo $PATH and it should not list the mysql/bin folder let me know if this works.

Reply

Comment by Art Busbey

Any suggestions about what to do if mysql won’t start from the prefpane? I have looked in the logs and can’t find any crash entries or other information about the process. It just won’t start.

Reply

By Jon Brown

I believe you must have the alias working correctly to get it to startup, but you can always kickstart it via the command line by running

/usr/local/mysql/bin/mysqld_safe &

in the terminal.

Reply

Trackback by My iBlog

My iBlog…

[...]Installing MYSQL on 10.7 Server | Musings of an Apple Systems Administrator[...]…

Comment by ralph

I am new to this is there an easier way to install because I have tried your step in terminal and I am not getting any response.

Reply

By Jon Brown

The easier way is actually not technically easier but you could erase your server HD. Install Mac OSX Snow Leopard Server then upgrade to Lion Server which would keep MYSQL installed.

Reply

Comment by Ralph

I finally got everything installed, thank you. I am developing my site and using software that needs MySQL, the software install can not find MySQL. Any help would be great.l

Reply

By Jon Brown

Im a little at a loss one of the steps was to create an alias for MYSQL which allows your PHP scripts to locate it, you may want to look at the path to MYSQL in the PHP.INI file, but I am not 100% sure. Could you provide some more information?

Reply

i don’t have this problem yet, but it’s the one part of the tut that did not work: creating an alias. i guess this is what you need it for :)

Reply

alias mysql /usr/local/mysql/bin/mysql
$ alias mysql /usr/local/mysql/bin/mysql
-bash: alias: mysql: not found
-bash: alias: /usr/local/mysql/bin/mysql: not found" line="1"

Reply

By Jon Brown

There is some really good information here http://superuser.com/questions/441288/path-issues-with-osx-lion that may help. You could try to unalias your mysql installation and then re-alias it. You can also try this modified command it may help alias mysql=/usr/local/mysql/bin/mysql I wrote this tutorial for 10.7.0 and MYSQL version mysql-5.5.14-osx10.6-x86_64 are you using a different version of the OS or MYSQL?

*Please note /usr/local/mysql is only symlink to /usr/local/mysql-5.5.14-osx10.6-x86_64 which means when you upgrade to new version symlink will be changed to point to new version but won’t be deleting the older version. However you need to copy your data directory to new location to make sure your existing databases are intact post upgrade.

Reply

Comment by jiggy gaton

Really great tutorial, thx! sure wish you wrote even more, like how to fix up Lion once PHP is installed, such as found here:
http://www.glenscott.co.uk/blog/2011/08/29/install-mcrypt-php-extension-on-mac-os-x-lion/ peeps are gunna need this as well!

Reply