Creating and Working with a MySQL Database

Installing and Configuring MySQL

The MySQL database system is a free implementation of a database system based on Structured Query Language (SQL). MySQL has been successfully deployed for a number of high-end applications on websites such as NASA, Yahoo!, and Slashdot. In addition to the database package itself, MySQL has JDBC and ODBC drivers available, making it accessible from any platform supporting these standards, including Microsoft Windows.

NOTE

The letters SQL are pronounced "S-Q-L," not "sequel," which is typically associated with Microsoft's SQL Server. MySQL, as documented on http://www.mysql.com, is pronounced "My S-Q-L."

Downloading the MySQL Distribution

Following the growing trend of providing Macintosh binaries for popular Unix/Linux packages, MySQL has added the Macintosh platform to its officially supported binary distributions. To install MySQL, all you need to do is download the latest binary distribution from http://www.mysql.com website and run the installer.

The MySQL software is updated on an ongoing basis, so if you want, you can download the latest source release and compile/install it on your own. We highly recommend, however, that you stick with the official packaged release.

The MySQL disk image can be downloaded from http://dev.mysql.com/downloads/.

NOTE

Three binary distributions of MySQL are available: Standard, Max, and Debug. The Standard distribution should be fine for most users. Developers might want to use the Max distribution because it includes some advanced features that will eventually be incorporated into the Standard distribution. The Debug distribution includes debug code and should not be used in a production environment.

Mount the distribution disk image (double-click, or use hdiutil attach <image name>, enter the resulting MySQL directory, and use either the installer command-line utility or GUI installer to set up the software. There are two packages you will need to install: the main MySQL package and a StartupItem that will launch the server at boot.

MySQL is installed in a version-tagged directory within /usr/local, such as /usr/local/mysql-standard-4.1.7-apple-darwin7.5.0-powerpc. This directory, in turn, is symlinked to /usr/local/mysql, so it's best to use the link for referencing commands from scripts and so on.

TIP

If you'd like to compile MySQL from scratch and install it in the same directory structure as the binary distribution, configure it with

 ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local /mysql/data --sbindir=/usr/local/mysql/bin 

NOTE

The installer automatically sets the appropriate directory permissions and runs the command /usr/local/mysql/bin/mysql_install_db, which sets up the default MySQL databases.

If you've compiled the software by hand, you must first use chown -R mysql /usr/local/mysql/data to set the data directory ownership. Then run mysql_install_db to manually install the initial databases.

Starting the MySQL Daemon

Your MySQL server can be started by changing to the /usr/local/mysql directory and then executing the command ./bin/safe_mysqld as root. Note: You must execute safe_mysqld from one level above the /usr/local/mysql/bin directory, or the daemon will fail to start.

 brezup:jray jray $ cd /usr/local/mysql ; sudo ./bin/safe_mysqld & Starting mysqld daemon with databases from /usr/local/mysql/data 

Alternatively, if you installed the StartupItem, you can simply type:

 sudo /Library/StartupItems/MySQLCOM/MySQLCOM start 

You should reset the root MySQL password immediately after the server is started. Use the command

 /usr/local/mysql/bin/mysqladmin -u root password '<my new password>' 

For example, to set my password to john (an extremely poor password, by the way), I'd use

 brezup:jray jray $ /usr/local/mysql/bin/mysqladmin -u root password 'john' 

NOTE

When creating the new root password, be aware that this password has no effect on the actual Tiger root account. For more information on MySQL user management, visit http://dev.mysql.com/doc/mysql/en/User_Account_Management.html. I recommend getting a firm grip on MySQL's permissions system before deploying MySQL on a public server.

TIP

The MySQL binaries are installed in /usr/local/mysql/bin, which is not part of the default user path. To alter the system to include the directory in your path, see Chapter 15, "Shell Configuration and Programming (Shell Scripting)." This eliminates the need to type the full pathname each time you need to access a MySQL utility. Alternatively, you might want to create a symbolic link from the binaries in /usr/local/mysql/bin to /usr/local/bin.