(1). 概述
在这一小节,主要是学习mysqladmin的使用法.
(2). mysqadmin命令格式
mysqladmin [OPTIONS] command command....
# [OPTIONS]常用选项
--character-sets-dir=utf8 # 指定字符集
-c : # 自动运行统计次数(与-i配合使用)
-i : # 间隔多长时间重复执行
-h : # 要连接mysql的主机地址
-u : # 用户名
-p : # 密码
-P : # 端口
Where command is a one or more of: (Commands may be shortened)
create databasename Create a new database # 创建数据库
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 # 刷新mysql日志
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 # kill线程
password [new-password] Change old password to new-password in current 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 # 关闭mysql
status Gives a short status message from the server # 查看mysql状态
start-slave Start slave
stop-slave Stop slave
variables Prints variables available
version Get version info from server
(3) mysqladmin常用命令
# 1. 创建测试数据库(hello-world)
lixin-macbook:~ lixin$ mysqladmin -h 127.0.0.1 -u root -p create hello-world
# 2. ping mysql
lixin-macbook:~ lixin$ mysqladmin -h 127.0.0.1 -u root -p123456 ping
mysqld is alive
# 3. 查看mysql的版本
lixin-macbook:~ lixin$ mysqladmin -h 127.0.0.1 -u root -p123456 version
mysqladmin Ver 8.42 Distrib 5.7.28, for macos10.14 on x86_64
Copyright (c) 2000, 2019, 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.
Server version 5.7.28-log
Protocol version 10
Connection 127.0.0.1 via TCP/IP
TCP port 3306
Uptime: 58 min 11 sec
Threads: 2 Questions: 31 Slow queries: 0 Opens: 112 Flush tables: 1 Open tables: 6 Queries per second avg: 0.008
# 4. 查看mysql status
lixin-macbook:~ lixin$ mysqladmin -h 127.0.0.1 -u root -p123456 status
Uptime: 3498 Threads: 2 Questions: 33 Slow queries: 0 Opens: 112 Flush tables: 1 Open tables: 6 Queries per second avg: 0.009
# 5. 修改账户对应的密码(我改完后,又再改回来了)
lixin-macbook:~ lixin$ mysqladmin -h 127.0.0.1 -u root -p123456 password 111111
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
# 6. 查看任务列表
# 只获得进程ID这一列
# lixin-macbook:sysbench lixin$ mysqladmin -h 127.0.0.1 -u root -p123456 processlist |awk '/[0-9]/{print $2}'
lixin-macbook:~ lixin$ mysqladmin -h 127.0.0.1 -u root -p123456 processlist
+----+------+-----------------+----+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+----+---------+------+----------+------------------+
| 20 | root | localhost | | Sleep | 127 | | |
| 22 | root | localhost:50545 | | Query | 0 | starting | show processlist |
+----+------+-----------------+----+---------+------+----------+------------------+
# 7. kill某个线程(可以kill多个线程)
lixin-macbook:~ lixin$ mysqladmin -h 127.0.0.1 -u root -p123456 kill 20,22
(4). mysqlshow使用
# mysqlshow [OPTIONS] [database [table [column]]]
# [OPTIONS]
# --count : 统计表数据行
# -k : 显示数据库的索引
# -t : 显示数据库类型
# -i : 显示数据表的额外信息
# 1. 统计employees库下所有表的rows数量
lixin-macbook:sysbench lixin$ mysqlshow -h 127.0.0.1 -u root -p123456 --count employees
Database: employees
+----------------------+----------+------------+
| Tables | Columns | Total Rows |
+----------------------+----------+------------+
| current_dept_emp | 4 | 300024 |
| departments | 2 | 11 |
| dept_emp | 4 | 331603 |
| dept_emp_latest_date | 3 | 300024 |
| dept_manager | 4 | 24 |
| employees | 6 | 300024 |
| salaries | 4 | 2844047 |
| titles | 4 | 443308 |
+----------------------+----------+------------+
# 2. 统计employees库下employees表的rows
lixin-macbook:sysbench lixin$ mysqlshow -h 127.0.0.1 -u root -p123456 --count employees employees
# *************************************************************
Database: employees Table: employees Rows: 300024
# *************************************************************
+------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| emp_no | int(11) | | NO | PRI | | | select,insert,update,references | |
| birth_date | date | | NO | | | | select,insert,update,references | |
| first_name | varchar(14) | utf8_general_ci | NO | | | | select,insert,update,references | |
| last_name | varchar(16) | utf8_general_ci | NO | | | | select,insert,update,references | |
| gender | enum('M','F') | utf8_general_ci | NO | | | | select,insert,update,references | |
| hire_date | date | | NO | | | | select,insert,update,references | |
+------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
# 3. 显示:employees库下employees表的索引信息
lixin-macbook:sysbench lixin$ mysqlshow -h 127.0.0.1 -u root -p123456 -k employees employees
Database: employees Table: employees
+------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| emp_no | int(11) | | NO | PRI | | | select,insert,update,references | |
| birth_date | date | | NO | | | | select,insert,update,references | |
| first_name | varchar(14) | utf8_general_ci | NO | | | | select,insert,update,references | |
| last_name | varchar(16) | utf8_general_ci | NO | | | | select,insert,update,references | |
| gender | enum('M','F') | utf8_general_ci | NO | | | | select,insert,update,references | |
| hire_date | date | | NO | | | | select,insert,update,references | |
+------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees | 0 | PRIMARY | 1 | emp_no | A | 299025 | | | | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
# 4. 显示表的额外信息
lixin-macbook:sysbench lixin$ mysqlshow -h 127.0.0.1 -u root -p123456 -i employees employees
Database: employees Wildcard: employees
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | | 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 |
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| employees | InnoDB | 10 | Dynamic | 299025 | 50 | 15220736 | 0 | 0 | 4194304 | | 2021-06-15 20:03:14 | | | utf8_general_ci | | | |
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
