MySQL has become one of the most popular databases for Web applications. The database is well suited for common Web-related tasks like content management, and for implementing Web features like discussion boards and guestbooks. For a time, some developers avoided MySQL for commercial applications because it did not implement certain features, such as transactions. But this is no longer the case, and MySQL is a great choice for just about any Web-based application. In this article I’ll give you an overview of MySQL’s features and drawbacks, show you how to install MySQL on Mac OS X, and introduce you to some of MySQL’s notable technical aspects. MySQL FeaturesPerhaps the most prominent feature of MySQL is its speed when running SQL SELECT statements. MySQL was built for speed. The core of the MySQL engine is very small and streamlined, and the default table type (a modified ISAM table) was designed specifically for running SELECTs quickly. If your application calls for the advantages of a relational structure but the database contents are relatively static — as is often the case with Web content — MySQL’s speed is a great advantage. MySQL is also undeniably stable. In both your production and serving environments, you can be reasonably confident that MySQL will be up and processing queries as long as power flows to your machine. Another important benefit is that MySQL is relatively easy to learn. Even if you’re new to relational databases, you can learn MySQL and create very sophisticated Web applications in a short period of time. The popularity of MySQL is a benefit as well, because if you run into difficulty, you can lean on the active community that supports MySQL. There are many mailing lists dedicated to MySQL, and most questions find quick and thorough answers. MySQL DrawbacksIf you are an advanced database user, you should be aware of some of MySQL’s limitations. MySQL’s implementation of standard query language is missing support for sub-selects, foreign key constraints (for some table types), stored procedures, and views. If you feel you need these features, you’re probably better off looking into PostgreSQL, FrontBase, or another database. Lack of support for transactions used to be a drawback of MySQL, but this has been addressed. Now, on Mac OS X, you can use the MySQL InnoDB table type and have access to row-level locking and robust transaction support, as well as foreign key constraints. Installing MySQLIf you’re running Mac OS X Server, you are in luck — MySQL is already there. Just go to Applications/Server/MySQL Manager to access it. If you are running Mac OS X Client, you’ll have to install MySQL. if you have already installed a version of MySQl and want to upgrade, I can recommend the upgrade instructions from http://www.entropy.ch. For a new installation, follow the Mac OS X installation instructions for the MySQL provided binary distribution ( a true Mac OS X installer package file) at http://dev.mysql.com/doc/refman/5.0/en/mac-os-x-installation.html and be done with it. However, sometimes you want to compile and install directly from the source, either because you are changing the default build settings, or you want the latest and greatest version before there’s a binary installer. The following will help you through that process. When installing MySQL, you need to be aware of the potential effect this will have on the security of your system, as a database server can open an avenue of attack. In the example below, I show how to install MySQL on Mac OS X while maintaining the security of your system. One basic security tenet is that of “least privilege.” In short, this means that everyone and everything should have only the privileges required for it to complete its task(s). Those privileges should be available for the least amount of time possible—ideally, once the task is completed, the privileges should be revoked. I’m also choosing to build MySQL from source, rather than install a pre-built binary. This gives greater control over the installation, as you’ll see below. Configuring and Compiling MySQLI plan to install mysql in You can now download the source via a Web browser. Once you have the source, you can pretty much follow the quick install directions from the mysql documentation pages, adding only debug support ( ./configure --prefix=/usr/local/mysql --with-unix-socket-path=/usr/local/mysql/run/mysql_socket --with-mysqld-user=mysql --with-comment --with-debug Once the configuration completes, running % ls -Fla /usr/local/mysql/ total 26 drwxr-xr-x 13 root wheel 1024 Jun 5 13:42 ./ drwxr-xr-x 11 root wheel 1024 Jun 5 12:19 ../ drwxr-xr-x 2 root wheel 1024 Jun 5 12:20 bin/ drwxr-xr-x 3 root wheel 1024 Jun 5 12:19 include/ drwxr-xr-x 2 root wheel 1024 Jun 5 12:19 info/ drwxr-xr-x 3 root wheel 1024 Jun 5 12:19 lib/ drwxr-xr-x 2 root wheel 1024 Jun 5 12:20 libexec/ drwxr-xr-x 3 root wheel 1024 Jun 5 12:20 man/ drwxr-xr-x 6 root wheel 1024 Jun 5 12:21 mysql-test/ drwxr-xr-x 2 root wheel 1024 Jun 5 13:42 run/ drwxr-xr-x 3 root wheel 1024 Jun 5 12:20 share/ drwxr-xr-x 7 root wheel 1024 Jun 5 12:21 sql-bench/ drwx------ 4 root wheel 1024 Jun 5 13:37 var/ Note that at this point everything is owned by root — meaning the mysql account won’t be able to write to the databases under sudo chgrp -R mysql /usr/local/mysql sudo chown -R mysql /usr/local/mysql/run /usr/local/mysql/var The directory listing now looks like: % ls -Fla /usr/local/mysql total 26 drwxr-xr-x 13 root mysql 1024 Jun 5 13:42 ./ drwxr-xr-x 11 root wheel 1024 Jun 5 12:19 ../ drwxr-xr-x 2 root mysql 1024 Jun 5 12:20 bin/ drwxr-xr-x 3 root mysql 1024 Jun 5 12:19 include/ drwxr-xr-x 2 root mysql 1024 Jun 5 12:19 info/ drwxr-xr-x 3 root mysql 1024 Jun 5 12:19 lib/ drwxr-xr-x 2 root mysql 1024 Jun 5 12:20 libexec/ drwxr-xr-x 3 root mysql 1024 Jun 5 12:20 man/ drwxr-xr-x 6 root mysql 1024 Jun 5 12:21 mysql-test/ drwxr-xr-x 2 mysql mysql 1024 Jun 5 13:42 run/ drwxr-xr-x 3 root mysql 1024 Jun 5 12:20 share/ drwxr-xr-x 7 root mysql 1024 Jun 5 12:21 sql-bench/ drwx------ 4 mysql mysql 1024 Jun 5 13:37 var/ You can now start mysql and perform a few important tasks, like setting a mysql password to protect the database itself. Note that, while starting the database requires system root privileges, actions within the database itself do not require system root privileges, but database root privileges. It is somewhat confusing that MySQL uses the account name “root” for its all-powerful account, just as the system does, even though they are completely separate entities. Starting mysql is accomplished with: sudo /usr/local/mysql/bin/mysqld_safe --user=mysql & Now you can run through some of the basic tests — but first, go ahead and secure the database by adding a password for the database “root” user, as follows: /usr/local/mysql/bin/mysqladmin -u root password sniggle Here “sniggle” is the password you are assigning to the database root account. In MySQL, a single user is associated with a username and a host. Most often on your development machine you will be connecting to the database locally, so the host will be “localhost”. However, if you are attempting to connect from a different machine, you will have to assign permissions based on both username and hostname. For more information on users and passwords within MySQL, read about MySQL’s grant tables, and the ConclusionMySQL is a great database for Web applications and a great complement to a Mac OS X development environment. Install it on your machine and create applications in Perl, PHP, JSP, or whatever languages you like best. To administer a MySQL installation on Mac OS X, you can look to popular tools such as the Web-based phpMyAdmin from phpwizard.net, or MacSQL from Runtime Labs. For information about starting MySQL on startup, see this article from macosxfaq.com.
|