MySQL port open in Leopard firewall (and in ipfw) but can't access

You can Google up some answers, but I'm betting that the problem is that the mysql service is going to reject connections from anything other than the 127.0.0.1 localhost socket.

Here are my notes on how to allow user connections from IP addresses other than the localhost address:
To add a host to the allowed connections, you need to grant access to the user by the host IP address. This required going through the Terminal command line app, logged in as root and issuing the following commands:

+mysql -u root -p+
(opens access to mysql service)

+grant all privileges on . to 'user'@'%' identified by 'password' with GRANT OPTION+
where user is a mysql user account name, the @IP address uses the wildcard % value to allow connections from any host, and the password value is that of the mysql user.

If the connection is only going to originate on a LAN with fixed IP addresses, you could use a specific IP address instead of the wildcard "%" value. You can also limit access to specific databases in the " ." value.

HTH,

-Doug

Hi,
It appears you are trying to use the installed mysql that comes with 10.5 server.
If you are on the LAN inside the firewall you shouldn't need any port open to reach the mysql server. Perhaps your settings need attention.
Here is a setup that works for me and uses the Server Admin interface to setup, start and stop the service:

MySQL Service in Server Admin

Initial Conditions:
1. Server -> Settings -> Services with MySQL checked.
For data storage on an external volume
2. Set permissions on /Volumes/<extHD name>/mysql to
owner = _mysql, group = admin
owner rwx, group rx, world rx (r=read, w=write, x=execute)

Server Admin MySQL Settings panel
1. Check – Allow network connections
2. Set database location to - /Volumes/<extHD name>/mysql
3. Set MySQL Root Password – xxxxxxx (this is the password to login to mysql)
4. Save and start MySQL service
5. In MySQL logs verify MySQL daemon started and
datadir = /Volumes/<extHD name>/mysql
with log entries for service up and running
6. In Terminal create a database:
mysqladmin –u root –p create <database name>
Note that database names are lowercase (case insensitive file names)

With the service running, then at the server terminal or through an ssh connection to the server, issue at the prompt $:
$ mysql -u root -p
$ mysql -u root -p -h server_url (if not on ssh and remote)

enter the MYSQL root password when prompted
you will be entered into the mysql monitor with prompt mysql>
remember to put a semicolon at the end of each statement as the end of command
select the database to use with

mysql> USE <database name>;

exit from the monitor and database with

mysql> exit;
should receive Bye

HTH,
Harry

Hi,
I posted during your last post. You can't run two versions of mysql simultaneously. 10.5 server has its own version and will run that if setup. There are significant modifications required to remove it and then install an independent version of mysql. Early posts on the forum document this issue.
HTH,
Harry

Hi Ben,

I have done some brief digging on MAMP and it would appear that you're trying to override the inherent features in OS X Server by using MAMP instead. Apache, MySQL and PHP are all inherent parts of OS X Server, minus the unified interface of MAMP. (phpMyAdmin is open-source and can be installed independently.) This may have been necessary on a non-server (client) version of OS X, but not for Server. As Harry pointed out, removing those elements from OS X Server are not simple tasks and you would be far better off using the built-in versions over trying to bypass them to run MAMP.

3306 is the default port for MySQL, so your pings are reaching the mysql service. You may run into the same problem as I did when trying to establish external connections to mysql from anywhere other than on the host machine (whether that be across a LAN or via the Internet though PHP commands to the mysql service) since requests from anything other than the localhost will be rejected. Working around that with MAMP is not well documented. phpMyAdmin doesn't get under the hood to make the changes needed to allow requests from other hosts.

The MAMP documentation is also lacking on how one can get under the hood of its mysql service, other than that you can access it through Terminal at:
/Applications/MAMP/Library/bin/mysql
in which case all normal mysql commands should work when working in that directory:
+/Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot+

All in all, though, I think it would be far easier to use the built-in versions. OS X Server is configured to work with what is already there and Apple's support documentation is built around that. Trying to get MAMP working means that you're pretty much on your own trying to figure out the whats, hows and whys when things aren't working. The Server Admin application gives you access to all those services (at least at a base level, excluding phpMyAdmin).

For 10.5 Server, the built-in version of MySQL is 5.0.91; PHP version is 5.2.14; Apache version is 2.2.14, all of which are mostly-current, stable releases.

I don't have any /mysql/msql directories inside the /var/ directory.

That is really odd, as they should be there for an installation of Server.

-Doug

Hi Ben,

glad to help, such as it is. 😉

I just checked my other server and yes, there's nothing there for /var/mysql - I never set up the service on that machine through Server Admin. But it is there on the machine hosting my mysql service, so enabling the service must activate those directories.

I like the idea behind MAMP, but without having robust documentation or support wiki.. you're left to figure stuff out on your own. 😟

I've never used ARD so I can't offer any definitive advice there. I do know there are firewall service ports that you can open in the Server firewall service for ARD, and I imagine that it might take getting a VPN configured to get onto your LAN for ARD to see the clients without having to resort to using VNC. I would hope that would be covered in the ARD documentation.

Running PHP and MySQL on OS X Server are pretty straightforward. I set up a subdomain to install phpMyAdmin for working with the MySQL databases, though you could simply drop that into a subdirectory of your main web directory. The biggest problem I ran into was not being able to initiate MySQL queries through PHP scripts on my websites, hence my original post regarding the lockout of requests from other host IP's. PHP runs pretty clean out of the box unless you need to get under the hood and tweak some settings for specific needs like file uploads and such run through PHP scripts, and that's not hard to change once you know where to find the config files and what to edit - and those instructions are in the PHP documentation and many other places on the web.

-Doug