Macports mysql5 setup under Snow Leopard

Question

I've managed to get my install all screwed up as I'm a newbie and messing up is what I'm good at.

1) Can anyone reference a credible set of setup instructions for this stuff?

2) Can I just uninstall and start from scratch (passwords and all) through macports? I tried and a bunch of mysql directories stuck around after the unistall, WTF!?

3) Where does mysqld.sock need to reside and what file settings do I need to apply to get it playing nice with php5 and apache2 ? Does it live in /tmp or in /opt/local/var/run/mysql5/ ? Do I need to configure /opt/local/etc/php5/php.ini? opt/local/var/db/php5/mysql.ini? /opt/local/etc/mysql5/my.cnf?

4) I've run > sudo -u mysql mysql_install_db5. If I run it again will it mess me up? I ran mysqladmin -u root password 'mypw' and got an error saying the mysql could not connect through the socket. So does this mean my password is now set? Is there a way I can tell?

5) The syntax of the password statement changes from blog to blog. Is my password 'mypw' or mypw (without the quotes)? (My password is not actually mypw)

6) What are the appropriate commands to start and stop mysql? I DO NOT want it to start when my machine starts.

---

Thanks Jergason, that's tremendously helpful.

I'd really like to stay consistent with the macports installation of mysql since it limits the damage I can do to my machine.

So the outstanding questions now are...

1) What is the default socket location for the macports mysql install?

2) What are the appropriate commands to start and stop mysql? I DO NOT want it to start when my machine starts. Some blogs say to use launchctl -load others say sudo /path/mysql_safe5. If you havn't set the password (as I don't intend to) do you need to use the -user -p parameters?

3) What are the necessary directory ownerships and permissions to get macports mysql working. It doesn't seem that macports sets any of that.

---

Nerdling

Check /tmp for the MacPorts mysql socket

Not helpful. As previously stated, I understand that /tmp is the default location for the socket. My problem is that when I attempt to start mysql the socket does not materialize there, nor does it appear in /opt/local/var/run/mysql5/. This tells me I have a problem starting mysql, which means there is something wrong with the setup. What it could be, I don't know. I'm afraid I wasn't born with an a priori knowledge of such things.

-What SPECIFIC directory SHOULD the socket be in for the mysql5 install using macports under Snow Leopard?

If you're using 5 different sites' tips on how to run mysql and you're surprised you've gotten lost … well, I'll just leave it at try to follow just one site at a time and back out all changes before going to the next.

Not helpful. I did exactly what you're suggesting to begin with. What do you think I did, mix and match instructions from 5 blogs? This is why I asked about clean uninstalls. I wanted to know if a state change from a previous install could effect future "clean" installs.

-What SPECIFIC blog describes the confirmed-to-be-correct install procedure of mysql5 using macports under Snow Leopard.

I think the only problem you're having with MacPorts mysql (what problem are you having??) is that it's got a socket where you don't expect it and it's using a default configuration.

Not helpful. Me too, see my original post. I think the problem lies with how mysql is started (or isn't starting).

-Do you have any SPECIFIC suggestions on how to correct the problem?

This should have been explained after the install was completed by MacPorts.

Not helpful. It wasn't, or I didn't see it if it was. Maybe because I was using Porticus.

-Could you be more SPECIFIC? At WHAT point should I have seen WHAT message?

Please see ${prefix}/share/doc/mysql5/ for ideas and/or contact the developer list. Also consider contacting the maintainer of the port; they likely know the most about it since they manage it.

Not helpful. RTM, Google is your friend, and all that. Yep, went there first. Although I will admit I may have overlooked something as I'm not inclined to spend days reading some poorly written docs when I have other work to do to when I'm sure my problem is a simple one.

• If you want to give me a SPECIFIC document that I may have overlooked, THAT would be helpful.

I was hoping for some simple, focused help like what Jergason provided. I (and others reading this) appreciate Jergason's willingness to convey his knowledge in order to accelerate the learning process of people new to various technologies. To him, the knowledge is basic, but to new-comers it's a massive time-saver. The true mark of intelligence is the understanding that technology knowledge is not a priori and that even basic help is of great help. Thanks again J-man.

Answer 1

I've just installed mysql5 myself, and since this is always a pain I've noted the process that I followed. This was done on Leopard, but I imagine the process is the same on Snow Leopard and Lion. It doesn't answer all the original questions, but at least it's something of a guide.

Installing Mysql

First, install mysql5 server with:

sudo port install mysql5-server

Just installing mysql5 doesn't install the server.

Pay attention to the console output, it includes instructions for setting up macports. You might want to copy and paste it to a text file. The following is based on it.

Instead of mysql5-server, you could use a port such as mysql55-server, mysql56-server, mariadb-server or percona-server to get a more recent version of mysql, or a fork. If you do, pay attention to the console output, as the following instructions are based on mysql5-server and will need to be adjusted to use the correct executables and paths.

If this is a new install, set up the database:

sudo -u _mysql mysql_install_db5

That outputs some generic instructions, which I don't think are entirely appropriate for macports. In my opinion the best way to load mysql5 as a daemon is to use macport's method:

sudo port load mysql5-server

As well as starting mysql5, this permanently loads it - it will run on boot up. To stop this later:

sudo port unload mysql5-server

If you don't want to run it as a daemon, you can run it at the command line:

sudo /opt/local/lib/mysql5/bin/mysqld_safe

Check that it's running by logging in at the command line:

mysql5 -u root -p

By default, the password is empty, so just press enter when prompted. To set a root password:

/opt/local/lib/mysql5/bin/mysqladmin -u root password 'correct horse battery staple'

Instructions for setting up both macports php and the native php installation follow.

Setup Macports PHP

Assuming you've already got macports php installed and running. You need to install php5-mysql (or something like php54-mysql depending on which version of php you're using):

sudo port install php5-mysql

This installs the mysql, mysqli and pdo drivers.

Now look in your /opt/local/etc/php5 directory, if you don't already have a php.ini configuration file copy either php.ini-development or php.ini-production to php.ini. Now edit php.ini and search for the appropriate lines to add:

pdo_mysql.default_socket=/opt/local/var/run/mysql5/mysqld.sock

and:

mysql.default_socket = /opt/local/var/run/mysql5/mysqld.sock

and:

mysqli.default_socket = /opt/local/var/run/mysql5/mysqld.sock

If you don't want to configure these, you can set them explicitly in your php script when you connect.

If you're having trouble connecting you might want to look at the other nearby settings, and compare with php.ini-development and php.ini-production to see what's been changed.

Then use the script below, or something similar to test that you can connect with php.

Setup System PHP

OS X's php comes with mysql and mysqli support built in (but not pdo), so all you need to do is set the macport unix socket. The default location is /opt/local/var/run/mysql5/mysqld.sock. Find the correct place in /etc/php.ini (if you don't have it already, copy it from /etc/php.ini.default) to add:

mysql.default_socket = /opt/local/var/run/mysql5/mysqld.sock

and:

mysqli.default_socket = /opt/local/var/run/mysql5/mysqld.sock

If you don't want to configure these, you can set them explicitly in your php script when you connect.

Test Script

Here's a php script to check that it can connect. Obviously, you won't normally use the root account in your php scripts so you might want to first create another mysql account for testing the connection. The PDO connection won't work for native php since that doesn't have PDO drivers.

<?php

$username = 'root';
$password = 'correct horse battery staple';

/* Try mysql: */

$connection = mysql_connect('localhost', $username, $password);
if ($connection === FALSE) {
    echo "Error connecting using mysql.\n\n";
    echo "Error ".mysql_errno().": ".mysql_error()."\n\n";

}
else {
    echo "Connected using mysql.\n\n";
    mysql_close($connection);
}

/* Try mysqli: */

$connection = mysqli_connect('localhost', $username, $password);
if ($connection->connect_error) {
    echo "Error connecting using mysqli:\n\n";
    echo "Error ".$connection->connect_errno.": ".$connection->connect_error."\n\n";
}
else {
    echo "Connected using mysqli.\n\n";
    $connection->close();
}

/* Try pdo:
 * Won't work for the version of php supplied with OS X. */

try {
    $pdo = new PDO('mysql:host=localhost', $username, $password);
    echo "Connected using PDO.\n\n";
    $pdo = null;
}
catch(PDOException $e) {
    echo "Error connecting using PDO:\n\n";
    echo "Error ".$e->getCode().": ".$e->getMessage()."\n\n";
}

Answer 2

I've just run into the same issue myself. To get my MySQL working, I simply had to do

sudo chmod -Rf 777 /opt/local/var/db/mysql5

The attempt to do sudo -u _mysql mysql_db5 failed, but I could then do

mysqladmin -u root  password the_new_password

without any problem.

If you don't want MySQL to start automatically when you turn the machine on, simply do this:

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

Then to start/stop MySQL:

start:

sudo /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper start

stop:

sudo /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper stop

Hope this helps.