Mysqladmin命令总结

mysqladmin 工具的使用格式：
mysqladmin [option] command [command option] command

1.每个五秒查看一次服务器的状态，总共重复3次。

[root@localhost mysqladmin]# mysqladmin -uroot -p -i 5 -c 3 status
Enter password: 
Uptime: 4700  Threads: 1  Questions: 855  Slow queries: 0  Opens: 129  Flush tables: 2  Open tables: 19  Queries per second avg: 0.181
Uptime: 4702  Threads: 1  Questions: 855  Slow queries: 0  Opens: 129  Flush tables: 2  Open tables: 19  Queries per second avg: 0.181
Uptime: 4704  Threads: 1  Questions: 855  Slow queries: 0  Opens: 129  Flush tables: 2  Open tables: 19  Queries per second avg: 0.181

2.查看服务器的状况：status

[root@localhost mysqladmin]# mysqladmin -uroot -p status
Enter password: 
Uptime: 4788  Threads: 1  Questions: 856  Slow queries: 0  Opens: 129  Flush tables: 2  Open tables: 19  Queries per second avg: 0.178

3.修改root 密码：

mysqladmin -u root -p原密码 password ‘newpassword’

[root@localhost mysqladmin]# mysqladmin -u root -predhat password 'newpassword'

4.检查mysqlserver是否可用：

mysqladmin -uroot -p ping

[root@localhost mysqladmin]# mysqladmin -uroot -p ping
Enter password: 
mysqld is alive

5.查询服务器的版本

mysqladmin -uroot -p version

[root@localhost mysqladmin]# mysqladmin -uroot -p version
Enter password: 
mysqladmin  Ver 9.0 Distrib 5.5.35-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Server version      5.5.35-MariaDB
Protocol version    10
Connection      Localhost via UNIX socket
UNIX socket     /var/lib/mysql/mysql.sock
Uptime:         1 hour 24 min 34 sec

Threads: 1  Questions: 868  Slow queries: 0  Opens: 130  Flush tables: 2  Open tables: 20  Queries per second avg: 0.171

6.查看服务器状态的当前值：

mysqladmin -uroot -p extended-status

[root@localhost mysqladmin]# mysqladmin -uroot -p extended-status
Enter password: 
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 0           |
| Aborted_connects                         | 5           |
| Aria_pagecache_reads                     | 48          |
| Uptime_since_flush_status                | 5098        |
+------------------------------------------+-------------+

7.查询服务器系统变量值：

mysqladmin -uroot -p variables

[root@localhost mysqladmin]# mysqladmin -uroot -p variables

8.显示服务器所有运行的进程：

mysqladmin -uroot -p processlist
mysqladmin -uroot -p-i 1 processlist //每秒刷新一次

[root@localhost mysqladmin]# mysqladmin -uroot -p processlist
Enter password: 
+----+------+-----------+----+---------+------+-------+------------------+----------+
| Id | User | Host      | db | Command | Time | State | Info             | Progress |
+----+------+-----------+----+---------+------+-------+------------------+----------+
| 24 | root | localhost |    | Query   | 0    |       | show processlist | 0.000    |
+----+------+-----------+----+---------+------+-------+------------------+----------+

9.创建数据库

mysqladmin -uroot -p create daba-test

[root@localhost mysqladmin]# mysqladmin -uroot -p create daba-test

10.显示服务器上的所有数据库

mysqlshow -uroot -p

[root@localhost mysqladmin]# mysqlshow -uroot -p
Enter password: 
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| daba-test          |
| mysql              |
| new_db             |
| test               |
+--------------------+

11.显示数据库daba-test下有些什么表：

mysqlshow -uroot -p daba-test

[root@localhost mysqladmin]# mysqlshow -uroot -p daba-test
Enter password: 
Database: daba-test
+--------+
| Tables |
+--------+
+--------+

12.统计daba-test 下数据库表列的汇总

mysqlshow -uroot -p daba-test -v

[root@localhost mysqladmin]# mysqlshow -uroot -p daba-test -v
Enter password: 
Database: daba-test
+--------+----------+
| Tables | Columns  |
+--------+----------+
+--------+----------+

13.统计daba-test 下数据库表的列数和行数

mysqlshow -uroot -p daba-test -v -v

[root@localhost mysqladmin]#  mysqlshow -uroot -p daba-test -v -v
Enter password: 
Database: daba-test
+--------+----------+------------+
| Tables | Columns  | Total Rows |
+--------+----------+------------+
+--------+----------+------------+

14.删除数据库 daba-test

mysqladmin -uroot -p drop daba-test

[root@localhost mysqladmin]# mysqladmin -uroot -p drop daba-test
Enter password: 
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

15.重载权限信息

mysqladmin -uroot -p reload

[root@localhost mysqladmin]# mysqladmin -uroot -p reload

16.刷新所有表缓存，并关闭和打开log

mysqladmin -uroot -p refresh

[root@localhost mysqladmin]#  mysqladmin -uroot -p refresh

17.使用安全模式关闭数据库

mysqladmin -uroot -p shutdown

[root@localhost mysqladmin]# mysqladmin -uroot -p shutdown

18.刷新命令mysqladmin flush commands

[root@localhost ~]# mysqladmin -u root -ptmppassword flush-hosts
[root@localhost ~]#  mysqladmin -u root -ptmppassword flush-logs
[root@localhost ~]#  mysqladmin -u root -ptmppassword flush-privileges
[root@localhost ~]#  mysqladmin -u root -ptmppassword flush-status
[root@localhost ~]#  mysqladmin -u root -ptmppassword flush-tables
[root@localhost ~]#  mysqladmin -u root -ptmppassword flush-threads

19.mysqladmin 执行kill 进程：

[root@localhost ~]# mysqladmin -uroot -p processlist
[root@localhost ~]# mysqladmin -uroot -p kill idnum

20.停止和启动MySQL replication on a slave server

[root@localhost ~]#  mysqladmin -u root -p stop-slave
[root@localhost ~]#  mysqladmin -u root -p start-slave

21.同时执行多个命令

[root@localhost ~]# mysqladmin -u root -p process status version