What Is MySQL?

CONTENTS

Appendix C. Introduction to MySQL

 

•  What Is MySQL?
•  Setting Up MySQL (Windows)
•  Setting Up MySQL (Mac OS X)
•  Getting Around in MySQL
•  Resources and Further Reading

If you're using Microsoft Access for your dynamic data development, your setup is easy. If you can't or don't want to use Access, your most accessible alternative is MySQL. MySQL is free, and has all sorts of other features in its favor but you need to know how to get it, set it up, and at least get around in it before you can plunge into development. This appendix takes you through the basics, for Windows and Mac OS X, to get you up and running as quickly as possible.

As explained in Chapter 26, "Introduction to Dynamic Dreamweaver," MySQL is a free, easily available database management system (DBMS) available for Windows and various flavors of UNIX (including Mac OS X). It's a great development tool, and is used for online database systems all over the world. In addition to its cost benefits, it's fast, stable, and can handle huge amounts of data and heavy traffic (lots of people accessing it at once).

Note

MySQL might be free for most uses, but it is not an open-source product.

MySQL uses the client/server model. Before beginning work with MySQL, you must know what that means. MySQL consists of several independent programs:

• Server. The server program (called mysqld, or the mysql daemon, in UNIX-speak) controls everything which users are allowed to use the program, how online access is handled, and so forth. The server must always be running before the system will function.

• Client. The client program (called mysql) enables users such as you to create and edit databases. This program must be running before you can work with a database; but it needn't be running while you access the database from Dreamweaver or a browser.

• Admin. Various utility programs are part of the MySQL package. The one you'll use most often (maybe the only one you'll use) is the admin program (mysqladmin).

Setting Up MySQL (Windows)

You can approaching MySQL for Windows in a variety of ways. All of them involve spending a little bit of time in the Console window (MS-DOS window, on Windows 98). These instructions minimize that, and you should find this a pretty smooth setup.

Downloading

MySQL for Windows is available from the MySQL web site (www.mysql.com). At the time of this writing, current downloads are available at http://www.mysql.com/ downloads/mysql-3.23.html. On this page, scroll down until you find the Windows download section. Download the Zip installation file.

Note

As always with software, MySQL is evolving. By the time you read this, the current version might have changed from 3.23. Download the latest stable release.

Installing

To install MySQL, follow these steps:

1. After downloading, use WinZip or another unzipping utility to unpack the archive.

2. From the archived files, find and launch setup.exe. Follow the setup instructions as they appear. For best results, accept the default install location (at the root of the C: drive). After you have finished, you'll have a new mysql folder on your C: drive.

Configuring and Setting Up

To use MySQL, and in particular to use it with the exercises in this book, you'll need to configure it and set up the Antiques database. This section also covers how to start and stop the MySQL program every time you want to work with it.

1. MySQL stores its databases in a folder called data. If you look within that folder (c:\mysql\data), you'll see that it contains several folders each of those represents one database. To copy the Antiques database to your computer, you'll need to store its folder in this location.

   1. On the CD, find the databases folder. Open that folder, and the antiques_mysql folder within it. Inside there is another folder called antiques.

   2. Copy that entire folder to the c:\mysql\data folder. Don't rename it or disturb any of its contents.

2. Now you need to launch the server portion of MySQL. If the server portion isn't running, you cannot work with MySQL or any of its databases. Doing this is easy because the server is accessed by a nice, friendly utility called WinMySQLAdmin (see Figure C.1). (This is a GUI for the mysqladmin and other utility programs discussed in the previous section.). To launch the server, follow these steps:

   Figure C.1. The WinMySQLAdmin interface (Windows).

   

   1. Browse or explore to c:\mysql\bin\WinMySQLAdmin.exe, and launch it. The WinMySQLAdmin interface window that opens look like that shown in Figure C.1.

   2. Bring the Databases tab to the front.

   3. The upper-left pane lists existing databases. (The mysql database contains all the administrative information for the system, like such as who the users are and so forth. The test database is empty; it's just a test.)

   4. Right-click in this pane, and from the contextual menu, choose Refresh Databases. If you have copied the antiques folder to the data folder, it will now appear in your list of databases.

   5. If you like, you can explore the other panes of this window, to see what tables and columns are contained in this database. You cannot edit the database from here, however.

      Tip

      

      The Databases pane of WinMySQLAdmin contains the name and IP address of your computer. You'll need that IP address in a few minutes. Jot it down somewhere while you're here, so that you don't have to look it up later.

3. WinMySQLAdmin needs to be running in the background before you can use or access any of the MySQL databases. Therefore, you don't want to quit the program. But, you can hide it. If you're in the Environment Tab, just click the Hide Me button in the lower-left corner of the window. Or right-click anywhere in the window and, from the contextual menu that pops up, choose Hide Me. The program window disappears, and a little stoplight icon appears in your system tray on the task bar. The stoplight shows a green light, which means the MySQL server is running (see Figure C.2).

   Note

   

   Don't try to hide WinMySQLAdmin by clicking the close box in the upper-right corner of the interface. This stops the MySQL daemon and MySQL will not be active!

   Figure C.2. The WinMySQLAdmin system tray icon, indicating that the MySQL server is running (Windows).

   

   Any time you want to access WinMySQLAdmin again, click the stoplight icon, and choose Show Me.

   To exit the MySQL server, when you have finished working just show the MySQLAdmin window and close it (click in the close box) instead of hiding it. The program doesn't take up much memory, however; if you want, you can leave it running all the time. If you shut down your computer with the stoplight still showing in the system tray, the next time you boot up, MySQL server will launch automatically, and the stoplight will reappear.

4. Now you need to launch the MySQL client program (the one that you actually manipulate database information with). This is done through the Command Prompt window (called the MS-DOS prompt, on Windows 98). To open this, go to Start > Programs > Accessories > Command Prompt (or MS-DOS Prompt).

5. This is a command-line interface, so there's no mousing around it. You'll launch the MySQL client program by typing the full path to the program file. Type this:

   c:\mysql\bin\mysql

   End the line by pressing Enter or Return. (Always do this in the command-line interface.) As long as you have started up the server already, the prompt will change to mysql>. You'll also be presented with some welcome information. You're now in the MySQL client program (see Figure C.3).

   Figure C.3. Launching the MySQL client program in the Command Prompt window (Windows).

   

6. Now you need to tell the server who will be using your databases, and how much access they should have. With the mysql> prompt still showing, type this:

   GRANT all privileges on antiques.* to ""@localhost;

   (End the line by pressing Enter/Return.) If you get an error message, check your typing and try again until it works.

   Next, check your notes and find the IP address for your computer. (Remember, this was displayed in the WinMySQLAdmin window.) Type this, substituting your IP address for the numbers shown here:

   GRANT all privileges on antiques.* to ""@192.123.123.123;

   You've just given anybody on your computer permission to access the Antiques database. You did it in two different ways, because different application servers sometimes require different access methods.

   Note

   

   You'll need to add permissions like this for every new database you create. MySQL itself might let you access its items without setting permissions, but some of the application servers you work with will require username and password information before you can work with MySQL online. If you want to just grant permissions once and for all, instead of specifying antiques.*, specify *.* note that this is a security risk if your computer is on a network or if you're connected to the Internet (especially if you have an always-on connection such as a cable modem or DSL, and aren't behind a firewall). Enterprising hackers have access to even the administrative tables of your MySQL installation.

7. After you have finished working in the MySQL client program, you can quit by just typing the following:

   quit;

The program will tell you goodbye, and the mysql> prompt will disappear. Note that this quits only the client program, not the server; and that's the way you want it. The server must be up and running as you work on your Dreamweaver files.

Note

If you ever try to launch the client program using the command in step 4, and get an error message that the socket is not available, that means the server program is not running. Use WinMySQLAdmin to launch it.

Setting Up MySQL (Mac OS X)

MySQL for Mac OS X is basically MySQL for UNIX. It's not for the timid. Setting it up involves using the Terminal window for command-line input, and probably learning more about system security than you ever wanted to know. The process is not as simple as it is on Windows. If you're willing to give it a try, however, the instructions here were created to make the installation as painless and friendly as possible.

Before You Begin

You'll need to know a few pieces of information about your particular system before you can proceed. Take a moment to find these things, and jot them down for easy reference:

• What is your user name? Your user name is the name you chose for yourself when you first set up your computer. It's the same name used for your Home folder. To find your user name, launch System Preferences from the Dock, and view the Users preferences. Select your account from the list, and choose Edit. Your user name is the "short name" that appears in this dialog box (see Figure C.4). For purposes of the examples in this appendix, the user name will be fred. Whenever you see Fred or fred in an example, substitute your user name.

  Figure C.4. Finding your short user name in the System Preferences (Mac).

  

• Where is your Home folder? This is the folder that appears when you click the home icon in the Finder window toolbar. It's named the same as your user name (for instance, fred). On your hard drive, it's located in the Users folder. In UNIX-speak, its address is /users/fred.

• What is your computer's IP address? The IP address is your official address in the world. You'll need to reference it at various times. To find your IP address, launch System Preferences from the Dock and view the Internet preferences. The IP address will be listed there. Write it down!

• What, and where, is the Terminal? The Terminal is a utility program on your computer. It is command-line interface for working with all UNIX-style programs and functions on Mac OS X. Most of your MySQL activities will be done through the Terminal. You can find the Terminal in Applications/Utilities, on your hard drive. Figure C.5 shows the Terminal window, ready for action.

  Figure C.5. The Terminal window, showing the % prompt (Mac).

  

Note

If you're not willing to use the Terminal, you should set up your Dreamweaver workstation to network to a Windows computer or a computer at your ISP. See Chapter 26 for instructions on "Setting Up for Development on a Remote Computer."

Downloading

MySQL for Mac OS X is available from the MySQL web site (www.mysql.com). At the time of this writing, current downloads are available at http://www.mysql.com/ downloads/mysql-3.23.html. On this page, scroll down until you find the Mac OS X download section. Don't download the version for OS X Server; you want the version called MacOS X 10.1.1 (Darwin 5.1.x) (PowerPC).

Note

As always with software, MySQL is evolving. By the time you read this, the current version might have changed from 3.2310.1.1. Download the latest stable release. If you download a later version of MySQL than the one used here, every time you see instructions that involve typing the full name and version of the program, substitute your version number.

The downloaded file has the extension.tar.gz; this means it's a UNIX archive file, similar to a Zip or StuffIt archive. Depending on your browser configuration, StuffIt Expander might have automatically uncompressed the archive for you into a folder called mysql-3.23.47-apple-darwin5.1-powerpc. If this didn't happen, find StuffIt or StuffIt Expander and drag the archive on top of it to uncompress.

After you have the uncompressed folder, rename it mysql and move it to your Home folder (see Figure C.6).

Figure C.6. The mysql folder in Fred's Home folder (Mac).

Installing

You're probably not used to installing UNIX applications. Guess what? You've done the first part of the installation already, just by uncompressing the archive and moving it to the folder where you want it to live. All that's left is to run the install script.

Find and launch the Terminal. (It's in Applications > Utilities > Terminal.) In the Terminal window, type this (substituting your user name for Fred's):

cd /users/fred/mysql  ./scripts/mysql_install_db

Wait for the % prompt to reappear. Your MySQL database manager has been installed!

Note

Any UNIX gurus out there will recognize that this is a non-standard install method. It was developed to be user-friendly above all, for non-UNIX gurus. It will work fine, as long as you're not using your computer in a multi-user environment (that is, as long as you're the only person using your computer).

Configuring and Setting Up

To use MySQL, and in particular to use it with the exercises in this book, you'll need to configure it and set up the Antiques database. This section also covers how to start and stop the MySQL program every time you want to work with it.

1. Copying files can be done in the Terminal window but one of the benefits of installing MySQL in your Home folder is that you can access this folder from the Finder (as you saw earlier). Leaving the Terminal window open (you can hide it, or minimize it down into the Dock), use the Finder to browse through your Home folder. Inside the mysql folder, there's a data folder. That's where you need to put the folder containing the various files of the Antiques database. Figure C.7 shows this happening.

   Figure C.7. Putting the antiques folder into MySQL's data folder (Mac).

   

   1. On the CD, find the databases folder. Open that folder and the antiques_mysql folder within it. Inside there is another folder called antiques.

   2. Copy that entire folder to the mysql/data folder in your Home folder. Don't rename it or disturb any of its contents.

2. Go back to the Terminal. To launch the server, type this in the Terminal window:

   ~/users/fred/mysql/bin/safe_mysqld --user=fred &

   (Remember to substitute your name for Fred's!) To test that the server launched, type this:

   ~/users/fred/mysql/bin/mysqladmin ping

   If the server is running, you'll get a response message that says mysqld is alive. If it's not running, you'll get an error message. You'll need to troubleshoot this (try re-typing the preceding command) and get the server to launch before you can continue.

3. Now you need to launch the MySQL client program (the one that you actually manipulate database information with). Type this:

   ~/users/fred/mysql/bin/mysql --user=root

   The prompt will change from the % prompt to simply mysql>. That's how you know you're in the program.

4. Now you need to tell the server who will be using your databases, and how much access they should have. With the mysql> prompt still showing, type this:

   GRANT all privileges on antiques.* to ""@localhost;

   Note

   

   Permissions will be a big factor in whether you succeed or fail at installing and working with MySQL on Mac OS X. In UNIX, everybody is locked out of everything unless you specifically start unlocking things for certain people. If you don't unlock things correctly, Dreamweaver and the browser won't be able to access your data and you won't be able to continue.

   By launching the mysql client program with the special --user=root addition to your code, you're launching the mysql client program as root, which is an administrative user. You have to do this because only the administrator can assign permissions. After you've given everybody on your computer permission to access a particular database, you can launch the program without that last bit of typing to work on that database.

   Then check your notes and find the IP address for your computer, and type:

   GRANT all privileges on antiques.* to ""@192.168.123.123;

   You've just given anybody on your computer permission to access the Antiques database. You did it in two different ways, because different application servers sometimes require different access methods.

5. When you have finished working in MySQL, you quit the program by just typing quit;

The program will tell you good-bye, and you're back to the % prompt. Note that this quits only the client program, not the server; and that's the way you want it. The server portion (mysqld) must be up and running as you work on your Dreamweaver files. Unless you specifically shut the server down, it will stay running until you shut down your computer.

Note

You'll need to add permissions like this for every new database you create. If you want to just grant permissions once and for all, instead of specifying antiques.*, specify *.* note that this is a security risk if your computer is on a network or if you're connected to the Internet (especially if you have an always-on connection like such as a cable modem or DSL). Enterprising hackers now have access to even the administrative tables of your MySQL installation.

Restarting the Server

The next time you start up your Mac and are ready to work on some live data pages, you'll need to re-start the server. Do just what you did in step 2: Launch the Terminal and type ~/users/fred/mysql/bin/safe_mysqld --user=fred &.

Getting Around in MySQL

As long as you never have to edit a database, you don't have to know much more about working in MySQL than what has already been covered in this appendix. However, you might find it handy to know at least the basics of working with databases in the MySQL client program.

MySQL works in the command-line interface. You type in SQL commands to edit the database. (This is actually a great way to learn how to write SQL statements.) After you've got the client program launched in your Command Prompt or Terminal window, and the mysql> prompt is showing, you can use some basic commands to examine and edit your database. Table C.1 shows some of these basic commands you can use to examine and edit your database. (The sample code shows the Antiques database being used.)

Table C.1. Commands for Common Operations in MySQL

To Do This:	Type This (Keywords Are Shown in All Caps):
Open a database for use. (Do this before doing anything else.)	USE antiques;
See a list of tables in the current database.	SHOW tables;
See a list of the columns in a particular table (for instance, the Customers table).	DESCRIBE customers;
See all the contents of a table.	SELECT * FROM customers;
See the contents of only one column in the table (all customers' last names, for instance).	SELECT lname FROM customers;
Find a particular record (or records) meeting a certain criteria, in the table.	SELECT * FROM customers WHERE fname="Fred";
Insert a new record (row) into a table. The data supplied must match the number and type of columns in the table.	INSERT INTO customers VALUES ("George","Flintstone");
Change a record in a table.	UPDATE customers SET fname="Fred" WHERE lname="Flintstone";

A few tips about entering commands into MySQL:

• It's conventional to put keywords in all caps, but it's not necessary; MySQL is not case-sensitive.

• All commands must end with a semicolon (;). If you end a line by pressing Enter/Return, but without typing a semicolon, MySQL thinks you have not finished entering the command and will patiently wait for you to finish. You can take advantage of this to type long command statements on multiple lines, for easier reading.

If you really, really hate command-line interfaces, but still want to use MySQL, a few free GUIs are available at www.mysql.com.

Resources and Further Reading

A lot of information is available to you, if you want to learn more about installing and using MySQL. The best place to begin is http://www.mysql.com/documentation/ index.html the extensive online documents cover all aspects of the program from installation to command lines, along with a very nice beginner's tutorial to get you started. Mac OS X users can also check out Marc Liyanage's OS X software resources at http://www.entropy.ch/software/macosx/. There are also various user forums onMySQL, PHP, and related issues. A good place to begin is http://www. sourceforge.net/.

If you like to do your learning from books, try Paul DuBois's excellent MySQL (published by New Riders).

CONTENTS