MySQL工具【mysqladmin 管理工具、mysqlshow 对象查看工具、perror 错误代码查看工具】

最新推荐文章于 2024-03-15 18:49:05 发布

Zhao.Mr

最新推荐文章于 2024-03-15 18:49:05 发布

阅读量384

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/qq_42226855/article/details/113830513

版权

Mysql 专栏收录该内容

17 篇文章 1 订阅

订阅专栏

本文介绍了MySQL的管理工具mysqladmin和对象查看工具mysqlshow的使用。mysqladmin用于执行管理操作，如创建和删除数据库，刷新权限等。mysqlshow则用于快速查看数据库、表、列和索引信息。通过示例展示了如何使用这两个工具进行各种操作，包括查看数据库状态、统计信息以及错误代码解析。

摘要由CSDN通过智能技术生成

01. mysqladmin 管理工具

mysqladmin 是一个执行管理操作的客户端程序。它可以用来检查服务器的当前配置和当前的状态，创建并删除数据库等。与mysql工具类似，但更侧重一些管理操作。更多的选项可以使用 mysqladmin --help工具查看。

Where command is a 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-hosts           Flush all cached hosts
  flush-logs            Flush all logs
  flush-status		Clear status variables
  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, 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
  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

示例

[root@ufo128 mysql]# mysqladmin status
Uptime: 873967  Threads: 1  Questions: 205  Slow queries: 0  Opens: 24  Flush tables: 1  Open tables: 13  Queries per second avg: 0.0
[root@ufo128 mysql]# 
[root@ufo128 mysql]# mysqladmin ping
mysqld is alive

# 只能关闭数据库不能开启数据库
[root@ufo128 mysql]# mysqladmin -uroot -p 123456 showdown

02. mysqlshow 对象查看工具

mysqlshow 可以很快的查找存在哪些数据库，数据库中的表，表中的列或索引。更多的选项可以使用 mysqlshow --help工具查看。

查看有哪些数据库

[root@ufo128 mysql]# mysqlshow -uroot
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| test               |
| test110            |
+--------------------+
[root@ufo128 mysql]# mysqlshow 
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| test               |
| test110            |
+--------------------+

显示统计信息：不指定数据库

[root@ufo128 mysql]# mysqlshow -uroot --count
+--------------------+--------+--------------+
|     Databases      | Tables |  Total Rows  |
+--------------------+--------+--------------+
| information_schema |     28 |         2329 |
| mysql              |     23 |         2019 |
| test               |      5 |           11 |
| test110            |      0 |            0 |
+--------------------+--------+--------------+
4 rows in set.

显示统计信息：指定数据库

[root@ufo128 mysql]# mysqlshow -uroot  test --count
Database: test
+-----------+----------+------------+
|  Tables   | Columns  | Total Rows |
+-----------+----------+------------+
| t2        |        1 |         11 |
| ufo123    |        2 |          0 |
| ufo1234   |        2 |          0 |
| ufo12345  |        2 |          0 |
| ufo123456 |        2 |          0 |
+-----------+----------+------------+
5 rows in set.

显示统计信息：指定数据库和表

[root@ufo128 mysql]# mysqlshow -uroot  test t2 --count
Database: test  Table: t2  Rows: 11
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type    | Collation | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
| id    | int(11) |           | YES  |     |         |       | select,insert,update,references |         |
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+

显示表中的所有索引，只用-k 或 --keys

[root@ufo128 mysql]# mysqlshow -uroot  mysql db -k
Database: mysql  Table: db
+-----------------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field                 | Type          | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-----------------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Host                  | char(60)      | utf8_bin        | NO   | PRI |         |       | select,insert,update,references |         |
| Db                    | char(64)      | utf8_bin        | NO   | PRI |         |       | select,insert,update,references |         |
| User                  | char(16)      | utf8_bin        | NO   | PRI |         |       | select,insert,update,references |         |
| Select_priv           | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| Insert_priv           | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| Update_priv           | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| Delete_priv           | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| Create_priv           | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| Drop_priv             | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| Grant_priv            | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| References_priv       | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| Index_priv            | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| Alter_priv            | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| Create_tmp_table_priv | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| Lock_tables_priv      | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| Create_view_priv      | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| Show_view_priv        | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| Create_routine_priv   | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| Alter_routine_priv    | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| Execute_priv          | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| Event_priv            | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
| Trigger_priv          | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |
+-----------------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| db    | 0          | PRIMARY  | 1            | Host        | A         |             |          |        |      | BTREE      |         |
| db    | 0          | PRIMARY  | 2            | Db          | A         |             |          |        |      | BTREE      |         |
| db    | 0          | PRIMARY  | 3            | User        | A         | 2           |          |        |      | BTREE      |         |
| db    | 1          | User     | 1            | User        | A         | 1           |          |        |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

与查询结果等价一致

[root@ufo128 mysql]# mysql -uroot  mysql -e "show full columns from db; show index from db;"

显示表的一些状态信息，使用 -i 或 --status

[root@ufo128 mysql]# mysqlshow -uroot  test t2 -i
Database: test  Wildcard: t2
+------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| t2   | MyISAM | 10      | Fixed      | 11   | 7              | 77          | 1970324836974591 | 1024         | 0         |                | 2021-02-16 22:03:25 | 2021-02-16 22:19:24 |            | latin1_swedish_ci |          |                |         |
+------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+

[root@ufo128 mysql]# mysql -uroot  test -e "show table status from test like 't2'"
+------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| t2   | MyISAM |      10 | Fixed      |   11 |              7 |          77 | 1970324836974591 |         1024 |         0 |           NULL | 2021-02-16 22:03:25 | 2021-02-16 22:19:24 | NULL       | latin1_swedish_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+

[root@ufo128 mysql]# mysql -uroot  -e "show table status from test like 't2'"
+------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| t2   | MyISAM |      10 | Fixed      |   11 |              7 |          77 | 1970324836974591 |         1024 |         0 |           NULL | 2021-02-16 22:03:25 | 2021-02-16 22:19:24 | NULL       | latin1_swedish_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+

03. perror 错误代码查看工具

在mysql使用的过程中，会出现各种各样的error，有的是由于操作系统引起的，比如文件或目录不存在；有的则是由于存储引擎使用不当引起的，这些错误一般都有一个代码，类似于 " error：# " 或者 " Errcode： # "，#代表具体的错误号，perror的作用就是解释这些错误代码的详细含义。

示例

[root@ufo128 mysql]# perror 30 60
OS error code  30:  Read-only file system
OS error code  60:  Device not a stream

Zhao.Mr

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录