7.4 mysqladmin, Administering a MySQL Server

7.4 mysqladmin , Administering a MySQL Server

mysqladmin is a client for performing administrative operations. You can use it to check the server's configuration and current status, create and drop databases, and more.

Invoke mysqladmin like this:

 shell>  mysqladmin  [  options  ]  command  [  command-option  ]  command  ... 

mysqladmin supports the following commands:

• create databasename

  Create a new database.

• drop databasename

  Delete a database and all its tables.

• extended-status

  Display the server status variables and their values.

• flush- hosts

  Flush all information in the host cache.

• flush-logs

  Flush all logs.

• flush-privileges

  Reload the grant tables (same as reload ).

• flush-status

  Clear status variables.

• flush-tables

  Flush all tables.

• flush-threads

  Flush the thread cache. (Added in MySQL 3.23.16.)

• kill id,id ,...

  Kill server threads.

• password new-password

  Set a new password. This changes the password to new-password for the account that you use with mysqladmin for connecting to the server.

• ping

  Check whether the server is alive .

• processlist

  Show a list of active server threads. This is like the output of the SHOW PROCESSLIST statement. If the --verbose option is given, the output is like that of SHOW FULL PROCESSLIST .

• reload

  Reload the grant tables.

• refresh

  Flush all tables and close and open log files.

• shutdown

  Stop the server.

• start-slave

  Start replication on a slave server. (Added in MySQL 3.23.16.)

• status

  Display a short server status message.

• stop-slave

  Stop replication on a slave server. (Added in MySQL 3.23.16.)

• variables

  Display the server system variables and their values.

• version

  Display version information from the server.

All commands can be shortened to any unique prefix. For example:

 shell>  mysqladmin proc stat  +----+-------+-----------+----+-------------+------+-------+------+  Id  User   Host       db  Command      Time  State  Info  +----+-------+-----------+----+-------------+------+-------+------+  6   monty  localhost      Processlist  0                  +----+-------+-----------+----+-------------+------+-------+------+ Uptime: 10077  Threads: 1  Questions: 9  Slow queries: 0 Opens: 6 Flush tables: 1  Open tables: 2 Memory in use: 1092K  Max memory used: 1116K 

The mysqladmin status command result displays the following values:

• Uptime

  The number of seconds the MySQL server has been running.

• Threads

  The number of active threads ( clients ).

• Questions

  The number of questions (queries) from clients since the server was started.

• Slow queries

  The number of queries that have taken more than long_query_time seconds. See Section 4.8.5, "The Slow Query Log."

• Opens

  The number of tables the server has opened.

• Flush tables

  The number of flush ... , refresh , and reload commands the server has executed.

• Open tables

  The number of tables that currently are open.

• Memory in use

  The amount of memory allocated directly by mysqld code. This value is displayed only when MySQL has been compiled with --with-debug=full .

• Maximum memory used

  The maximum amount of memory allocated directly by mysqld code. This value is displayed only when MySQL has been compiled with --with-debug=full .

If you execute mysqladmin shutdown when connecting to a local server using a Unix socket file, mysqladmin waits until the server's process ID file has been removed, to ensure that the server has stopped properly.

mysqladmin supports the following options:

• --help , -?

  Display a help message and exit.

• --character-sets-dir= path

  The directory where character sets are installed. See Section 4.7.1, "The Character Set Used for Data and Sorting."

• --compress , -C

  Compress all information sent between the client and the server if both support compression.

• --count= # , -c #

  The number of iterations to make. This works only with --sleep ( -i ).

• --debug[= debug_options ] , -# [ debug_options ]

  Write a debugging log. The debug_options string often is 'd:t:o, file_name ' . The default is 'd:t:o,/tmp/mysqladmin.trace' .

• --force , -f

  Don't ask for confirmation for the drop database command. With multiple commands, continue even if an error occurs.

• --host= host_name , -h host_name

  Connect to the MySQL server on the given host.

• --password[= password ] , -p[ password ]

  The password to use when connecting to the server. Note that if you use the short option form ( -p ), you cannot have a space between the option and the password. If no password is given on the command line, you will be prompted for one.

• --port= port_num , -P port_num

  The TCP/IP port number to use for the connection.

• --protocol={TCP SOCKET PIPE MEMORY}

  The connection protocol to use. New in MySQL 4.1.

• --relative , -r

  Show the difference between the current and previous values when used with -i . Currently, this option works only with the extended-status command.

• --silent , -s

  Exit silently if a connection to the server cannot be established.

• --sleep= delay , -i delay

  Execute commands again and again, sleeping for delay seconds in between.

• --socket= path , -S path

  The socket file to use for the connection.

• --user= user_name , -u user_name

  The MySQL username to use when connecting to the server.

• --verbose , -v

  Verbose mode. Print out more information on what the program does.

• --version , -V

  Display version information and exit.

• --vertical , -E

  Print output vertically. This is similar to --relative , but prints output vertically.

• --wait[= # ] , -w[ # ]

  If the connection cannot be established, wait and retry instead of aborting. If an option value is given, it indicates the number of times to retry . The default is one time.

You can also set the following variables by using -- var_name = value options:

• connect_timeout

  The number of seconds before connection timeout. (Default value is 0.)

• shutdown_timeout

  The number of seconds to wait for shutdown. (Default value is 0.)

It is also possible to set variables by using -- set-variable = var_name = value or -O var_name = value syntax. However, this syntax is deprecated as of MySQL 4.0.