Database Clients


Both MySQL and PostgreSQL use a client/server system for accessing databases. In the simplest terms, the database server handles the requests that come into the database and the database client handles getting the requests to the server as well as getting the output from the server to the user.

Users never interact directly with the database server even if it happens to be located on the same machine they are using. All requests to the database server are handled by a database client, which might or might not be running on the same machine as the database server.

Both MySQL and PostgreSQL have command-line clients. A command-line client is a very primitive way of interfacing with a database and generally isn't used by end users. As a DBA, however, you use the command-line client to test new queries interactively without having to write front-end programs for that purpose. In later sections of this chapter, you will learn a bit about the MySQL graphical client and the web-based database administration interfaces available for both MySQL and PostgreSQL.

The following sections examine two common methods of accessing a remote database, a method of local access to a database server, and the concept of web access to a database.

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 22.4, the database client is running on the database server itself.

Figure 22.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 22.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 22.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 22.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 22.6 shows an example.

Figure 22.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 22.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 22.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 22.1. database is optional, and if given, it should be the name of the database to which you want to connect.

Table 22.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 you are connecting as needs a password to access the database. Note that this is a lowercase p.

-P n

Specifies n as the number of the port that the client should connect to. Note that this is an uppercase P.

-?

Displays a help message.


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

Caution

Although mysql allows you to specify the password on the command line after the -p option, and thus allows 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 mysql command line.


You can access the MySQL server without specifying a database to use. After you log in, you use the help command 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 use command and the name of a database that has been created (such as animals) and that you are authorized to connect to, like this:

mysql> use animals Database changed mysql>


The PostgreSQL Command-Line Client

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

Table 22.2. Command-Line Options to Use When Invoking psql

Option

Action

-h hostname

Connects to the remote host hostname (if the database server isn't located on the local system).

-p n

Specifies n as the number of the port that the client should connect to. Note that this is a lowercase p.

-U username

Connects to the database as the user username.

-W

Prompts for a password after connecting to the database. In PostgreSQL 7 and later, password prompting is automatic if the server requests a password after a connection has been established.

-?

Displays a help message.


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

Graphical Clients

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.

Related Fedora and Database Commands

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 mysqld server

mysqladmin Administers the mysqld server

mysqldump Dumps or backs up MySQL data or tables

pgaccess Accesses a PostgreSQL database server

pg_ctl Controls a PostgreSQL server or queries its status

psql Accesses PostgreSQL via an interactive terminal




Red Hat Fedora 5 Unleashed
Red Hat Fedora 5 Unleashed
ISBN: 067232847X
EAN: 2147483647
Year: 2004
Pages: 362

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net