Andrew Hudson - Fedora™ Unleashed, 2008 edition

FIGURE 18.7 The user accesses the database through the World Wide Web. The front end is the user's web browser, the client is running on leopard, and the server is running on simba.

In another possible web access scenario, it could be said that the client is a two-piece application in which part of it is running on the user's workstation and the other part is running on the web server. For example, the database programmer can use JavaScript in the web form to ensure that the user has entered a valid query. In this case, the user's query is partially processed on her own workstation and partially on the web server. Error checking is done on the user's own workstation, which helps reduce the load on the server and also helps reduce network traffic because the query is checked for errors before being sent across the network to the server.

The MySQL command-line client is mysql, and it has the following syntax:

mysql [ options ] [ database ]

Some of the available options for mysqlare discussed in Table 18.1. database is optional, and if given, it should be the name of the database to which you want to connect.

TABLE 18.1 Command-Line Options to Use When Invoking mysql

More options are available than are listed in Table 18.1, but these are the most common options. See the man page for mysqlfor more information on the available options.

Although mysqlenables you to specify the password on the command line after the -poption, and thus enables you to avoid having to type the password at the prompt, you should never invoke the client this way. Doing so causes your password to display in the process list, and the process list can be accessed by any user on the system. This is a major security hole, so you should never give your password on the mysqlcommand line.

You can access the MySQL server without specifying a database to use. After you log in, you use the helpcommand to get a list of available commands, like this:

mysql> help

MySQL commands:

Note that all text commands must be first on line and end with ';'

help (\h) Display this help.

? (\?) Synonym for `help'.

clear (\c) Clear command.

connect (\r) Reconnect to the server. Optional arguments are db and host.

edit (\e) Edit command with $EDITOR.

ego (\G) Send command to mysql server, display result vertically.

exit (\q) Exit mysql. Same as quit.

go (\g) Send command to mysql server.

nopager (\n) Disable pager, print to stdout.

notee (\t) Don't write into outfile.

pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.

print (\p) Print current command.

quit (\q) Quit mysql.

rehash (\#) Rebuild completion hash.

source (\.) Execute a SQL script file. Takes a file name as an argument.

status (\s) Get status information from the server.

tee (\T) Set outfile [to_outfile]. Append everything into given outfile.

use (\u) Use another database. Takes database name as argument.

You can then access a database by using the usecommand and the name of a database that has been created (such as animals) and to which you are authorized to connect, like this:

mysql> use animals

Database changed

mysql>

You invoke the PostgreSQL command-line client with the command psql. Like mysql, psqlcan be invoked with the name of the database to which you would like to connect. Also like mysql, psqlcan take several options. These options are listed in Table 18.2.

TABLE 18.2 Command-Line Options to Use When Invoking psql

Several more options are available in addition to those listed in Table 18.2. See the psql's man page for details on all the available options.

If you'd rather interact with a database by using a graphical database client than with the command-line clients discussed in the previous section, you're in luck: A few options are available.

MySQL has an official graphical client, called MySQLGUI. MySQLGUI is available in both source and binary formats from the MySQL website at http://www.mysql.com/.

Web-based administration interfaces are also available for MySQL and PostgreSQL. phpMyAdmin and phpPgAdmin are two such products. Both of these products are based on the PHP-embedded scripting language and therefore require you to have PHP installed. Of course, you also need to have a web server installed.

The following commands are useful for creating and manipulating databases in Fedora:

► createdb — Creates a new PostgreSQL database

► createuser — Creates a new PostgreSQL user account

► dropdb — Deletes a PostgreSQL database

► dropuser — Deletes a PostgreSQL user account

► mysql — Interactively queries the mysqldserver

► mysqladmin — Administers the mysqldserver

► mysqldump — Dumps or backs up MySQL data or tables

► pgaccess — Accesses a PostgreSQL database server