Fedora™ Unleashed, 2008 edition

NOTE

You should consider access and permission issues when setting up a database. Should users be able to create and destroy databases? Or should they only be able to use existing databases? Will users be able to add records to the database and modify existing records? Or should users be limited to read-only access to the database? And what about the rest of the world? Will the general public need to have any kind of access to your database through the Internet? As DBA, you must determine the answers to these questions.

SSH Access to a Database

Two types of remote database access scenarios are briefly discussed in this section. In the first scenario, the user directly logs in to the database server through SSH (to take advantage of the security benefits of encrypted sessions) and then starts a program on the server to access the database. In this case, shown in Figure 18.4, the database client is running on the database server itself.

FIGURE 18.4 The user logs in to the database server located on host simba from the workstation (host cheetah). The database client is running on simba.

In the other scenario, shown in Figure 18.5, the user logs in to a remote host through SSH and starts a program on it to access the database, but the database is actually running on a different system. Three systems are now involved: the user's workstation, the remote host running the database client, and the remote host running the database server.

FIGURE 18.5 The user logs in to the remote host leopard from the workstation (host cheetah) and starts a database client on leopard. The client on leopard then connects to the database server running on host simba. The database client is running on leopard.

The important thing to note in Figure 18.5 is the middleman system leopard. Although the client is no longer running on the database server itself, it isn't running on the user's local workstation, either.

Local GUI Client Access to a Database

A user can log in to the database server by using a graphical client (which could be running on Windows, Macintosh OS, or a Unix workstation). The graphical client then connects to the database server. In this case, the client is running on the user's workstation. Figure 18.6 shows an example.

FIGURE 18.6 The user starts a GUI database program on his workstation (hostname cheetah). This program, which is the database client, then connects to the database server running on the host lion.

Web Access to a Database

In this section, we look at two basic examples of web access to the database server. In the first example, a user accesses the database through a form located on the World Wide Web. At first glance, it might appear that the client is running on the user's workstation. Of course, in reality it is not; the client is actually running on the web server. The web browser on the user's workstation simply provides a way for the user to enter the data that he wants to send to the database and a way for the results sent from the database to be displayed to the user. The software that actually handles sending the request to the database is running on the web server in the form of a CGI script; a Java servlet; or embedded scripting such as the PHP or Sun Microsystems, Inc.'s JavaServer Pages (JSP).

Often, the terms client and front end are used interchangeably when speaking of database structures. However, Figure 18.7 shows an example of a form of access in which the client and the front end aren't the same thing at all. In this example, the front end is the form displayed in the user's web browser. In such cases, the client is referred to as middleware.

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

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

mysql [options] [database]

Some of the available options for mysql are 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

Option	Action
-h hostname	Connects to the remote host hostname (if the database server isn't located on the local system).
-u username	Connects to the database as the user username.
-p	Prompts for a password. This option is required if the user as whom you are