Install and configure MySQL 5 with MacPorts
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 <your-password-here>
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
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:
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:
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.
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.
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
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.
NP. Also, I noticed that on
NP. Also, I noticed that on reboot after running 'mysqlstart' the server still didn't start (even tho there was no error). I fixed this by 'touch'-ing the sock file and then running 'mysqlstart' a second time.
+server is important
I typically have installed things by hand (wget, untar, configure, make, make install, etc) but more recently I've felt that 99% of the time, the ports folks are getting it right and I'd just be wasting time doing it by hand.
However, getting used to the new paths and ways that other people do it isn't always so easy! Your post was pretty informative regarding that.
Thought I should mention that (at least according to this message: http://lists.macosforge.org/pipermail/macports-users/2007-April/002829.html ) the +server flag does still seem to be important if you want to launch mysql at startup.
Also, if you could mention the whole thing about the client being 'mysql5' (having the major version number after the executable... maybe aliasing it if the user only plans on using one version on their system), and possibly also about restarting to make sur the .sock file is properly created...
Again, the whole ports thing is almost more pain than it's worth, at least while doing it for the first time. :( So, thanks for this resource!
Thanks for the clarification
Thom, thanks for the clarification on +server, I'll update that point in the post.
You're absolutely right to point out the use of versions in executable names. I just ran into this while writing a shell script that calls mysqladmin. I didn't realize that I needed to call mysqladmin5 :)
I suppose sysadmins have differing opinions on configure/make/make install vs port systems— largely based on Unix skill level and server environment constraints.
I think that it's harder for those with lots of command line experience to get used to build locations other than /usr/local. I doubt that it's difficult for those who are relatively new to the command line, especially since they can see the contents of /opt/local in the Finder, which makes it easier to edit configuration files.
I'm starting to see some of MacPorts shortcomings with PHP extension selection. Someone recently asked how to install PHP's dBase extension, but didn't receive a timely response. Admittedly, dBase is not in high-demand, but it appears compiling PHP from source or using Fink might be the best way to add these extensions.