User login
Popular posts
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.
Related links and references
Comments
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!
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!!
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.
Post new comment