mysqladmin - The Client Tool for Administrators

A quick introduction is provided on mysqladmin, the client tool managing MySQL servers. You can run mysqladmin on a remote system to check the status of the MySQL server or shutdown the server.

What Is mysqladmin? mysqladmin is a client tool program for database server administrators to manage a MySQL server remotely. The syntax to run mysqladmin is:

C:\herong>set set "MySQL=\Program Files\MySQL\MySQL Server 5.7"
C:\herong>"%MySQL%\bin\mysqladmin" [options] command [command-arg]

mysqladmin version 5.7 supports a long list of commands. You can get a list of supported commands by:

C:\herong>"%MySQL%\bin\mysqladmin"

\Program Files\MySQL\MySQL Server 5.7\bin\mysqladmin  
Ver 8.42 Distrib 5.7.10, for Win32 on AMD64

Administration program for the mysqld daemon.
Usage: \Program Files\MySQL\MySQL Server 5.7\bin\mysqladmin [OPTIONS]
  command command....
  --bind-address=name IP address to bind to.
  -c, --count=#    Number of iterations to make. This works with -i
                   (--sleep) only.
  -#, --debug[=#]  This is a non-debug version. Catch this and exit.
  --debug-check    This is a non-debug version. Catch this and exit.
  --debug-info     This is a non-debug version. Catch this and exit.
  -f, --force      Don't ask for confirmation on drop database; with
                   multiple commands, continue even if an error occurs
  -C, --compress   Use compression in server/client protocol.
  --character-sets-dir=name 
                   Directory for character set files.
  --default-character-set=name 
                   Set the default character set.
  -?, --help       Display this help and exit.
  -h, --host=name  Connect to host.
  -b, --no-beep    Turn off beep on error.
  -p, --password[=name] 
                   Password to use when connecting to server. 
                   If password is not given it's asked from the tty.
  -W, --pipe       Use named pipes to connect to server.
  -P, --port=#     Port number to use for connection or 0 for default 
                   to, in order of preference, my.cnf, $MYSQL_TCP_PORT
                   /etc/services, built-in default (3306).
  --protocol=name  The protocol to use for connection (tcp, socket, 
                   pipe, memory).
  -r, --relative   Show difference between current and previous values 
                   when used with -i. Currently only works with 
                   extended-status.
  --secure-auth    Refuse client connecting to server if it uses old
                   (pre-4.1.1) protocol. Deprecated. Always TRUE
  --shared-memory-base-name=name 
                   Base name of shared memory.
  -s, --silent     Silently exit if one can't connect to server.
  -S, --socket=name The socket file to use for connection.
  -i, --sleep=#    Execute commands repeatedly with a sleep between.
  ...
  
Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- -----------------------------
bind-address                      (No default value)
count                             0
force                             FALSE
compress                          FALSE
...

Default options are read from the following files in the given order:
C:\Windows\my.ini C:\Windows\my.cnf 
C:\my.ini C:\my.cnf C:\Program Files\MySQL\MySQL Server 5.7\my.ini 
C:\Program Files\MySQL\MySQL Server 5.7\my.cnf 
The following groups are read: mysqladmin client
The following options may be given as the first argument:
--print-defaults      Print the program argument list and exit.
--no-defaults         Don't read default options from any option file,
                      except for login file.
--defaults-file=#     Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
                      Also read groups with concat(group, suffix)
--login-path=#        Read this path from the login file.

Where command is a one or more of: (Commands may be shortened)
  create databasename Create a new database
  debug            Instruct server to write debug information to log
  drop databasename   Delete a database and all its tables
  extended-status     Gives an extended status message from the server
  flush-hosts         Flush all cached hosts
  flush-logs          Flush all logs
  flush-status        Clear status variables
  flush-tables        Flush all tables
  flush-threads       Flush the thread cache
  flush-privileges    Reload grant tables (same as reload)
  kill id,id,...      Kill mysql threads
  password [new-password] Change old password to new-password in 
                      current format
  ping                Check if mysqld is alive
  processlist         Show list of active threads in server
  reload              Reload grant tables
  refresh             Flush all tables and close and open logfiles
  shutdown            Take server down
  status              Gives a short status message from the server
  start-slave         Start slave
  stop-slave          Stop slave
  variables           Prints variables available
  version             Get version info from server

Two options are important to run mysqladmin, --host=name and --user=name. These options allows you to specify the host name where the MySQL server is running, the user name to be used to access the server.

Here are some examples of using mysqladmin to manage the MySQL server running on my local host with "root" as the user name:

C:\herong>"%MySQL%\bin\mysqladmin" --host=localhost ping

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)


C:\herong>"%MySQL%\bin\mysqladmin" --host=localhost 
   --user=root --password=TopSecret ping

mysqld is alive


C:\herong>"%MySQL%\bin\mysqladmin" --host=localhost 
   --user=root --password=TopSecret status

Uptime: 26233  Threads: 1  Questions: 19  Slow queries: 0  Opens: 12
Flush tables: 1  Open tables: 0  Queries per second avg: 0.001


C:\herong>%MySQL%\bin\mysqladmin --host=localhost 
   --user=root --password=TopSecret processlist

--+----+---------------+--+-------+----+--------+----------------+
Id|User|Host           |db|Command|Time|State   |Info            |
--+----+---------------+--+-------+----+--------+----------------+
6 |root|localhost:63499|  |Query  |0   |starting|show processlist|
--+----+---------------+--+-------+----+--------+----------------+


C:\herong>"%MySQL%\bin\mysqladmin" --host=localhost 
   --user=root --password=TopSecret shutdown


C:\herong>"%MySQL%\bin\mysqladmin" --host=localhost --user=root ping

mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to MySQL server on 'localhost' (10061)'
Check that mysqld is running on localhost and that the port is 3306.
You can check this by doing 'telnet localhost 3306'

Last update: 2015.

Table of Contents

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

Introduction of MySQL Programs

 List of MySQL Programs

 mysqld - The MySQL Server Program

mysqladmin - The Client Tool for Administrators

 mysql - The Client Tool for End Users

 Using mysql to Run SQL Statements

 mysqldump - Dumping Data to Files

 mysqlimport - Loading Data from Files

 Perl Programs and MySQL Servers

 PHP Programs and MySQL Servers

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 Table Column Types for Different Types of Values

 Using DDL to Create Tables and Indexes

 Using DML to Insert, Update and Delete Records

 Using SELECT to Query Database

 Transaction Management and Isolation Levels

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 Outdated Tutorials

 References

 PDF Printing Version