mysqladmin
You are viewing an old version of this article. View
the current version here.
Contents
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
| Option | Description |
|---|---|
-c, | Number of iterations to make. This works with -i ( |
| Check memory and open file usage at exit. |
| Print some debug info at exit. |
-f, | Don't ask for confirmation on drop database; with multiple commands, continue even if an error occurs. |
-C, | Use compression in server/client protocol. |
| Directory for character set files. |
| Set the default character set. |
-?, | Display this help and exit. |
-h, | Connect to host. |
-b, | Turn off beep on error. |
-p, | Password to use when connecting to server. If password is not given it's asked from the tty. |
-P, | 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). |
| The protocol to use for connection (tcp, socket, pipe, memory). |
-r, | Show difference between current and previous values when used with -i. Currently only works with extended-status. |
-O, | Change the value of a variable. Please note that this option is deprecated; you can set variables directly with |
-s, | Silently exit if one can't connect to server. |
-S, | The socket file to use for connection. |
-i, | Execute commands repeatedly with a sleep between. |
| Enable SSL for connection (automatically enabled with other flags).Disable with ' |
| CA file in PEM format (check OpenSSL docs, implies |
| CA directory (check OpenSSL docs, implies |
| X509 cert in PEM format (implies |
| SSL cipher to use (implies |
| X509 key in PEM format (implies |
| Verify server's "Common Name" in its cert against hostname used when connecting. This option is disabled by default. |
-u, | User for login if not current user. |
-v, | Write more information. |
-V, | Output version information and exit. |
-E, | Print output vertically. Is similar to ' |
-w, | Wait and retry if connection is down. |
| |
|
mysqladmin Variables (--variable-name=value
--variable-name=value| Variables and boolean options | {FALSE|TRUE} Value (after reading options) |
|---|---|
count | 0 |
debug-check | FALSE |
debug-info | FALSE |
force | FALSE |
compress | FALSE |
character-sets-dir | (No default value) |
default-character-set | (No default value) |
host | (No default value) |
no-beep | FALSE |
port | 3306 |
relative | FALSE |
socket | /var/run/mysqld/mysqld.sock |
sleep | 0 |
ssl | FALSE |
ssl-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-cert | FALSE |
user | (No default value) |
verbose | FALSE |
vertical | FALSE |
connect_timeout | 43200 |
shutdown_timeout | 3600 |
mysqladmin Default Options
Default options are read from the following files in the given order:
/etc/my.cnf/etc/mysql/my.cnf/usr/etc/my.cnf~/.my.cnf
The following groups are read:
mysqladminclientclient-serverclient-mariadb
The following options may be given as the first argument:
| Option | Description |
|---|---|
| Print the program argument list and exit. |
| Don't read default options from any option file. |
| Only read default options from the given file #. |
| Read this file after the global files are read. |
mysqladmin Commands
Command is one or more of: (Commands may be shortened)
| Command | Description |
|---|---|
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-all-statistics | Flush all statistics tables |
flush-all-status | Flush status and statistics |
flush-client-statistics | Flush client statistics |
flush-hosts | Flush all cached hosts |
flush-index-statistics | Flush index statistics |
flush-logs | Flush all logs |
flush-privileges | Reload grant tables (same as reload) |
flush-slow-log | Flush slow query log |
flush-status | Clear status variables |
flush-table-statistics | Clear table statistics |
flush-tables | Flush all tables |
flush-threads | Flush the thread cache |
flush-user-statistics | Flush user statistics |
kill id,id,... | Kill mysql threads |
password new-password | Change old password to new-password, MySQL 4.1 hashing. |
old-password new-password | Change old password to new-password in old 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; see also SHUTDOWN |
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 |
Typical example usage
Quick check of what the server is doing:
shell> mysqladmin status Uptime: 8023 Threads: 1 Questions: 14 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.1 shell> 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 kill command:
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
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
