Knowledgebase

Mysql Client Commands

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) 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.
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.
help      (\h) Display this help.
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.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given
               outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing
               binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.
query_attributes Sets string parameters (name1 value1 name2 value2 ...)
for the next query to pick up.
ssl_session_data_print Serializes the current SSL session data to stdout 
or file.

For server side help, type 'help contents'

The prompt command reconfigures the default mysql> prompt. The string for defining the prompt can contain the following special sequences.

Option Description
\C The current connection identifier
\c A counter that increments for each statement you issue
\D The full current date
\d The default database
\h The server host
\l The current delimiter
\m Minutes of the current time
\n A newline character
\O The current month in three-letter format (Jan, Feb, …)
\o The current month in numeric format
\P am/pm
\p The current TCP/IP port or socket file
\R The current time, in 24-hour military time (0–23)
\r The current time, standard 12-hour time (1–12)
\S Semicolon
\s Seconds of the current time
\T Print an asterisk (*) if the current session is inside a transaction block (from MySQL 8.0.28)
\t A tab character
\U

Your full user_name@host_name account name
\u Your user name
\v The server version
\w The current day of the week in three-letter format (Mon, Tue, …)
\Y The current year, four digits
\y The current year, two digits
\_ A space
\  A space (a space follows the backslash)
\' Single quote
\" Double quote
\\ A literal \ backslash character
\x

x, for any “x” not listed above
Option Description

mysqladmin supports the following commands. Some of the commands take an argument following the command name.

  • create db_name

    Create a new database named db_name.

  • debug

    Prior to MySQL 8.0.20, tell the server to write debug information to the error log. The connected user must have the SUPER privilege. Format and content of this information is subject to change.

    This includes information about the Event Scheduler. See Section 25.4.5, “Event Scheduler Status”.

  • drop db_name

    Delete the database named db_name and all its tables.

  • extended-status

    Display the server status variables and their values.

  • flush-hosts

    Flush all information in the host cache. See Section 5.1.12.3, “DNS Lookups and the Host Cache”.

  • flush-logs [log_type ...]

    Flush all logs.

    The mysqladmin flush-logs command permits optional log types to be given, to specify which logs to flush. Following the flush-logs command, you can provide a space-separated list of one or more of the following log types: binary, engine, error, general, relay, slow. These correspond to the log types that can be specified for the FLUSH LOGS SQL statement.

  • flush-privileges

    Reload the grant tables (same as reload).

  • flush-status

    Clear status variables.

  • flush-tables

    Flush all tables.

  • flush-threads

    Flush the thread cache.

  • kill id,id,...

    Kill server threads. If multiple thread ID values are given, there must be no spaces in the list.

    To kill threads belonging to other users, the connected user must have the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege).

  • password new_password

    Set a new password. This changes the password to new_password for the account that you use with mysqladmin for connecting to the server. Thus, the next time you invoke mysqladmin (or any other client program) using the same account, you must specify the new password.

 

  • ping

    Check whether the server is available. The return status from mysqladmin is 0 if the server is running, 1 if it is not. This is 0 even in case of an error such as Access denied, because this means that the server is running but refused the connection, which is different from the server not running.

  • processlist

    Show a list of active server threads. This is like the output of the SHOW PROCESSLIST statement. If the --verbose option is given, the output is like that of SHOW FULL PROCESSLIST. (See Section 13.7.7.29, “SHOW PROCESSLIST Statement”.)

  • reload

    Reload the grant tables.

  • refresh

    Flush all tables and close and open log files.

  • shutdown

    Stop the server.

  • start-replica

    Start replication on a replica server. Use this command from MySQL 8.0.26.

  • start-slave

    Start replication on a replica server. Use this command before MySQL 8.0.26.

  • status

    Display a short server status message.

  • stop-replica

    Stop replication on a replica server. Use this command from MySQL 8.0.26.

  • stop-slave

    Stop replication on a replica server. Use this command before MySQL 8.0.26.

  • variables

    Display the server system variables and their values.

  • version

    Display version information from the server.

 

 

All commands can be shortened to any unique prefix. For example:

$> mysqladmin proc stat
+----+-------+-----------+----+---------+------+-------+------------------+
| Id | User  | Host      | db | Command | Time | State | Info             |
+----+-------+-----------+----+---------+------+-------+------------------+
| 51 | jones | localhost |    | Query   | 0    |       | show processlist |
+----+-------+-----------+----+---------+------+-------+------------------+
Uptime: 1473624  Threads: 1  Questions: 39487
Slow queries: 0  Opens: 541  Flush tables: 1
Open tables: 19  Queries per second avg: 0.0268

The mysqladmin status command result displays the following values:

  • Uptime

    The number of seconds the MySQL server has been running.

  • Threads

    The number of active threads (clients).

  • Questions

    The number of questions (queries) from clients since the server was started.

  • Slow queries

    The number of queries that have taken more than long_query_time seconds.

  • Opens

    The number of tables the server has opened.

  • Flush tables

    The number of flush-*, refresh, and reload commands the server has executed.

  • Open tables

    The number of tables that currently are open.

If you execute mysqladmin shutdown when connecting to a local server using a Unix socket file, mysqladmin waits until the server's process ID file has been removed, to ensure that the server has stopped properly.

mysqladmin supports the following options, which can be specified on the command line or in the [mysqladmin] and [client] groups of an option file.

 

Table 4.12 mysqladmin Options

Option Name Description Introduced Deprecated
--bind-address Use specified network interface to connect to MySQL Server    
--compress Compress all information sent between client and server   8.0.18
--compression-algorithms Permitted compression algorithms for connections to server 8.0.18  
--connect-timeout Number of seconds before connection timeout    
--count Number of iterations to make for repeated command execution    
--debug Write debugging log    
--debug-check Print debugging information when program exits    
--debug-info Print debugging information, memory, and CPU statistics when program exits    
--default-auth Authentication plugin to use    
--default-character-set Specify default character set    
--defaults-extra-file Read named option file in addition to usual option files    
--defaults-file Read only named option file    
--defaults-group-suffix Option group suffix value    
--enable-cleartext-plugin Enable cleartext authentication plugin    
--force Continue even if an SQL error occurs    
--get-server-public-key Request RSA public key from server    
--help Display help message and exit    
--host Host on which MySQL server is located    
--login-path Read login path options from .mylogin.cnf    
--no-beep Do not beep when errors occur    
--no-defaults Read no option files    
--password Password to use when connecting to server    
--password1 First multifactor authentication password to use when connecting to server 8.0.27  
--password2 Second multifactor authentication password to use when connecting to server 8.0.27  
--password3 Third multifactor authentication password to use when connecting to server 8.0.27  
--pipe Connect to server using named pipe (Windows only)    
--plugin-dir Directory where plugins are installed    
--port TCP/IP port number for connection    
--print-defaults Print default options    
--protocol Transport protocol to use    
--relative Show the difference between the current and previous values when used with the --sleep option    
--server-public-key-path Path name to file containing RSA public key    
--shared-memory-base-name Shared-memory name for shared-memory connections (Windows only)    
--show-warnings Show warnings after statement execution    
--shutdown-timeout The maximum number of seconds to wait for server shutdown    
--silent Silent mode    
--sleep Execute commands repeatedly, sleeping for delay seconds in between    
--socket Unix socket file or Windows named pipe to use    
--ssl-ca File that contains list of trusted SSL Certificate Authorities    
--ssl-capath Directory that contains trusted SSL Certificate Authority certificate files    
--ssl-cert File that contains X.509 certificate    
--ssl-cipher Permissible ciphers for connection encryption    
--ssl-crl File that contains certificate revocation lists    
--ssl-crlpath Directory that contains certificate revocation-list files    
--ssl-fips-mode Whether to enable FIPS mode on client side    
--ssl-key File that contains X.509 key    
--ssl-mode Desired security state of connection to server    
--ssl-session-data File that contains SSL session data 8.0.29  
--ssl-session-data-continue-on-failed-reuse Whether to establish connections if session reuse fails 8.0.29  
--tls-ciphersuites Permissible TLSv1.3 ciphersuites for encrypted connections 8.0.16  
--tls-version Permissible TLS protocols for encrypted connections    
--user MySQL user name to use when connecting to server    
--verbose Verbose mode    
--version Display version information and exit    
--vertical Print query output rows vertically (one line per column value)    
--wait If the connection cannot be established, wait and retry instead of aborting    
--zstd-compression-level Compression level for connections to server that use zstd compression 8.0.18  
Option Name Description Introduced Deprecated
  • mysql, command, mysqladmin
  • 56 Users Found This Useful
Was this answer helpful?

Related Articles

Basic Bash Shell Commands

What is Bash? Bash, or the Bourne Again SHell, is based on the Bourne shell. It is the default shell command language interpreter for GNU Linux/Unix and is accessed via a terminal. The shell...

Mysql Query Commands

Introduction to MySQL Query Commands MySQL is an open-source relational database management system. It is one of the most widely used SQL databases, which provides speed, reliability, and...