"the site for those who crave disappointment"

Sponsored Links


17th Aug 2005, 19:40:27

By James Stocks

MySQL is a doddle to use in OS X. As a database to power web sites (such as this one), it's hard to beat.

Installing MySQL is easy thanks to the installer package provided by MySQL. Go to the MySQL download page and choose the version marked as the GA release, which at the time of writing was 4.0.20. If you are offered options such as 'Max', 'Debug' and 'Standard', choose standard.

Contents of MySQL Disk Image

When you've downloaded and mounted the disk image, you'll see that it contains two packages, one for MySQL itself and another for a startup item to launch MySQL on each boot. Install MySQL first, then the startup item.

The MySQL Installer

When the install completes, open the Terminal. If you look in /usr/local, you'll see that MySQL has been installed there:

Last login: Tue Aug 17 12:11:11 on ttyp1
Welcome to Darwin!
PowerBook:~ stocksy$ cd /usr/local/
PowerBook:/usr/local stocksy$ ls

mysql is a symlink to mysql-standard-4.0.20-apple-darwin7.3.0-powerpc, which is handy when it comes to upgrading. At this point, it's a good idea to add /usr/local/mysql/bin to your path variable. This is done by opening /etc/profile and appending the path. Type

sudo pico /etc/profile

You'll see:

# System-wide .profile for sh(1)

export PATH

[ -r /etc/bashrc ] && source /etc/bashrc

Add the MySQL path to PATH="/bin:/sbin:/usr/bin:/usr/sbin" so that it reads PATH="/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/mysql/bin" then press control+o, enter, control+x to save. You'll need to open a new terminal window for the changes to take effect.

If everything went OK, typing mysqladmin at the prompt should now give you a list of options for that command.

Start MySQL

If you don't use the startup item, or if you haven't rebooted since you installed MySQL, you'll need to start the daemon (mysqld) manually. To do so, type this in the terminal:

sudo bash
safe_mysqld &

MySQL will start, the '&' backgrounds the mysqld process. The first thing to do is set a root password, which is accomplished with:

mysqladmin -u root password 'your-password-including-the-quotes'

Now, you can connect to MySQL by typing

mysql -u root -p

If you provide your password, you'll finally be given a MySQL prompt:

PowerBook:~ stocksy$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 53 to server version: 4.0.20-standard

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


Switch to the MySQL configuration database:

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

For security, it's a really good idea to delete the default users who have permission to access the database over the netowork.

mysql> DELETE FROM user WHERE user = '';
Query OK, 2 rows affected (0.09 sec)

mysql> DELETE FROM user WHERE user = 'root' and password = '';
Query OK, 1 row affected (0.30 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.18 sec)

Again, for security, I'm removing the test database (which could otherwise be accessed by any user)

mysql> drop database test;
Query OK, 0 rows affected (0.29 sec)

Stopping MySQL


mysqladmin -u root -p shutdown

Creating a user for PHP

Earlier, I said that PHP can connect to MySQL. It's not a very good idea to give it root access, just as you don't have MySQL run as root on your Mac. You can make a add a user from the MySQL prompt like this:

mysql> grant select, insert on databasename.* to username@localhost identified by 'your-password-including-the-quotes';

Here, I've given the user insert and select privileges, you can grant whatever privileges you need to.

Backup up MySQL

There are several way of doing this, but I like to use mysqldump. Type this in terminal:

mysqldump -u root -p database > /path/where/you/want/to/backup.sql

If you want to do this as part of a cron job, remember you'll need to supply the absolute path to mysqladmin. You can supply the root password as an argument after -p, but remember that it'll be stored in plain text.

Restoring a backup

Create a database with the same name as the one you are importing, then type this in terminal:

mysql -uroot -p databasename '/file/location.sql'

Tip: If you have (or are willing to install) PHP, try phpMyAdmin.

New Comments

Some Rights Reserved