10.2 Choosing the Right Client

This section describes general capabilities of important administrative MySQL client programs. Each of these programs can be invoked with the --help option to display a help message that shows the command syntax and the other options that the program supports.

In some cases, you might be able to choose from among different clients to accomplish a given task. By understanding the purpose and capabilities of each client, you can better get your work done by selecting an appropriate client for particular tasks.

In general, you perform most administrative operations using either mysqladmin, mysql, or MySQLCC.

10.2.1 The mysqladmin Client

The mysqladmin client is designed specifically for administrative operations. These include (but are not limited to) the following:

• "Ping" the server to see whether it's running and accepting client connections

• Shut down the server

• Create and drop databases

• Display server configuration and version information

• Display or reset server status variables

• Set passwords

• Reload the grant tables

• Flush the log files or various server caches

• Start or stop replication slave servers

• Show information about client connections or kill connections

For a full list of mysqladmin capabilities, invoke the program with the --help option.

mysqladmin accepts multiple commands on the same command line. For example, the following command displays a brief status message, followed by the list of server system variables:

shell> mysqladmin status variables

Some mysqladmin commands are available only to MySQL accounts that have administrative privileges. For example, to shut down the server, it's necessary to connect using an administrative account such as root that has the SHUTDOWN privilege:

shell> mysqladmin -u root -p shutdown

10.2.2 The mysql Client

The mysql client sends SQL statements to the server, and thus can perform any operation that can be expressed using SQL. Some examples are

• Create and drop databases

• Create, drop, and modify tables and indexes

• Retrieve data from tables

• Modify data in tables

• Set up user accounts, grant and revoke privileges, and set passwords

• Display server configuration and version information

• Display or reset server status variables

• Reload the grant tables

• Flush the log files or various server caches

• Start or stop replication slave servers

• Show information about client connections or kill connections

You can use mysql in interactive mode, where you type in queries and see their results. mysql also can operate in batch mode, in which it reads queries stored in a text file. The mysql program is covered extensively in the "Core Study Guide."

10.2.3 The MySQLCC Client

MySQL Control Center (MySQLCC) is a graphical client with capabilities that are something like those of mysql and mysqladmin combined. MySQLCC can send SQL statements to the server, so it can do pretty much anything that mysql can do. MySQLCC also can perform administrative operations such as pinging or shutting down the server, which mysqladmin can do but mysql cannot. MySQLCC is available in precompiled form for Windows and Linux, or it can be compiled from source.

MySQLCC requires a graphical environment such as Windows or the X Window System. However, if the MySQL server is running on a host with no graphical environment, you can connect to it remotely by running MySQLCC on a client host that does have a graphical environment.

10.2.4 Other Administrative Clients

The mysqladmin, mysql, and MySQLCC programs just described are multipurpose programs. Other MySQL clients have more specialized administrative capabilities:

• mysqlcheck checks, repairs, analyzes, and optimizes tables. It can perform all these operations on MyISAM tables, and can perform some of them on InnoDB and BDB tables. It provides a command-line interface to the various SQL statements (such as CHECK TABLE and REPAIR TABLE) that instruct the server to perform these operations.

• mysqlimport provides a command-line interface to the LOAD DATA INFILE statement. It is used to load datafiles into tables without having to issue the statement yourself.

• mysqldump dumps the contents of databases and tables. It's useful for making backups or for copying databases to another machine.

• mysqlshow produces information about the structure of your databases and tables. It provides a command-line interface to various forms of the SHOW statement that list the names of your databases, tables within a database, or information about table columns or indexes.

The "Core Study Guide" provides more detail on the general use of mysqlcheck, mysqlimport, and mysqldump. The capabilities of mysqlshow are illustrated by several of the exercises at the end of this chapter.

10.2.5 Client Program Limitations

No client program does everything. It's important to understand what each client can do, but you should also know what they cannot do. For example:

• mysqladmin can create or drop databases, but it has no capabilities for creating or dropping individual tables or indexes. It can change passwords, but cannot create or delete user accounts. The mysql and MySQLCC programs can perform all of these operations.

• mysqlimport loads datafiles, but it cannot load SQL-format dump files containing INSERT statements produced by mysqldump. In this sense, mysqlimport is not the complement of mysqldump. Dump files containing SQL statements should be processed using mysql instead.

• None of the client programs can start the server. You invoke the server directly or by using a startup script, or you can arrange to have the operating system invoke the server as part of its system startup procedure. Server startup procedures are discussed in Chapter 11, "MySQL Installation and Configuration."

• None of the clients in the preceding discussion can shut down the server except mysqladmin and MySQLCC. For example, mysql cannot do so because it sends SQL statements to the server, and there is no "shut down" SQL statement. mysqladmin and MySQLCC shut down the server by using a special non-SQL capability of the client/server protocol. If you use an account that has the SHUTDOWN privilege, either program can shut down local or remote servers.

10.2.6 The perror Utility

perror is a handy utility that is installed with the MySQL server. The purpose of the perror program is to give you a textual representation of the error codes used by MySQL when operating system-level errors occur.

You can use perror in situations when a query results in a message such as the following being returned to you:

mysql> CREATE TABLE CountryCopy SELECT * FROM Country;

ERROR 1: Can't create/write to file './world/CountryCopy.frm' (Errcode: 13)

This error message indicates that MySQL cannot write to the file CountryCopy.frm, but does not report the reason. It might be due to a full disk, a permissions problem, or some other error.

Running the perror program results in a message indicating that the source of the problem is that someone has set the permissions incorrectly for the current database:

shell> perror 13

Error code  13:  Permission denied