Install and configure MySQL 5 with MacPorts

7/18/09 - Having trouble starting MySQL on system startup? Rob Wilkerson shows how he gets around the issue.

4/2/08 - UPDATED - Replaced symlink to MySQL socket with proper MySQL configuration file settings. Thanks Doug!

10/20/07 - UPDATED - Added full path to aliases, thanks Joel!

10/7/07 - UPDATED - Clarified +server option and executable names, thanks Thom!

I decided to move my MySQL server installation from the server I installed from MySQL.com to macports. Why? I believe MacPorts will simplify future upgrades. I'd also like to keep my LAMP installs in the same location. If you haven't already, install and configure MacPorts. Here are the steps I'll go over:

  • Intall MySQL 5
  • Create the initial MySQL databases
  • Options for starting MySQL
  • Confirm that MySQL is running
  • Set Basic MySQL Security

Install MySQL 5

You can install using Porticus, Port Authority, or from the command line:

sudo port install mysql5 +server

The +server variant is required if you'll need to create a system startup item for MySQL 5. You'll see plenty of output during the build process and an activation message just before the installation completes.

Create initial MySQL databases

MySQL databases are kept in /opt/local/var/db/mysql5/ and you'll notice that this directory is empty. Run mysql_install_db to create the mysql and test databases.

sudo /opt/local/lib/mysql5/bin/mysql_install_db --user=mysql
Password:

You should see output similar to the following:

Installing MySQL system tables...
070925 20:32:07 [Warning] Setting lower_case_table_names=2 because file system for /opt/local/var/db/mysql5/ is case insensitive OK
Filling help tables...
070925 20:32:11 [Warning] Setting lower_case_table_names=2 because file system for /opt/local/var/db/mysql5/ is case insensitive OK  To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands:
/opt/local/lib/mysql5/bin/mysqladmin -u root password 'new-password'
/opt/local/lib/mysql5/bin/mysqladmin -u root -h homebase.local password 'new-password'
See the manual for more instructions. You can start the MySQL daemon with:
cd /opt/local ; /opt/local/lib/mysql5/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl 
cd mysql-test ; perl mysql-test-run.pl  
Please report any problems with the /opt/local/lib/mysql5/bin/mysqlbug script!
The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com

Options for starting MySQL

I personally don't want MySQL to start at system start up, I'll start it manually when I need it. If you're energy conscious and set your Mac to go to sleep you won't want to set mysqld to start at system boot either. If, however, you need MySQL to always start at system boot

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

Whether you install the startup item or not, it's handy to add aliases to your profile to start and stop mysql from the command line. Use your favorite text editor (vi, pico, emacs) to add aliases.

vi ~/.profile

or

vi ~/.bash_profile

Add the following:

alias mysqlstart='sudo /opt/local/bin/mysqld_safe5 &'
alias mysqlstop='/opt/local/bin/mysqladmin5 -u root -p shutdown'

Note: Notice the '5' at the end of mysqld_safe? MacPorts adds version numbers at the end of executables to allow the installation of multiple versions (i.e. MySQL 4 and 5). You'll need to add the version number at the end of some of the executables you use, like mysqladmin5. Now you're ready to start your MySQL server with:

mysqlstart

or, if you didn't create aliases

sudo /opt/local/share/mysql5/mysql/mysql.server start

Confirm that MySQL is running

If MySQL's okay, you should see a server start confirmation message. You can test to ensure that MySQL is running with (leave password empty when prompted):

mysqladmin5 -u root -p ping 
Enter password: 
mysqld is alive

or with

mysql5 -u root -p
Enter password: 
Welcome to the MySQL monitor. 
Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.45  Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

If you get the following error...

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

...set the mysqld_safe socket path in a new MySQL configuration file. Create /opt/local/etc/mysql5/my.cnf, add the following to it, save, the try again.

[mysqld_safe] socket = /tmp/mysql.sock

NOTE: If you've installed MacPort's PHP 5 you will probably need to tell PHP where the MySQL socket is. Edit your php.ini file located at /opt/local/etc/php.ini.

mysql.default_socket = /tmp/mysql.sock

Set Basic MySQL Security

Finally, set the root mysql password to secure your server.

mysqladmin5 -u root password

Moving MySQL data from other installations

If, like me, you want to move data from a previous MySQL 5 install (i.e. /usr/local/mysql/data), be sure to stop MySQL first. You'll want to overwrite the mysql database you created after installation when copying over data files and indices. Ensure that everything copied to /opt/local/var/db/mysql5/ remains owned by mysql. Refer to the MySQL documentation if you're upgrading from MySQL 4 to 5.

Now get busy querying.

Comments

mysql_install_db problem workaround

On snow leopard I was having trouble getting this command to work:


sudo /opt/local/lib/mysql5/bin/mysql_install_db --user=mysql

Eventually, I tried this instead and it worked perfectly:

sudo su
/opt/local/lib/mysql5/bin/mysql_install_db

Resolving permissions issue with pid file

The Macports installation failed for me because it didn't set up the right directory structure for the runtime and log files. I would get errors regarding the pid file. Resolving this is pretty easy:

1. Create the runtime and log directories: sudo mkdir /opt/local/var/log; sudo mkdir /opt/local/var/log/mysql5; sudo mkdir /opt/local/var/run; sudo mkdir /opt/local/var/run/mysql5

2. Set ownership for the new mysql5 directories to the _mysql user: sudo chown _mysql /opt/local/var/log/mysql5; sudo chown _mysql /opt/local/var/run/mysql5

socket errors

Thanks for the guide! I kept getting mysql socket errors, but did it this way and it worked:
http://hacksushi.com/2009/12/03/installing-mysql-on-mac-os-x-snow-leopard/

Trouble installing Drupal

I got some problems when I tried installing Drupal. The problem was that Drupal looked for the connection to mysql through a file called mysqld.sock instead of af file file called mysql.sock. I solved this issue by creating symlink with the name that Drupal was looking for. Now what I thought was strange is that I could install both Joomla and Wordpress with no hassle, so the connection has to differ for them in some way.

Thanks for the walkthrough!!

Issues

Well, mysqld starts up no problem as I can clearly see mysqld when I run top. However, whenever I try to run the mysql command (with or without any flags, like "-u root -p") I always get this error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

I've made the my.conf file, I've fixed my php.ini files (the MacPorts installation of PHP5 had two php.ini files. I modified both and made a copy of the production version as php.ini in /opt/local/etc).

Sad thing is, before getting MacPorts and more or less re-installing MySQL, Apache2, and PHP5, I had this stuff working fine.

Do I need to uninstall all my original installations from the time before I started using MacPorts? Anyone have any advice on making such an endeavor any easier?

Problem to get mySQL running

i installed the package php5 +apache2+fastcgi+macosx+mysql5+pear+sqlite with macport.

but i don't get mySQL running. i get following error messages:
$ /opt/local/share/mysql5/mysql/mysql.server start
Starting MySQL
. ERROR! Manager of pid-file quit without updating file.

and

/opt/local/var/db/mysql5/localhost.err
090703 19:57:23 mysqld started
090703 19:57:25 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090703 19:57:25 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090703 19:57:25 [ERROR] /opt/local/libexec/mysqld: unknown variable 'table_open_cache=4'
090703 19:57:26 mysqld ended

and

/opt/local/var/db/mysql5/Diogenes.local.err
090703 19:54:03 mysqld started
090703 19:54:03 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090703 19:54:03 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090703 19:54:03 [ERROR] /opt/local/libexec/mysqld: unknown variable 'table_open_cache=4'
090703 19:54:03 mysqld ended

i've the same problem with the regular installation "mysql-5.1.35-osx10.5-x86_64.dmg" from the mySQL-site and MAMP.

can one help me?

lixx

Problem to get mySQL running

i installed the package php5 +apache2+fastcgi+macosx+mysql5+pear+sqlite with macport.

but i don't get mySQL running. i get following error messages:
$ /opt/local/share/mysql5/mysql/mysql.server start
Starting MySQL
. ERROR! Manager of pid-file quit without updating file.

and

/opt/local/var/db/mysql5/localhost.err
090703 19:57:23 mysqld started
090703 19:57:25 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090703 19:57:25 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090703 19:57:25 [ERROR] /opt/local/libexec/mysqld: unknown variable 'table_open_cache=4'
090703 19:57:26 mysqld ended

and

/opt/local/var/db/mysql5/Diogenes.local.err
090703 19:54:03 mysqld started
090703 19:54:03 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090703 19:54:03 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090703 19:54:03 [ERROR] /opt/local/libexec/mysqld: unknown variable 'table_open_cache=4'
090703 19:54:03 mysqld ended

i've the same problem with the regular installation "mysql-5.1.35-osx10.5-x86_64.dmg" from the mySQL-site and MAMP.

can one help me?

lixx

Server won't stay running

As background, I didn't install this via macports, but all of the folders/files this walkthrough has referred to have been present and seem to work, up to a point. I'm running this on OS 10.5.

I'm having trouble starting the server. When I try to start it by calling "sudo /opt/local/share/mysql5/mysql/mysql.server start", I get the following error:

Starting MySQL
.. ERROR! Manager of pid-file quit without updating file.

When I try to start the server by calling "sudo /opt/local/lib/mysql5/bin/mysqld_safe5 &", i get this error:

Starting mysqld daemon with databases from /opt/local/var/db/mysql5
STOPPING server from pid file /opt/local/var/db/mysql5/derek-andersons-imac.local.pid
090415 17:51:49 mysqld ended

(I don't understand the difference between starting them those ways, but that doesn't matter too much).

I have added the lines to my.cnf:

[mysqld_safe]
socket = /tmp/mysql.sock

[mysqld]
socket = /tmp/mysql.sock

and this doesn't fix it. I have also ran "chown mysql /opt/local/var/run/mysql5" and "chown mysql /opt/local/var/run/mysql5" because I thought this might be the issue, but to no avail.

Any help would be greatly appreciated, as I've been pulling my hair out over this for well over 5 hours.

Thanks! - Josh

Re: Server won't stay running

I had that problem, too. I looked in /opt/local/var/db/mysql5/ and saw windhund.local.err (yours would be derek-andersons-imac.local.err) and it shed light on the problem.

In my case...
090416 13:12:49 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
090416 13:12:49 [ERROR] Do you already have another mysqld server running on port: 3306 ?
090416 13:12:49 [ERROR] Aborting

I had a previously installed mysql server running. I uinstalled and killed that. Then it was...

090416 13:15:35 [ERROR] Can't start server : Bind on unix socket: Permission denied
090416 13:15:35 [ERROR] Do you already have another mysqld server running on socket: /var/mysql/mysql.sock ?

Which was mysql's way of saying it doesn't have permission to write to /var/mysql. I deleted to old /var/mysql directory and let it create a new one. Then it started fine.

Looks like permissions

Thx Shwern, I think you're right. You could also try changing ownership to the mysql user:

sudo chown -R mysql /var/mysql

And fyi, the difference between the two startup commands is that the latter, the one ending with &, starts the MySQL service in the background, which is preferred.

my.cnf

...running port 1.7
after setting everything up i still could not connect to my running mysql server getting a "Can't connect to local MySQL server through socket '/opt/local/var/run/mysql5/mysqld.sock".

adding a [client] section to "/opt/local/etc/mysql5/my.cnf" helped - everything now works. my.conf now looks like this:

[mysqld_safe]
socket = /tmp/mysql.sock
[client]
socket = /tmp/mysql.sock

Thanks!

I also had to add this configuration setting in the my.cnf file - would think it worth adding to the main instructions (which are extremely helpful!).

Thanks for sharing your fix

Thanks for sharing your fix Rocco.

outdated command

Please update the instructions to use "macports" instead of "darwinports".

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

Updated

Updated, thanks.

that sock file

Cheers for the guide it has been very helpful, also thanks to those who suggested updates in the comments.

I had things working fine then updated the my.cnf file as per doug's suggestion, not wanted to have symlinks littering my environment.

my.cnf file:
[mysqld_safe]
socket = /tmp/mysql.sock

[mysqld]
socket = /tmp/mysql.sock

Following this i got errors when running, starting or stopping mysql. The error complained about a missing mysqld.sock file in /opt/local/var/run/mysql5.

eg
'/opt/local/var/run/mysql5/mysqld.sock' (2)'

The only way i found around this was to symlink from /tmp/mysql.sock to this missing location. Given that i set out to reduce symlinks, having to add one to get it working is both amusing and a little frustrating. I'm possibly missing a setting in the my.cnf file? Any suggestions would be great.

Quick Note

One thing I didn't see on here, while I was looking at all of this, is the importance of restarting your computer after you've got MySQL installed.

I have _no_ idea why, but MySQL simply will not start up under any conditions (seemingly, anyways), unless I restart my machine, then start MySQL.

Food for thought ...

I ran into a little issue

I ran into a little issue with this whereby building MySQL in MacPorts resulted in a 'broken pipe' error. To fix this, I did:

sudo port clean --work mysql5

Before then trying the 'sudo port install mysql5 +server' command.

this worked for me on Leopard, but also make sure you have used MacPorts to install:

  • Gawk
  • Nawk
  • TK
  • GMake

As apparently it can cause headaches if they are not previously installed.

Updating via Macports?

Is there anything special I need to do to update a Macports installed version of mysql other than run the port upgrade command?

Thanks, just what I needed!

Thanks, just what I needed!

Thank you

Thanks for the write-up, I have been wrestling with it for some time. I almost got it all installed the compiling way but GCC kept complaining. Even after I made the mandatory change to _mysql.c.

MySQL Socket Error

I've run into this error with a CakePHP installation:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

So I added:

[mysqld_safe]
socket = /tmp/mysql.sock

[mysqld]
socket = /tmp/mysql.sock

to my my.cnf file

I then ran into this error with a Zend Framework installation:

error: 'Can't connect to local MySQL server through socket '/opt/local/var/run/mysql5/mysqld.sock' (2)'

I updated the mysqli.default_socket setting in my php.ini file.

mysqli.default_socket = /tmp/mysql.sock

Now both errors are gone.

MySQL socket changes

Thanks for sharing this Seth, it'll definitely help others having similar issues.

mysql + sleep

You said:

"If you're energy conscious and set your Mac to go to sleep you won't want to set mysqld to start at system boot either."

I'm curious why you said that. Do you think the MySQL process doesn't work after the computer wakes from sleep? I've never had an issue with that. Can you clarify?

mysqld would keep machine from sleeping

When I first installed MySQL, probably the MySQL.com package, it would not allow the machine it was installed on to sleep. I should note that that doesn't seem to be the case anymore.

references to darwinports

Your instructions suggest doing this:

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

But the latest version of mysql uses "org.macports.mysql5.plist".

Haven't had a chance to update

@Ted Wood, thanks for pointing this out. I had written these instructions while installing on 10.4 over six months ago and much has changed. I plan on making updates at some point, but I've been a bit busy lately.

/tmp/mysql.sock

I've had nothing but headaches over this .sock file location. Even though I followed the instructions to the line, and started from scratch at least twice, I still can't get mysql5 to start up properly. It keeps complaining about the location of the .sock file. Why does the MacPorts installation default to a location other than /tmp/mysql.sock? Why can't it use that location, which seems to be the industry standard? Someone please help me figure this out.

Unknown database 'mysql'

The mysql_install_db failed for me:
----------------------------------
ERROR: 1049 Unknown database 'mysql'
080512 10:53:05 [ERROR] Aborting

080512 10:53:05 [Note] /opt/local/libexec/mysqld: Shutdown complete

Installation of system tables failed!
----------------------------------

Any clues on how to solve this?

Great Resource

This was not an easy install; thanks for the great resource. I'm pretty sure I'd have ditched MacPorts without a guide (just not worth the trouble). Nonetheless, I still ran into some trouble with the socket.

For whatever reason, my install expected the socket to exist as /opt/local/var/run/mysql5/mysqld.sock (not /tmp/myslq.sock). The only way I could get it to work was to create the socket it expected.


$ touch /opt/local/var/run/mysql5/mysqld.sock

Then, once I deleted the contents of my my.cnf file, I was able to connect.

Thanks again.

THANK YOU SO MUCH!

... for sharing. after 1 day of troubleshooting and -un, re- and installing I managed to complete my installation applying your workaround.

Thanks!

Had the same MySQL socket issue

I'm pretty sure I had the same problem installing MySQL 5 on a new machine. Thanks for sharing this Rob, I'm sure it'll help out others.

creating the proper mysql.sock

There's a lot of buzz about the problem with /tmp/mysql.sock and fixing it with the symlink. I'm sorry, but that's a sysadmin hack when you're not sure what's going on. The proper way to fix this is to add this to your /opt/local/etc/mysql5/my.cnf:


[mysqld_safe]
socket = /tmp/mysql.sock

[mysqld]
socket = /tmp/mysql.sock

Speaking of hack, I'm not sure if both of these are needed or not. But at least this is configuring mysql to do what you want rather than bending the system to do what mysql should have done.

I've done this and still getting problems

I've added what you said to add to the my.cnf file and I'm still getting this error:

noel-churchills-imac:etc noelchurchill$ mysql5 -u root -p ping
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/opt/local/var/run/mysql5/mysqld.sock' (2)

Any idea why? This post has been super helpful so far!

@Noel Had your exact same

@Noel

Had your exact same problem - i.e. kept getting:

"ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/opt/local/var/run/mysql5/mysqld.sock' (2)"

What worked for me was to add this as well to my.cnf:

[client]
socket = /tmp/mysql.sock

-Svenni

p.s.
(
if this does not work check out (i did some of these before adding to my.cnf..) :
http://mdzyuba.blogspot.com/2007/11/mysql-macport-install-on-mac-os-x-ti...
)

You're absolutely right...

I've updated my installation and this post accordingly.

This solved my problem

Took me some time to solve my problem only to find that the solution was in the comments. You really should update the how-to chad. Thanks for a nice how to!

Problems starting mysql, solution

When starting mysql, my server would start and then immediately stop. The problem was that the macports mysql config was set up to start mysql as a specific user.

I edited the cnf file /opt/local/etc/mysql5/my.cnf and changed the user to root. Then I ran sudo mysql_install_db5 and was then able to start the server. You could also just create the user that my.cnf is expecting.

Thanks for the helpful writeup Chad.

Thanks for sharing that tip

Thanks for sharing that tip Phil!

no socket?

I had the following error:
Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

I made the symbolic link to the typical mysql socket location, as it is written, but unfortunately, after restart the terminal, i have still the error, when i type:

$ mysqladmin5 -u root -p ping
Enter password:
mysqladmin5: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/opt/local/var/run/mysql5/mysqld.sock' (2)'
Check that mysqld is running and that the socket: '/opt/local/var/run/mysql5/mysqld.sock' exists!

thank you for your help!!

sock errors

What worked for me was to add this as well to my.cnf:

[client]
socket = /tmp/mysql.sock

-Svenni

Where is the sock?

I'm confused. The default .sock installed by macports is at:
/opt/local/var/run/mysql5/mysqld.sock

But everywhere in the examples here we're talking:
/tmp/mysqld.sock

Where should the sock file be? Should the my.cnf listing be changed to the mysqld.sock path in the /opt directory, and if so, why? I'd like to avoid silly hacks like symlinking the /tmp/mysqld.sock to somewhere else.

Thanks for any thoughts!

Use my.cnf to specify mysqld.sock location

It should be where /tmp/mysqld.sock and it can be put there by specifiying the path in your my.cnf file as svenni shows.

Did you set mysql to start

Did you set mysql to start on system startup? If not, did you start the mysql manually? You can always check if the mysql daemon is running via the Unix top command or the Process Viewer application on OS X. You can also review docs from MySQL.

Thank you for your answer.

Thank you for your answer. It seems that no mysql server is install on my computer. For installing mysql, i followed your article 'how to install apache with macports'. I'm sure that i have installed mysql, but have i installed the server?...

You'll also need to follow

You'll also need to follow the instructions on this page. The MySQL part of the Apache/PHP 5 only adds MySQL support into PHP.

It's a minor detail but the

It's a minor detail but the step where you confirm that MySQL is running uses the '-p' option. This prompts for a password but you don't set the MySQL root password until the following step...

Thanks for pointing that out

Actually, -p can be used, you just don't enter anything when prompted. That's what I should note.

Alias Didn't Work

The alias 'mysqlstart' did not work for me. I got a response along the lines of '[1] 410' - the two numbers changed each time. When I checked mysql was not running.

I changed the alias to the full command 'sudo /opt/local/share/mysql5/mysql/mysql.server start' and it now appears to be working fine.

Thanks Joel!

I had fixed that in my profile after posting this and forgot to update it.

copyright © 2011, 2 tablespoons | Privacy Policy