MariaDB_mysqladmin

mysqladmin

via: https://mariadb.com/kb/en/mysqladmin/

 

mysqladmin is an administration program for the mysqld daemon. It can be used to:

• Monitor what the MySQL clients are doing (processlist)
• Get usage statistics and variables from the MariaDB / MySQL server
• Create/drop databases
• Flush (reset) logs, statistics and tables
• Kill running queries.
• Stop the server (shutdown)
• Start/stop slaves
• Check if the server is alive (ping)

Usage

mysqladmin [OPTIONS] command command....

mysqladmin Options

OptionDescription-c, --count=# Number of iterations to make. This works with -i (--sleep) only.--debug-checkCheck memory and open file usage at exit.--debug-infoPrint some debug info at exit.-f, --forceDon't ask for confirmation on drop database; with multiple commands, continue even if an error occurs.-C, --compressUse compression in server/client protocol.--character-sets-dir=nameDirectory for character set files.--default-character-set=nameSet the default character set.-?, --helpDisplay this help and exit.-h, --host=nameConnect to host.-b, --no-beepTurn 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.-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=nameThe protocol to use for connection (tcp, socket, pipe, memory).-r, --relativeShow difference between current and previous values when used with -i. Currently only works with extended-status.-O, --set-variable=nameChange the value of a variable. Please note that this option is deprecated; you can set variables directly with--variable-name=value.-s, --silentSilently exit if one can't connect to server.-S, --socket=nameThe socket file to use for connection.-i, --sleep=# Execute commands repeatedly with a sleep between.--sslEnable SSL for connection (automatically enabled with other flags).Disable with '--skip-ssl'.--ssl-ca=nameCA file in PEM format (check OpenSSL docs, implies --ssl).--ssl-capath=nameCA directory (check OpenSSL docs, implies --ssl).--ssl-cert=nameX509 cert in PEM format (implies --ssl).--ssl-cipher=nameSSL cipher to use (implies --ssl).--ssl-key=nameX509 key in PEM format (implies --ssl).--ssl-verify-server-certVerify server's "Common Name" in its cert against hostname used when connecting. This option is disabled by default.-u, --user=nameUser for login if not current user.-v, --verboseWrite more information.-V, --versionOutput version information and exit.-E, --verticalPrint output vertically. Is similar to '--relative', but prints output vertically.-w, --wait[=#]Wait and retry if connection is down.--connect_timeout=#  --shutdown_timeout=#  

mysqladmin Variables (--variable-name=value)

Variables and boolean options {FALSE|TRUE}  Value (after reading options)count0debug-checkFALSEdebug-infoFALSEforceFALSEcompressFALSEcharacter-sets-dir(No default value)default-character-set(No default value)host(No default value)no-beepFALSEport3306relativeFALSEsocket/var/run/mysqld/mysqld.socksleep0sslFALSEssl-ca(No default value)ssl-capath(No default value)ssl-cert(No default value)ssl-cipher(No default value)ssl-key(No default value)ssl-verify-server-certFALSEuser(No default value)verboseFALSEverticalFALSEconnect_timeout43200shutdown_timeout3600

mysqladmin Default Options

Default options are read from the following files in the given order:

1. /etc/my.cnf
2. /etc/mysql/my.cnf
3. /usr/etc/my.cnf
4. ~/.my.cnf

The following groups are read:

1. mysqladmin
2. client
3. client-server
4. client-mariadb

The following options may be given as the first argument:

OptionDescription--print-defaultsPrint the program argument list and exit.--no-defaultsDon't read default options from any option file.--defaults-file=# Only read default options from the given file #.--defaults-extra-file=# Read this file after the global files are read.

mysqladmin Commands

Command is one or more of: (Commands may be shortened)

CommandDescriptioncreate databasenameCreate a new databasedebugInstruct server to write debug information to logdrop databasenameDelete a database and all its tablesextended-statusGives an extended status message from the serverflush-all-statisticsFlush all statistics tablesflush-all-statusFlush status and statisticsflush-client-statisticsFlush client statisticsflush-hostsFlush all cached hostsflush-index-statisticsFlush index statisticsflush-logsFlush all logsflush-privilegesReload grant tables (same as reload)flush-slow-logFlush slow query logflush-statusClear status variablesflush-table-statisticsClear table statisticsflush-tablesFlush all tablesflush-threadsFlush the thread cacheflush-user-statisticsFlush user statisticskill id,id,...Kill mysql threadspassword new-passwordChange old password to new-password, MySQL 4.1 hashing.old-password new-passwordChange old password to new-password in old format.pingCheck if mysqld is aliveprocesslistShow list of active threads in serverreloadReload grant tablesrefreshFlush all tables and close and open logfilesshutdownTake server down; see also SHUTDOWNstatusGives a short status message from the serverstart-slaveStart slavestop-slaveStop slavevariablesPrints variables availableversionGet version info from server

Typical example usage

Quick check of what the server is doing:

shell> mysqladmin statusUptime: 8023  Threads: 1  Questions: 14  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.1shell> mysqladmin processlist+----+-------+-----------+----+---------+------+-------+------------------+| Id | User  | Host      | db | Command | Time | State | Info             |+----+-------+-----------+----+---------+------+-------+------------------+....+----+-------+-----------+----+---------+------+-------+------------------+

More extensive information of what is happening 'just now' changing (great for troubleshooting a slow server):

shell> mysqladmin --relative --sleep=1 extended-status | grep -v " 0 "

Check the variables for a running server:

shell> mysqladmin variables | grep datadir| datadir                                  | /my/data/ |

Other ways to stop mysqld (unix)

If you get the error:

mysqladmin: shutdown failed; error: 'Access denied; you need (at least one of) the SHUTDOWN privilege(s) for this operation'

It means that you didn't use mysqladmin with a user that has the SUPER or SHUTDOWN privilege.

If you don't know the user password, you can still take the mysqld process down with a system killcommand:

kill -SIGTERM pid-of-mysqld-process

The above is identical to mysqladmin shutdown.

On windows you should use:

NET STOP MySQL

See also

• mytop, a 'top' like program for MariaDB/MySQL that allows you to see what the server is doing. A mytop optimized for MariaDB is included in MariaDB 5.3