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
-c, | Number of iterations to make. This works with -i ( ) only. |
| 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 ' ', but prints output vertically. |
-w, | Wait and retry if connection is down. |
| |
|
mysqladmin Variables (--
variable-name=value
)
--
variable-name=value {FALSE|TRUE}
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:
mysqladmin
client
client-server
client-mariadb
The following options may be given as the first argument:
| 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)
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