Sunday, August 10, 2008

MySQL On Mac OS X Using MacPorts

If you only want to use the MySQL client, all you have to do is install it using the port command:

sudo port install mysql5

You can now connect to remote servers with something like this:

mysql5 -usomeuser -psomepassword

However, if you want to run a MySQL server on your local machine, there are few more things that you have to do. First, you need to initialize MySQL system tables:

sudo mysql_install_db5 --user=mysql

This initializes MySQL databases under /opt/local/var/db/mysql5/ . We also need to create a directory under MacPorts that is identical to /var/run:

$ ls -ld /var/run
drwxrwxr-x  27 root  daemon  918 Aug 10 09:07 /var/run
$ sudo mkdir /opt/local/var/run
$ sudo chgrp daemon /opt/local/var/run
$ sudo chmod g+w /opt/local/var/run
$ ls -ld /opt/local/var/run
drwxrwxr-x  2 root  daemon  68 Aug 10 15:31 /opt/local/var/run

You can now start the MySQL server, but it is NOT SECURE yet. Make it only listen for local connections using the --bind-address option. This makes it impossible to connect to MySQL from the outside. If you intend this MySQL installation to be used for development only, you should always start it like this. If you want to use this in production and connect to it from other machines, you'll need to remove the --bind-address option, but only AFTER SECURING THE INSTALLATION.

sudo mysqld_safe5 --bind-address > /dev/null 2>&1 &

MySQL comes with a nice script to secure it, called mysql_secure_installation. Unfortunately, if you installed MySQL using the sudo port install mysql5 command, the script will fail, because it's looking for the mysql command to be in your path, but the command is really called mysql5. You can make a symlink to fix it, though I'm not sure if this will clash with future MacPorts MySQL installations. You can remove the symlink after you're done if you wish.

sudo ln -s mysql5 /opt/local/bin/mysql

Now you can run the secure script. Be sure to set the root password, remove anonymous users, disable remote root login, remove the test database, and reload privilege tables. Basically answer Y to everything.

sudo mysql_secure_installation5

You should now be able to connect using your new root password (you'll be prompted for it):

mysql5 -uroot -p

To remove the symlink you made:

# Make sure it's really a symlink to mysql5.
$ ls -l /opt/local/bin/mysql
lrwxr-xr-x  1 root  admin  6 Aug 10 16:11 /opt/local/bin/mysql -> mysql5
$ sudo rm /opt/local/bin/mysql

To shut down MySQL, you can use the mysqladmin command. It will prompt for your MySQL root password:

mysqladmin5 shutdown -uroot -p

If you are planning to use this server in production, you should probably create a my.cnf with all the needed options, and make MySQL start up when the machine boots. You should also create some non-root users, even if it's only used for development. But this is a topic for another time.

No comments: