MySQL 101 on OS X - Part I

Well, to get started with my CodeIgniter book, I needed to get MySQL setup again on my machine. So for my own future reference, here are the steps to do that.

Install MySQL

This is pretty easy, go to mysql.com and download the open source server for your operating system. I used the version 5.1.31 for Mac OS X 10.5. There is lots of info on the MySQL site for this if you need it.

Starting and Stopping the Server

The first step is to start the server. On OS X, MySQL is located in the /usr/local/mysql/bin directory. You may want to put this directory in your path to save keystrokes.

To start the MySQL server use this command as root:

sudo bash

/usr/local/mysql/bin/mysqld_safe &

This should get the database server up and running in the background. To test the server to see if it is running, run the mysql client.

mysql

If the server is running you should see something like this:

macpro:~ # mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.31 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 

If the server is not running, you will get an error message like this:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Eventually, you will want to stop the server. That it done with the following command (also as root):

/usr/local/mysql/bin/mysqladmin shutdown

You can start and stop the server, now it is time to setup a development account.

Creating a User Account

You can actually do all your development using the default root account with no password set. But this is a bad idea. Once you move your code to a hosted web server, you will need to use a username and password to use MySQL. So you might as well get used to it now.

The command to add a user to MySQL is straightforward, but a bit verbose. Here is an example:

mysql


GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'somePass' WITH GRANT OPTION;


quit

First your start the client and enter an SQL statement. The SQL command creates a superuser account (all privileges are granted) for a user named "monty" connecting from the server named localhost, with a password of 'somePass'. Type quit to exit out of the client.

Alternate Ways of Entering MySQL Commands

Using the text interface of the MySQL client is only one way to enter SQL statement and commands into MySQL. I find the text client to be a bit clunky, so I often enter SQL statements on the command line like this:

mysql -u monty -psomePass -v -e "mysql statement;"

This command executes the SQL statement from the command line and displays the output there. The -v option provides verbose output. The -e option defines the SQL statement to be executed. The advantage of this sort of setup is you don't have to leave you shell prompt to perform database functions. In addition, it is easy to script the command line option. For example, a simple shell script for executing commands might look like this:

#!/bin/bash
mysql -u monty -psomePass -v -e "$1"

Or, if I wanted to script the add user functionality in say Perl, it might look something like this:

   1:#!/usr/bin/perl -w
   2:
   3:my $userToAdd = "monty";
   4:my $userPassword = "somePass";
   5:my $hostName = "localhost";
   6:
   7:#  SQL String
   8:my $sqlStr = "GRANT ALL PRIVILEGES ON " . '*.*' . " TO '$userToAdd'\@'$hostName' IDENTIFIED BY '$userPassword' WITH GRANT OPTION\n";
   9:
  10:#print $sqlStr;
  11:system "mysql -u root -p -v -e \"$sqlStr\" ";
  12:
  13:

For someone like me who makes a lot of typo mistakes, this is really helpful.

Well that ends part I. In part II, I will create a database and load a dump file into the database.

For more information see:

• MySQL command line options