mysqladmin - The Client Tool for Administrators

MySQL Tutorials - Herong's Tutorial Examples

∟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 given below, assuming that the "%mysql%" environment variable has been defined to represent the MySQL Server directory.

herong> %mysql%\bin\mysqladmin [options] command [command-arg]

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

herong> "%MySQL%\bin\mysqladmin"

  C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqladmin
  Ver 8.0.16 for Win64 on x86_64 (MySQL Community Server - GPL)

Administration program for the mysqld daemon.
Usage: C:\Program Files\MySQL\MySQL Server 8.0\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 8.0\my.ini
  C:\Program Files\MySQL\MySQL Server 8.0\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:

herong> %mysql%\bin\mysqladmin --host=localhost ping

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


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

mysqld is alive


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


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|
--+----+---------------+--+-------+----+--------+----------------+


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


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'