Understanding MySQL Command-Line Client Programs

The post describe the available clients for administrative tasks. Command-Line client programs available for MySQL are as shown in the table below:

Prompt	Description
mysql	Sends SQL statements to the server
mysqladmin	Assists in managing the server
mysqlimport	Loads data files into tables
mysqldump	Backs up the contents of databases and tables

The mysqld server program must be running for clients to gain access to databases. mysql is a general-purpose command-line client for sending SQL statements to the server, including those of an administrative nature. mysqladmin is an administrative command-line client that assists in managing the server. mysqlimport provides a command-line interface to the ‘LOAD DATA INFILE’ statement. It is used to load data files into tables without having to issue LOAD DATA INFILE statements manually. mysqldump is a command-line client for dumping the contents of databases and tables. It is useful for making backups or for copying databases to other machines.

The list in the table above includes only a few of the command-line programs in a MySQL installation. For more information about command-line client programs, see the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/programs-client.html
http://dev.mysql.com/doc/mysql/en/programs-admin-utils.html

Invoking Command-Line Clients

You control the behavior of client programs by specifying options following the program name. Find out how to use the mysql client:

shell> mysql --help
...
-?, --help         Display this help and exit.
-I, --help          Synonym for -?
...
--character-sets-dir=name  Directory for character set files.
--column-type-info  Display column type information.
-c, --comments      Preserve comments. Send comments to the server. The default
                    is --skip-comments (discard comments), enable with --comments
-C, --compress      Use compression in server/client protocol.
...

Determine the version of the MySQL distribution that you are running:

shell] mysql –V
mysql  Ver 14.14 Distrib 5.6.10, for Linux (x86_64) using  EditLine wrapper

Two general forms of option syntax:

– Long option (--[option])
– Short option (-[option])

The client programs that you run do not have to be the same version as the server.

Connection Parameter Options

We can connect with the MySQL server either locally, to server running on same host or Remotely, to server running on different host. These are a few of the most common connection parameter options:

-h:Followed by the name or IP address of a given host, to connect to the server (the default is local host)
-C:Compresses all information sent between the client and the server if both support compression
–protocol:Followed by the connection protocol to use for connecting to the server: {TCP|SOCKET|PIPE|MEMORY}
-P:Followed by a port number to use instead of the default (3306)
-S:Sets UNIX socket file, or the name of the named pipe to use on Windows
–shared-memory-base-name:(Windows only) The shared-memory name to use for connections that are made using shared memory to a local server. This option applies only if the server supports shared-memory connections.

Examples of how you can use some of these options:

mysql -h 192.168.1.101 -P 3351 -u root -p
mysql --host=localhost --compress
mysql --host=localhost -S /var/lib/mysql/mysql.sock
mysql --protocol=TCP

Invoking mysql Client

Below are some examples of invoking the mysql client:

1. Provide credentials on the command line (-u[name] (or–username=[name]): With or without a space after the option):

shell> mysql -u[name] -p[password]

-p[password](or–password=[password): Without a space after the option. When you use the option without a value, you are prompted for the password. You can also put it in an option file instead of on the command line, or provide credentials in a login path.

2. Provide credentials in a login path - –login-path=[login-path]: Use the credentials for this login path as created with mysql_config_editor.

shell> mysql --login-path=[login-path]

3. Execute a statement:

shell> mysql --login-path= -e ""

-e “[statement]” (or–execute="[statement]"): Invokes the mysql client and then executes the SQL statement. For example, to see the current server version:

shell> mysql --login-path=admin -e "SELECT VERSION()"
+-------------------------------------------+
| VERSION()                                 |
+-------------------------------------------+
| 5.6.10-enterprise-commercial-advanced-log |
+-------------------------------------------+

Redirect output to a file by adding > [file_name].

4. Execute using a specific option file:

shell> mysql --defaults-file=[opt_file_name] ...

5. Execute using a text file containing SQL statements:

shell> mysql ... < [file_name.sql]

Note: File must be in plain text format with a statement terminator for each statement. File must be located on the host that is running the mysql client. < and -e are mutually exclusive. They cannot be used together.

mysql Client: Safe Updates

You can inadvertently issue statements that modify many rows in a table or statements that return extremely large result sets. The –safe-updates option helps prevent these problems. Add the ‘–safe-updates’ option when invoking the client:

shell] mysql ... --safe-updates

Set safe updates mode to impose the following SQL statement restrictions:

UPDATE and DELETE are allowed only if they include a WHERE clause that specifically identifies which records to update or delete by means of a key value, or if they include a LIMITclause.
Output from single-table SELECT statements is restricted to no more than 1,000 rows unless the statement includes a LIMIT clause.
Multiple-table SELECT statements are allowed only if MySQL examines no more than 1,000,000 rows to process the query.

mysql Client: Output Formats

By default, mysql produces output whether it is used in interactive or batch mode:

Interactive

When you invoke mysql interactively, it displays query output in a tabular format that uses bars and dashes to display values lined up in boxed columns.

–table (or -t): Produces tabular output format even when running in batch mode. This is the default format for interactive mode.

Batch

When you invoke mysql with a file as its input source on the command line, it runs in batch mode with query output displayed using tab characters between data values.

–batch (or -B): Produces batch mode (tab-delimited) output, even when running interactively, and does not use history file. This is the default format for batch mode. In batch mode, use the –raw or -r options to suppress conversion of characters such as newline and carriage return to escape sequences such as \n or \r. In raw mode, the characters are printed literally. Use these options to select an output format that is different from either of the default formats:

–html (or -H): Produces output in HTML format
–xml (or -X): Produces output in XML format

mysql Client: MySQL Client Commands

Using interactive mode for typical day-to-day usage, you can submit commands that display information, as in this example:

mysql> HELP
...
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
...

You can display specific information about the current client session:

mysql> STATUS
mysql  Ver 14.14 5.6.8, for Linux (x86_64) using  EditLine wrapper
Connection id:          8
Current database:       world_innodb
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version: 5.6.8-enterprise-commercial-advanced-log MySQL Enterprise
Server - Advanced Edition (Commercial)
...

You can also log session queries and their output:

mysql> tee my_tee_file.txt

mysql Client: SQL Statements

If you issue an SQL statement on a system running mysql, the client sends this statement to the MySQL server and the server executes the statement and returns the results. **Data Definition Language (DDL) **Some common SQL statements:

CREATE DATABASE/TABLE:Creates a database or table with the given name
ALTER DATABASE/TABLE:Can change the overall characteristics of a database or table
DROP DATABASE/TABLE:Drops all tables in the database and deletes the database, or drops a specific table

**Data Manipulation Language (DML) **Some common SQL statements:

SELECT:Retrieves rows selected from one or more tables
INSERT:Inserts new rows into an existing table • DELETE:Deletes rows from an existing table
UPDATE:Updates columns of existing rows in the named table with new values
JOIN:Combines tables as part of SELECT, multiple-table DELETE and UPDATE statements

mysql Client: Help on SQL Statements

You can access server-side help within the mysql client. Server-side help performs lookups in the MySQL Reference Manual for a particular topic, directly from the mysql> prompt. Use HELPfollowed by a keyword to access information. To display the top-most entries of the help system, use the CONTENTS keyword.

You do not have to step through the items listed in the contents list to get help on a specific subject. Just give a topic as the keyword to get some hints. For example, HELP STATUS results in a list of status-related SQL statements:

mysql> HELP STATUS
...
SHOW
SHOW ENGINE
SHOW MASTER STATUS
...

To view full SQL category list:

mysql> HELP CONTENTS
...
Account Management
Administration
Compound Statements
Data Definition
Data Manipulation
Data Types
...

To view help on a specific SQL category or statement:

mysql> HELP Data Manipulation
mysql> HELP JOIN

To view help on status-related SQL statements:

mysql> HELP STATUS

For more information about the HELP statement, see the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/help.html.

mysql Client: SQL Statement Terminators

SQL statements require a terminator: ; and \g are the common terminators that are equivalent and can be used interchangeably. The \G terminator, terminates queries and displays query results in a vertical style that shows each output row with each column value on a separate line. This terminator is especially useful if a query produces very wide output lines because the vertical format can make the result much easier to read. For example:

mysql> SELECT VERSION();
+-------------------------------------------+
| VERSION()                                 |
+-------------------------------------------+
| 5.6.10-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT VERSION()\G
*************************** 1. row ***************************
VERSION(): 5.6.10-enterprise-commercial-advanced-log
1 row in set (0.00 sec)

If you decide to abandon a statement that you are composing, you can cancel the statement and return to a new mysql> prompt using the \c terminator. For example:

mysql> SELECT VERSION()\c
mysql>

mysql Client: Special Statement Terminators

With mysql, you can enter a single query over multiple input lines. This makes it easier to issue a long query, because you can enter it over the course of several lines. mysql waits until it sees the statement terminator before sending the query to be executed, the prompt changes from mysql> to > as in this example:

mysql> SELECT Name, Population FROM City
> WHERE CountryCode = 'IND'
> AND Population > 3000000;

If a statement results in an error, mysql displays the error message returned by the server:

mysql> This is an invalid statement;
ERROR 1064 (42000): You have an error in your SQL syntax ; check
the manual that corresponds to your MySQL server version ...

You can also end the session and exit the mysql client using the \q terminator or QUIT or EXIT.

mysql> \q
Bye

Additional commands:

edit (\e): Edit command with $EDITOR.
pager (\P): Set PAGER [to_pager]. Print the query results via PAGER.
rehash (\#): Rebuild completion hash.

These additional commands work on UNIX and Linux operating systems but are not supported on Windows.

mysql Client: Redefining the Prompt

The mysql> prompt is the primary (or default) prompt. It signifies that the mysql client is ready for a new statement to be entered. You can change the default prompt by placing current information in the prompt, such as the user (\u), host (\h), and database (\d), as shown in the examples below.

1. Redefine the prompt:

mysql> PROMPT term 1>;
term 1>

2. Add information within the prompt:

mysql> PROMPT(\u@\h) [\d]\>
PROMPT set to '(\u@\h) [\d]\>'
(root@localhost) [test]>

3. Return to the original prompt:

(root@localhost) [test]>
mysql> PROMPT
mysql>

Everything following the first space after the PROMPT keyword becomes part of the prompt string, including other spaces. The string can contain special sequences. To return the prompt to the default, specify PROMPT or \R with no arguments as shown in the 3rd example.

Note: The second example in the slide assumes that the database was previously set to test.

mysql Client: Using Script Files

When you run it interactively, mysql reads queries entered at the keyboard. mysql also accepts input from a file. The MySQL server executes the queries in the file and displays any output produced. An input file containing SQL statements to be executed is known as a “script file” or a “batch file”. A script file should be a plain-text file containing statements in the same format that you would use to enter the statements interactively. In particular, each statement must end with a terminator.

Quotation marks are not required around the name of the file following the SOURCE command.

mysql> SOURCE /usr/stage/world_innodb.sql
Query OK, 0 rows affected (0.00 sec)
...

mysqladmin Client

You can check the server’s configuration and current status, create and drop databases, and more by using the mysqladmin command-line client. It is a command-line client for DBAs and has many capabilities as listed below:

“Ping” the server.
Shut down the server.
Create and drop databases.
Display server and version information.
Display or reset server status variables.
Set passwords.
Reload grant tables.
Flush log files and caches.
Start and stop replication.
Display client information.

Invoking the mysqladmin Client

mysqladmin accepts one or more commands following the program name. For example, the command options displayed in the slide have the following results:

1. status: Displays a brief status message, followed by the list of server variables:

shell> mysqladmin --login-path=admin status
Uptime: 363501  Threads: 1  Questions: 5441  Slow queries: 0  Opens: 53
Flush tables: 1  Open tables: 0  Queries per second avg: 0.14

2. variables: Displays server system variables and their values:

shell> mysqladmin --login-path=admin status
Uptime: 363501  Threads: 1  Questions: 5441  Slow queries: 0  Opens: 53
Flush tables: 1  Open tables: 0  Queries per second avg: 0.14

3. variables: Displays server system variables and their values:

shell> mysqladmin -uroot -poracle variables
Warning: Using a password on the command line interface can be insecure.
+-----------------------------------------+--------------------------+
| Variable_name                           | Value                    |
+-----------------------------------------+--------------------------+
| auto_increment_increment                | 1                        |
| auto_increment_offset                   | 1                        |
| autocommit                              | ON                       |
| automatic_sp_privileges                 | ON                       |
| back_log                                | 50                       |
| basedir                                 | /usr                     |
...

4. processlist: Lists active server threads.

shell> mysqladmin --login-path=admin processlist
+----+------+----------------+----+---------+------+-------+------------------+
| Id | User | Host           | db | Command | Time | State | Info             |
+----+------+----------------+----+---------+------+-------+------------------+
| 35 | root | localhost:1184 |    | Query   | 0    |       | show processlist |
+----+------+----------------+----+---------+------+-------+------------------+

5. shutdown: Stops the server - Some mysqladmin commands are available only to MySQL accounts that have the required privileges. For example, to shut down the server, you must connect to it using an administrative account such as root that has the SHUTDOWN privilege.