mysqladmin 命令详解

mysqladmin是一个执行管理操作的客户端程序。它可以用来检查服务器的配置和当前状态、创建和删除数据库等。

[root@fp-web-118 bin]# mysqladmin --help   ###linux的命令
mysqladmin Ver 8.42 Distrib 5.6.51, for Linux on x86_64
Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Administration program for the mysqld daemon.
Usage: 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-check       Check memory and open file usage at exit.
  --debug-info        Print some debug info at 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 设置字符集.
  -?, --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.
  -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.(Defaults to on; use --skip-secure-auth to disable.)
  -s, --silent （静默模式）
  -S, --socket=name   The socket file to use for connection.
  -i, --sleep=#       Execute commands repeatedly with a sleep between.
  --ssl               Enable SSL for connection (automatically enabled with other flags).
  --ssl-ca=name       CA file in PEM format (check OpenSSL docs, implies --ssl).
  --ssl-capath=name   CA directory (check OpenSSL docs, implies --ssl).
  --ssl-cert=name     X509 cert in PEM format (implies --ssl).
  --ssl-cipher=name   SSL cipher to use (implies --ssl).
  --ssl-key=name      X509 key in PEM format (implies --ssl).
  --ssl-crl=name      Certificate revocation list (implies --ssl).
  --ssl-crlpath=name  Certificate revocation list path (implies --ssl).
  --ssl-verify-server-cert  Verify server's "Common Name" in its cert against hostname used when connecting. This option is disabled by default.
  --ssl-mode=name     SSL connection mode.
  -u, --user=name     User for login if not current user.
  -v, --verbose       Write more information.
  -V, --version       Output version information and exit.
  -E, --vertical      Print output vertically. Is similar to --relative, but
                      prints output vertically.
  -w, --wait[=#]      Wait and retry if connection is down.
  --connect-timeout=# 
  --shutdown-timeout=# 
  --plugin-dir=name   Directory for client-side plugins.
  --default-auth=name Default authentication client-side plugin to use.
  --enable-cleartext-plugin 
                      Enable/disable the clear text authentication plugin.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
bind-address                      (No default value)
count                             0
debug-check                       FALSE
debug-info                        FALSE
force                             FALSE
compress                          FALSE
character-sets-dir                (No default value)
default-character-set             auto
host                              (No default value)
no-beep                           FALSE
port                              0
relative                          FALSE
secure-auth                       TRUE
socket                            (No default value)
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-crl                           (No default value)
ssl-crlpath                       (No default value)
ssl-verify-server-cert            FALSE
user                              (No default value)
verbose                           FALSE
vertical                          FALSE
connect-timeout                   43200
shutdown-timeout                  3600
plugin-dir                        (No default value)
default-auth                      (No default value)
enable-cleartext-plugin           FALSE
按照以下给定的顺序来查找mysql配置文件:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
The following groups are read: mysqladmin client
以下的选项可能会被选择
--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 (创建数据库命令：mysqladmin –uroot –p creat db_name)
  drop databasename   (删除数据库命令：mysqladmin –uroot –p drop db_name)
  extended-status     (显示服务器状态变量及变量值：mysqladmin –uroot –p extended-status)
  flush-hosts         （刷新所有主机的缓存信息）
  flush-logs          （刷新所有的曰志）
  flush-status        （清空状态变量）
  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
  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
  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

mysqladmin 工具的使用格式：

mysqladmin [option] command [command option] command ......

option 选项：

-c number 自动运行次数统计，必须和 -i 一起使用
-i number 间隔多长时间重复执行
每个两秒查看一次服务器的状态，总共重复5次。
./mysqladmin -uroot -p -i 2 -c 5 status
-h, --host=name Connect to host. 连接的主机名或iP
-p, --password[=name] 登录密码，如果不写于参数后，则会提示输入
-P, --port=# Port number to use for connection. 指定数据库端口
-s, --silent Silently exit if one can't connect to server.
-S, --socket=name Socket file to use for connection. 指定socket file
-i, --sleep=# Execute commands again and again with a sleep between. 间隔一段时间执行一次
-u, --user=name User for login if not current user.登录数据库用户名
-v, --verbose Write more information. 写更多的信息
-V, --version Output version information and exit. 显示版本

mysqladmin的相关命令：

可以直接执行或显示相关的服务器信息

mysqladmin password jinzs1988                  #<==设置密码，前文用过的。
mysqladmin -uroot -jinzs1988 password jinzs19880   #<==修改密码，前文用过的。
mysqladmin -uroot -jinzs1988 status           #<==查看状态，相当于show status。
mysqladmin -uroot -jinzs1988 -i 1 status      #<==每秒查看一次状态。
mysqladmin -uroot -jinzs1988 extended-status   #<==等同show global status;。
mysqladmin -uroot -jinzs1988 flush-logs        #<==切割日志。
mysqladmin -uroot -jinzs1988 processlist       #<==查看执行的SQL语句信息。
mysqladmin -uroot -jinzs1988 processlist -i 1  #<==每秒查看一次执行的SQL语句。
mysqladmin -uroot -p'jinzs1988' shutdown           #<==关闭mysql服务，前文用过的。
mysqladmin -uroot -p'jinzs1988' variables          #<==相当于show variables。

实例： 

1、查看服务器的状况：status
[root@fp-web-118 bin]# mysqladmin  -uroot -p status
Enter password:
Uptime: 26  Threads: 1  Questions: 2  Slow queries: 0  Opens: 67  Flush tables: 1  Open tables: 60  Queries per second avg: 0.076
[root@fp-web-118 bin]#

2.修改root 密码：
[root@fp-web-118 bin]# mysqladmin -u root -poldpassword password 'newpassword'

3.检查mysqlserver是否可用：
[root@fp-web-118 bin]# mysqladmin -uroot -p ping
显示结果：
mysqld is alive

4.查询服务器的版本
[root@fp-web-118 bin]#mysqladmin -uroot -p version

5.显示服务器所有运行的进程：
mysqladmin -uroot -p processlist
mysqladmin -uroot -p-i 1 processlist 每秒刷新一次
[root@fp-web-118 bin]# mysqladmin -uroot -p processlist
Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 8  | root | localhost |    | Query   | 0    | init  | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
[root@fp-web-118 bin]#mysqladmin -uroot -p -i 1 processlist
Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 9  | root | localhost |    | Query   | 0    | init  | show processlist |
6.创建数据库
[root@fp-web-118 bin]#mysqladmin -uroot -p create daba-test
[root@fp-web-118 bin]#mysqladmin -uroot -pdadong123 create lili
Warning: Using a password on the command line interface can be insecure.
[root@DB02 ~]#
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lili               |
| mao                |
| mysql              |
| dadong             |
| hehe            |
| performance_schema |
+--------------------+
7 rows in set (0.00 sec)
9.显示服务器上的所有数据库
mysqlshow -uroot -p

10.显示数据库daba-test下有些什么表：
mysqlshow -uroot -p daba-test

[root@fp-web-118 bin]#mysqlshow -uroot -pdadong123 mysql
Warning: Using a password on the command line interface can be insecure.
Database: mysql
+---------------------------+
|          Tables           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |

11.统计daba-test 下数据库表列的汇总
mysqlshow -uroot -p daba-test -v

12.统计daba-test 下数据库表的列数和行数
mysqlshow -uroot -p daba-test -v -v

13. 删除数据库 daba-test
mysqladmin -uroot -p drop daba-test

14. 重载权限信息
mysqladmin -uroot -p reload

15.刷新所有表缓存，并关闭和打开log
mysqladmin -uroot -p refresh

16.使用安全模式关闭数据库
mysqladmin -uroot -p shutdown