Using MySQL Client Programs

This chapter is from the book

DUBOIS:MYSQL CERTIFICATION S/G _p1

Learn More Buy

This chapter is from the book

This chapter is from the book 

DUBOIS:MYSQL CERTIFICATION S/G _p1

Learn More Buy

This chapter covers the following exam topics:

• Invoking command-line client programs

• Specifying command-line options

• The mysql client

• Using mysql interactively

• Using script files with mysql

• mysql client commands and SQL statements

• Using the ---safe-updates option

• Using mysqlimport

• Using mysqldump and reloading the dump

• Checking tables with mysqlcheck and myisamchk

• Using MySQLCC

• Using MySQL Connector/ODBC and MySQL Connector/J

Questions on the material in this chapter make up approximately 10% of the exam.

This chapter discusses general principles that are common to most MySQL client programs. It also describes how to use several specific types of clients:

• The interactive graphical client MySQLCC (MySQL Control Center). This general-purpose client provides a graphical interface to the MySQL server. It can be thought of as an extended graphical version of the character-based mysql client.

MySQLCC is still being actively developed at the time of publication of the first Core exam. For that reason, you are expected to be familiar with the general properties and capabilities of MySQLCC, but you are not expected to know all the ins and outs of the program.

• The character-based client programs. These run either interactively or perform their job based on command-line arguments with no further input from the user. Character-based clients discussed in this chapter include:

• mysql, a general-purpose client for issuing queries and retrieving their results. It can be used interactively or in batch mode to read queries from a file.

• n mysqladmin, an administrative client that helps you manage the server.

• mysqlimport, a program for importing text files into databases. It provides a command-line interface to the LOAD DATA INFILE SQL statement.

• mysqldump, a program for dumping the contents of databases. It can be used to make database backups or to copy databases to other machines.

• mysqlcheck and myisamchk, programs for checking and repairing tables. They're useful for checking and maintaining the integrity of certain types of tables.

• The MySQL Connector drivers. These drivers provide connectivity to the MySQL server for client programs:

• MySQL Connector/ODBC, the MySQL driver for programs that use the ODBC (Open Database Connectivity) interface.

• MySQL Connector/J, the MySQL driver for JDBC connectivity to Java programs.

3.1 Invoking Command-Line Client Programs

MySQL client programs can be invoked from the command line; for example, from a Windows console prompt or from a Unix shell prompt. When you invoke a client program, you can specify options to control its behavior. Some options tell the client how to connect to the MySQL server. Other options tell the program what actions to perform.

This section discusses the following option-related topics:

• The general syntax for specifying options

• How to use connection parameter options

• How to specify options in an option file

Most examples in this section use the mysql program, but the general principles apply to other MySQL client programs as well.

To determine the options supported by any MySQL program, invoke the program with the --help option. For example, to find out how to use mysql, use this command:

shell> mysql --help

To determine the version of a program, use the --version option. For example:

shell> mysql --version
mysql Ver 12.22 Distrib 4.0.18, for apple-darwin7.2.0 (powerpc)

This output indicates that the mysql client is from MySQL version 4.0.18.

3.1.1 Specifying Command-Line Options

Typically, you invoke MySQL client programs with options that indicate to the program what you want it to do. This section describes the general syntax for specifying options, as well as some of the options that are common to most MySQL clients.

3.1.1.1 Command Option Syntax

Options to MySQL programs have two general forms:

• Long options consist of a word preceded by double dashes.

• Short options consist of a single letter preceded by a single dash.

In many cases, a given option has both a long and a short form. For example, to display a program's version number, you can use the long --version option or the short -V option. These two commands are equivalent:

shell> mysql --version
shell> mysql -V

Options are case sensitive. --version is recognized by MySQL programs, but lettercase variations such as --Version or --VERSION are not. This applies to short options as well. -V and -v are both legal options, but mean different things.

Some options are followed by values. For example, when you specify the --host or -h option to indicate the host machine where the MySQL server is running, you must follow the option with the machine's hostname. For a long option, separate the option and the value by an equal sign (=). For short options, the option and the value can be, but need not be, separated by a space. The following three option formats are equivalent; each one specifies myhost.example.com as the host machine where the MySQL server is running:

--host=myhost.example.com
-h myhost.example.com
-hmyhost.example.com

In most cases, if you don't specify an option explicitly, a program will use a default value. Default values make it easier to invoke MySQL client programs because you need specify only those options for which the defaults are unsuitable. For example, the default server hostname is localhost, so if the MySQL server to which you want to connect is running on the local host, you need not specify --host or -h at all.

Exceptions to these option syntax rules are noted in the following discussion wherever relevant. The most important exception is that password options have a slightly different behavior than other options.

3.1.1.2 Connection Parameter Options

To connect to a server using a client program, the client must know upon which host the server is running. A connection may be established locally to a server running on the same host as the client program, or remotely to a server running on a different host. To connect, you must also identify yourself to the server with a username and password.

Each MySQL client has its own program-specific options, but all clients support a common set of options for making a connection to the MySQL server. This section describes the options that specify connection parameters, and how to use them if the default values aren't appropriate.

For command-line clients, all connection parameters are specified after the command name. The following discussion lists each option's long form and short form, as well as its default value. (You'll need to specify connection parameters for other types of client programs as well, such as the graphical MySQLCC client. Such a client might allow you to specify connection parameters on the command line, but might also provide an additional method of allowing you to indicate them, such as a dialog box.)

There are three options that indicate to the client where the server is running, as well as the type of connection to establish:

• --host=host_name or -h host_name

This option specifies the machine where the MySQL server is running. The value can be a hostname or an IP number. The hostname localhost means the local host (that is, the computer on which you're running the client program). On Unix, localhost is treated in a special manner. On Windows, the value . also means the local host and is treated in a special manner as well. For a description of this special treatment, refer to the discussion of the --socket option.

• The default host value is localhost.

• --port=port_number or -P port_number

This option indicates the port number to which to connect on the server host; it applies only to TCP/IP connections. TCP/IP is used unless you connect using a hostname value of . on Windows or localhost on Unix.

The default MySQL port number is 3306.

• --socket=socket_name or -S socket_name

This option's name comes from its original use for specifying a Unix domain socket file. On Unix, for a connection to the host localhost, a client connects to the server using a Unix socket file. This option specifies the pathname of that file.

On Windows, the --socket option is used for specifying a named pipe. For Windows NT-based systems that support named pipes, a client can connect using a pipe by specifying . as the hostname. In this case, --socket specifies the name of the pipe. Pipe names aren't case sensitive. (Note that NT-specific MySQL servers don't enable named pipe connections by default; the server must be started with the --enable-named-pipe option.)

If this option is omitted, the default Unix socket file pathname is /tmp/mysql.sock and the default Windows pipe name is MySQL.

Two options provide identification information. These are the username and password of the account that you want to use for accessing the server. The server will reject a connection attempt unless you provide values for these parameters that correspond to an account that the server recognizes:

• --user=user_name or -u user_name

This option specifies the username for your MySQL account. To determine which account applies, the server uses the username value in conjunction with the name of the host from which you connect. This means that there can be different accounts with the same username, which can be used for connections from different hosts.

On Unix, client programs use your system login name as your default MySQL account username. On Windows, the default MySQL account name is ODBC.

• --password=pass_value or -ppass_value

This option specifies the password for your MySQL account. There is no default password. If you omit this option, your MySQL account must be set up to allow you to connect without a password.

MySQL accounts are set up using the GRANT statement, which is discussed in the "Professional Study Guide."

Password options are special in two ways, compared to the other connection parameter options:

• You can omit the password value after the option name. This differs from the other connection parameter options, each of which requires a value after the option name. If you omit the password value, the client program will prompt you interactively for a password, as shown here:

shell> mysql -p
Enter password:

When you see the Enter password: prompt, type in your password and press Enter. The password isn't echoed as you type, to prevent other people from seeing it.

• If you use the short form of the password option (-p) and give the password value on the command line, there must be no space between the -p and the value. That is, -ppass_val is correct, but -p pass_val is not. This differs from the short form for other connection parameter options, where a space is allowed between the option and its value. (For example, -hhost_name and -h host_name are both valid.) This exceptional requirement that there be no space between -p and the password value is a logical necessity of allowing the option parameter to be omitted.

Another option that affects how the connection setup occurs is --compress (or -C). This option causes data sent between the client and the server to be compressed before transmission and uncompressed upon receipt. The result is a reduction in the number of bytes sent over the connection, which can be helpful on slow networks. The cost is additional computational overhead for both the client and server to perform compression and uncompression. --compress and -C take no value after the option name.

Here are some examples that show how to specify connection parameters:

• Connect to the server using the default hostname and username values with no password:

shell> mysql

• Connect to the server on the local host with a username of myname, asking mysql to prompt you for a password:

shell> mysql --host=localhost --password --user=myname

• Connect with the same options as the previous example, but using the corresponding short option forms:

shell> mysql -h localhost -p -u myname

• Connect to the server at a specific IP address, with a username of myname and password of mypass:

shell> mysql --host=192.168.1.33 --user=myname --password=mypass

• Connect to the server on the local host, using the default username and password and compressing client/server traffic:

shell> mysql --host=localhost --compress

3.1.1.3 Using Option Files

As an alternative to specifying options on the command line, you can place them in an option file. The standard MySQL client programs look for option files at startup time and use any appropriate options they find there. Putting an option in an option file saves you time: You need not specify the option on the command line each time you invoke a program.

Options in option files are organized into groups, with each group preceded by a [group-name] line that names the group. Typically, the group name is the name of the program to which the group of options applies. For example, the [mysql] and [mysqldump] groups are for options to be used by mysql and mysqldump, respectively. The special [client] group can be used to specify options that you want all client programs to use. A common use for the [client] group is to specify connection parameters.

To write an option in an option file, use the long option format that you would use on the command line, but omit the leading dashes. Here's a sample option file:

[client]
host = myhost.example.com
compress


[mysql]
safe-updates

In this example, the [client] group specifies the server hostname and indicates that the client/server protocol should use compression for traffic sent over the network. Options in this group apply to all standard clients. The [mysql] group applies only to the mysql program. The group shown indicates that mysql should use the --safe-updates option.

Note that if an option takes a value, spaces are allowed around the = sign, something that isn't true for options specified on the command line.

Where an option file should be located depends on your operating system. The standard option files are as follows:

• On Windows, programs use the my.ini file in the Windows directory and the C:\my.cnf file.

• On Unix, the file /etc/my.cnf serves as a global option file used by all users. You can set up your own option file by creating a file named .my.cnf in your home directory.

To use an option file, create it as a plain text file using an editor. Client programs can access options from multiple option files, if they exist. It isn't an error for an option file to be missing.

To create or modify an option file, you must have write permission for it. Client programs need only read access.

To tell a program to read a specific option file instead of the standard option files, use the --defaults-file option. For example, to use the file C:\my-opts for mysql on Windows, invoke the program like this:

shell> mysql --defaults-file=C:\my-opts

If you use --defaults-file, it must be the first option after the command name.

If a program finds that an option is specified multiple times, either in the same option file or in multiple option files, the option value that occurs last takes precedence. Options specified on the command line take precedence over options found in option files.

3.1.2 Selecting a Default Database

For most client programs, you must specify a database so that the program knows where to find the tables that you want to use. The conventional way to do this is to name the database on the command line following any options. For example, to dump the contents of the world database to an output file named world.sql, you might run mysqldump like this:

shell> mysqldump --password --user=user_name world > world.sql

For the mysql client, a database name on the command line is optional. This is because you can explicitly indicate the database name for any table when you issue queries. For example, the following statement selects rows from the table Country in the world database:

mysql> SELECT * FROM world.Country;

To select or change the default database while running mysql, issue a USE db_name statement, where db_name is the name of the database you'd like to use. For example, the following statement makes world the default database:

mysql> USE world;

The advantage of selecting a default database with USE is that in subsequent queries you can refer to tables in that database without having to specify the database name. For example, with world selected as the default database, the following SELECT statements are equivalent, but the second is easier to write because the table name doesn't need to be qualified with the database name:

mysql> SELECT * FROM world.Country;
mysql> SELECT * FROM Country;

The default database is sometimes called the current database.