mysqladmin工具是MySQL官方提供的一个MySQL数据库管理工具,提供了众多的参数来管理数据库;比原生的MySQL客户端mysql工具要强大很多,特别是mysqladmin和shell结合来使用,可以很方便的实现数据库中各种状态的查看和统计。
mysqladmin常用命令介绍
Usage: mysqladmin [OPTIONS] command command....
参数选项,用的最多的是如下两个参数:
-i number 同 --sleep=number :每隔多长时间重复执行,单位是秒
-c number 同 --count=number :需要运行多少次命令,必须和-i一起使用
如果要查看mysqldadmin详细的参数和命令,可使用如下命令;
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 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
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
本地或远程连接数据库,查看数据库是否存活:
本地连接数据库:
mysql@Tdongkf[14:30:55]:~$mysqladmin -h 10.1.12.124 -P3310 -udbmgr -p ping
Enter password:
mysqld is alive
远程连接数据库,查看数据库是否存活:
mysql@Tdongkf[14:33:11]:~$mysqladmin --socket=/mysql/my3310/mysql.sock -uroot -p ping
Enter password:
mysqld is alive
查看数据库版本:
mysql@Tdongkf[14:33:19]:~$mysqladmin -h10.1.12.124 -udbmgr -pdbmgr1234 -P3310 version
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
h tables: 1 Open tables: 154 Queries per second avg: 0.023
查看数据库服务器状态:
36:57]:~$mysqladmin -h10.1.12.124 -udbmgr -p -P3310 version :
Enter password:
mysqladmin Ver 8.42 Distrib 5.7.22, for linux-glibc2.12 on x86_64
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
Other names may be trademarks of their respective
owners.
Server version 5.7.22
Protocol version 10
Connection 10.1.12.124 via TCP/IP
TCP port 3310
Uptime: 2 hours 25 min 14 sec
Threads: 6 Questions: 208 Slow queries: 0 Opens: 161 Flush tables: 1 Open tables: 154 Queries per second avg: 0.023
检查数据库服务器的各种状态变量:
输出结果太多,已忽略部分结果,mysqladmin extended-status命令可以获得所有MySQL性能指标,即show global status的输出,默认的,使用extended-status,看到是累计值,但是,加上参数-r(--relative),就可以看到各个指标的差值,配合参数-i(--sleep)就可以指定刷新的频率。
mysql@Tdongkf[14:38:17]:~$mysqladmin -h10.1.12.124 -udbmgr -p -P3310 extended-status
Enter password:
+------------------------------------+-------------+
| Variable_name | Value |
+------------------------------------+-------------+
| Aborted_clients | 0 |
| Aborted_connects | 6 |
| Binlog_cache_disk_use | 0 |
......
检查数据库服务器的各种状态参数信息:
mysql@Tdongkf[14:39:38]:~$mysqladmin -h10.1.12.124 -udbmgr -p -P3310 variables
Enter password:
+-----------------------------------------+-----------+
| Variable_name | Value |
+-------------------------------- --------+-----------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
......
查看数据库服务器的进程信息:
mysql@Tdongkf[14:41:38]:~$mysqladmin -h10.1.12.124 -udbmgr -p -P3310 processlist
Enter password:
+----+-------+--------------------+--------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+--------------------+--------+---------+------+----------+------------------+
| 3 | root | localhost | sys | Sleep | 7896 | | |
| 4 | dbmgr | 10.99.175.68:60542 | sakila | Sleep | 97 | | |
| 5 | dbmgr | 10.99.175.68:60543 | sakila | Sleep | 97 | |
......
kill线程会话:
mysql@Tdongkf[14:43:05]:~$mysqladmin -h10.1.12.124 -udbmgr -p -P3310 kill 7
Enter password:
mysql@Tdongkf[14:43:07]:~$
---查看会话已消失
修改用户密码:
mysql@Tdongkf[14:45:32]:~$mysqladmin -h10.1.12.124 -P3310 -udbmgr -p password 'dbmgr'
Enter password:
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
注:MySQL数据库提示使用明文密码不安全,建议使用ssl确保安全;再次连接需要用新密码连接;
不使用flush privilege命令,重载权限信息:
mysql@Tdongkf[14:46:58]:~$mysqladmin -h10.1.12.124 -udbmgr -p -P3310 reload
Enter password:
mysql@Tdongkf[14:47:01]:~$
不会有任何提示就执行完毕了。
创建数据库:
mysql@Tdongkf[14:48:00]:~$mysqladmin -h10.1.12.124 -udbmgr -p -P3310 create dbtestEnter password:
mysql@Tdongkf[14:48:02]:~$
查看数据库,已经创建完毕了:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbtest |
| mysql |
| performance_schema |
| sakila |
| sys |
+--------------------+
6 rows in set (0.00 sec)
删除数据库:
mysql@Tdongkf[14:48:02]:~$mysqladmin -h10.1.12.124 -udbmgr -p -P3310 drop dbtest
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'dbtest' database [y/N] y
Database "dbtest" dropped
mysql@Tdongkf[14:49:40]:~$
使用安全模式关闭数据库:
mysql@Tdongkf[14:50:10]:~$mysqladmin -h10.1.12.124 -udbmgr -p -P3310 shutdown
Enter password:
mysql@Tdongkf[14:50:12]:~$
提示:用户要有关闭数据库的权限才能正常关闭数据库。
各种刷新类命令:
flush-hosts: 刷新主机缓存信息
flush-tables: 刷新表缓存
flush-threads: 刷新线程缓存
flush-logs: 刷新日志
flush-privileges: 重载权限信息
flush-status: 重置状态变量
mysqladmin -h10.1.12.124 -udbmgr -p -P3310 flush-hosts
mysqladmin -h10.1.12.124 -udbmgr -p -P3310 flush-tables
mysqladmin -h10.1.12.124 -udbmgr -p -P3310 flush-threads
mysqladmin -h10.1.12.124 -udbmgr -p -P3310 flush-logs
mysqladmin -h10.1.12.124 -udbmgr -p -P3310 flush-privileges
mysqladmin -h10.1.12.124 -udbmgr -p -P3310 flush-status
管理复制:
mysqladmin -h10.1.12.124 -udbmgr -p -P3310 start-slave
mysqladmin -h10.1.12.124 -udbmgr -p -P3310 stop-slave
多个mysqladmin命令一起使用:
mysql@Tdongkf[14:53:25]:~$mysqladmin -h10.1.12.124 -udbmgr -p -P3310 processlist status version
Enter password:
+----+-------+-------------------+----+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-------------------+----+---------+------+----------+------------------+
| 2 | dbmgr | 10.1.12.124:44940 | | Query | 0 | starting | show processlist |
+----+-------+-------------------+----+---------+------+----------+------------------+
Uptime: 3 Threads: 1 Questions: 3 Slow queries: 0 Opens: 105 Flush tables: 1 Open tables: 98 Queries per second avg: 1.000
mysqladmin Ver 8.42 Distrib 5.7.22, for linux-glibc2.12 on x86_64
Copyright (c) 2000, 2018, 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.22
Protocol version 10
Connection 10.1.12.124 via TCP/IP
TCP port 3310
Uptime: 3 sec
Threads: 1 Questions: 4 Slow queries: 0 Opens: 105 Flush tables: 1 Open tables: 98 Queries per second avg: 1.333
除了单独使用各种命令外,还可以与操作系统的各种命令结合来使用,功能更强大。
统计状态变量:
mysql@Tdongkf[14:56:08]:~$mysqladmin -h10.1.12.124 -udbmgr -p -P3310 -r -i 1 extended-status |grep "Questions\|Queries\|Innodb_rows\|Com_select \|Com_insert \|Com_update \|Com_delete "
Enter password:
| Com_delete | 0 |
| Com_insert | 0 |
| Com_select | 0 |
| Com_update | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 8 |
| Innodb_rows_updated | 0 |
| Queries | 6 |
| Questions | 3
......
还可以和awk命令结合:
mysql@Tdongkf[14:57:48]:~$mysqladmin -h10.1.12.124 -udbmgr -p -P3310 -r -i 1 ext |awk -F"|" '{if($2 ~ /Variable_name/){print " ";}if($2 ~ /Questions|Queries|Innodb_rows|Com_select |Com_insert |Com_update |Com_delete |Innodb_buffer_pool_read_requests/)print $2 $3;}'
Enter password:
Com_delete 0
Com_insert 0
Com_select 0
Com_update 0
Innodb_buffer_pool_read_requests 1761
......
怎么样,利用mysqladmin输出的结果在shell中可以结合各种强大的命令来处理结果,方便我们查看各种MySQL数据库信息;
MySQL提供了许多管理工具来方便我们对数据库进行管理,这些工具本身也是非常稳定的,我们在运维的过程中,建议尽量使用官方提供的工具来操作,毕竟MySQL数据库也越来越强大和稳定了。
完
=end=