文章转自华为云社区，作者：Tiamo_T，原文链接：https://bbs.huaweicloud.com/blogs/309025
在以下所有 15 个 mysqladmin 命令行示例中，tmppassword 用作 MySQL 根用户密码。
请将其更改为您的 MySQL 根密码。
1、如何修改MySQL root用户密码？
# mysqladmin -u root -ptmppassword password 'newpassword' # mysql -u root -pnewpassword Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.1.25-rc-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>复制
2. 如何检查 MySQL Server 是否启动并运行？
# mysqladmin -u root -p ping Enter password: mysqld is alive复制
3. 我如何知道我正在运行什么版本的 MySQL？
除了给出“服务器版本”之外，此命令还显示 mysql 服务器的当前状态。
# mysqladmin -u root -ptmppassword version mysqladmin Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686 Copyright (C) 2000-2006 MySQL AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 5.1.25-rc-community Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 107 days 6 hours 11 min 44 sec Threads: 1 Questions: 231976 Slow queries: 0 Opens: 17067 Flush tables: 1 Open tables: 64 Queries per second avg: 0.25复制
4. MySQL服务器的现状如何？
# mysqladmin -u root -ptmppassword status Uptime: 9267148 Threads: 1 Questions: 231977 Slow queries: 0 Opens: 17067 Flush tables: 1 Open tables: 64 Queries per second avg: 0.25复制
status 命令显示以下信息：
- Uptime：mysql 服务器的正常运行时间（以秒为单位）
- 线程：连接到服务器的客户端总数。
- 问题：服务器自启动以来执行的查询总数。
- 慢查询：执行时间超过 long_query_time 变量值的查询总数。
- Opens：服务器打开的表总数。
- 刷新表：刷新表的次数。
- 打开表：数据库中打开的表总数。
5. 如何查看所有 MySQL Server 状态变量及其当前值？
# mysqladmin -u root -ptmppassword extended-status +-----------------------------------+-----------+ | Variable_name | Value | +-----------------------------------+-----------+ | Aborted_clients | 579 | | Aborted_connects | 8 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 41387238 | | Bytes_sent | 308401407 | | Com_admin_commands | 3524 | | Com_assign_to_keycache | 0 | | Com_alter_db | 0 | | Com_alter_db_upgrade | 0 |复制
6.如何显示所有MySQL服务器系统变量和值？
# mysqladmin -u root -ptmppassword variables +---------------------------------+---------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------+ | auto_increment_increment | 1 | | basedir | / | | big_tables | OFF | | binlog_format | MIXED | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | skip..... | time_format | %H:%i:%s | | time_zone | SYSTEM | | timed_mutexes | OFF | | tmpdir | /tmp | | tx_isolation | REPEATABLE-READ | | unique_checks | ON | | updatable_views_with_limit | YES | | version | 5.1.25-rc-community | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | i686 | | version_compile_os | redhat-linux-gnu | | wait_timeout | 28800 | +---------------------------------+---------------------------------+复制
7、如何显示mysql数据库中所有正在运行的进程/查询？
# mysqladmin -u root -ptmppassword processlist +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 20 | root | localhost | | Sleep | 36 | | | | 23 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+复制
您可以有效地使用此命令来调试任何性能问题并识别导致问题的查询，方法是每 1 秒自动运行一次该命令，如下所示。
# mysqladmin -u root -ptmppassword -i 1 processlist +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 20 | root | localhost | | Sleep | 36 | | | | 23 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+ +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 24 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+复制
管理 MySQL 服务器的 Mysql admin 命令的 15 个实际用法
Tiamo_T 发表于 2021/11/12 09:41:31 317 0 0
【摘要】 管理 MySQL 服务器的 Mysql admin 命令的 15 个实际用法在以下所有 15 个 mysqladmin 命令行示例中，tmppassword 用作 MySQL 根用户密码。请将其更改为您的 MySQL 根密码。1、如何修改MySQL root用户密码？# mysqladmin -u root -ptmppassword password ‘newpassword’# mysq…
管理 MySQL 服务器的 Mysql admin 命令的 15 个实际用法
在以下所有 15 个 mysqladmin 命令行示例中，tmppassword 用作 MySQL 根用户密码。
请将其更改为您的 MySQL 根密码。
1、如何修改MySQL root用户密码？
mysqladmin -u root -ptmppassword password ‘newpassword’
mysql -u root -pnewpassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>
- 如何检查 MySQL Server 是否启动并运行？
mysqladmin -u root -p ping
Enter password:
mysqld is alive
3. 我如何知道我正在运行什么版本的 MySQL？
除了给出“服务器版本”之外，此命令还显示 mysql 服务器的当前状态。
mysqladmin -u root -ptmppassword version
mysqladmin Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686
Copyright © 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.1.25-rc-community
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 107 days 6 hours 11 min 44 sec
Threads: 1 Questions: 231976 Slow queries: 0 Opens: 17067
Flush tables: 1 Open tables: 64 Queries per second avg: 0.25
4. MySQL服务器的现状如何？
mysqladmin -u root -ptmppassword status
Uptime: 9267148
Threads: 1 Questions: 231977 Slow queries: 0 Opens: 17067
Flush tables: 1 Open tables: 64 Queries per second avg: 0.25
status 命令显示以下信息：
Uptime：mysql 服务器的正常运行时间（以秒为单位）
线程：连接到服务器的客户端总数。
问题：服务器自启动以来执行的查询总数。
慢查询：执行时间超过 long_query_time 变量值的查询总数。
Opens：服务器打开的表总数。
刷新表：刷新表的次数。
打开表：数据库中打开的表总数。
5. 如何查看所有 MySQL Server 状态变量及其当前值？
mysqladmin -u root -ptmppassword extended-status
±----------------------------------±----------+
| Variable_name | Value |
±----------------------------------±----------+
| Aborted_clients | 579 |
| Aborted_connects | 8 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 41387238 |
| Bytes_sent | 308401407 |
| Com_admin_commands | 3524 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
6.如何显示所有MySQL服务器系统变量和值？
mysqladmin -u root -ptmppassword variables
±--------------------------------±--------------------------------+
| Variable_name | Value |
±--------------------------------±--------------------------------+
| auto_increment_increment | 1 |
| basedir | / |
| big_tables | OFF |
| binlog_format | MIXED |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
skip…
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmpdir | /tmp |
| tx_isolation | REPEATABLE-READ |
| unique_checks | ON |
| updatable_views_with_limit | YES |
| version | 5.1.25-rc-community |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | i686 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 28800 |
±--------------------------------±--------------------------------+
7、如何显示mysql数据库中所有正在运行的进程/查询？
mysqladmin -u root -ptmppassword processlist
±—±-----±----------±—±--------±-----±------±-----------------+
| Id | User | Host | db | Command | Time | State | Info |
±—±-----±----------±—±--------±-----±------±-----------------+
| 20 | root | localhost | | Sleep | 36 | | |
| 23 | root | localhost | | Query | 0 | | show processlist |
±—±-----±----------±—±--------±-----±------±-----------------+
您可以有效地使用此命令来调试任何性能问题并识别导致问题的查询，方法是每 1 秒自动运行一次该命令，如下所示。
mysqladmin -u root -ptmppassword -i 1 processlist
±—±-----±----------±—±--------±-----±------±-----------------+
| Id | User | Host | db | Command | Time | State | Info |
±—±-----±----------±—±--------±-----±------±-----------------+
| 20 | root | localhost | | Sleep | 36 | | |
| 23 | root | localhost | | Query | 0 | | show processlist |
±—±-----±----------±—±--------±-----±------±-----------------+
±—±-----±----------±—±--------±-----±------±-----------------+
| Id | User | Host | db | Command | Time | State | Info |
±—±-----±----------±—±--------±-----±------±-----------------+
| 24 | root | localhost | | Query | 0 | | show processlist |
±—±-----±----------±—±--------±-----±------±-----------------+
8.如何创建MySQL数据库？
# mysqladmin -u root -ptmppassword create testdb # mysql -u root -ptmppassword Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 705 Server version: 5.1.25-rc-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | sugarcrm | | testdb | +--------------------+ 4 rows in set (0.00 sec)复制
9. 如何删除/删除现有的 MySQL 数据库？
# mysqladmin -u root -ptmppassword drop testdb 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 'testdb' database [y/N] y Database "testdb" dropped # mysql -u root -ptmppassword Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 707 Server version: 5.1.25-rc-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | sugarcrm | +--------------------+ 3 rows in set (0.00 sec)复制
10. 如何重新加载/刷新特权或授权表？
# mysqladmin -u root -ptmppassword reload;复制
刷新命令将刷新所有表并关闭/打开日志文件。
# mysqladmin -u root -ptmppassword refresh复制
11.关闭MySQL服务器的安全方法是什么？
# mysqladmin -u root -ptmppassword shutdown # mysql -u root -ptmppassword ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'复制
注意：您也可以使用“/etc/rc.d/init.d/mysqld stop”来关闭服务器。要启动服务器，请执行“/etc/rc.d/init.d/mysql start”
12.所有mysqladmin刷新命令的列表。
# mysqladmin -u root -ptmppassword flush-hosts # mysqladmin -u root -ptmppassword flush-logs # mysqladmin -u root -ptmppassword flush-privileges # mysqladmin -u root -ptmppassword flush-status # mysqladmin -u root -ptmppassword flush-tables # mysqladmin -u root -ptmppassword flush-threads复制
- flush-hosts：刷新主机缓存中的所有信息。
- 刷新权限：重新加载授权表（与重新加载相同）。
- 刷新状态：清除状态变量。
- 刷新线程：刷新线程缓存。
13. 如何杀死挂起的 MySQL 客户端进程？
首先使用 processlist 命令识别挂起的 MySQL 客户端进程。
# mysqladmin -u root -ptmppassword processlist +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 20 | root | localhost | | Sleep | 64 | | | | 24 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+复制
现在，使用 kill 命令并传递 process_id，如下所示。要杀死多个进程，您可以传递逗号分隔的进程 ID。
# mysqladmin -u root -ptmppassword kill 20 # mysqladmin -u root -ptmppassword processlist +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 26 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+复制
14.如何在从服务器上启动和停止MySQL复制？
# mysqladmin -u root -ptmppassword stop-slave Slave stopped # mysqladmin -u root -ptmppassword start-slave mysqladmin: Error starting slave: The server is not configured as slave; fix in config file or with CHANGE MASTER TO复制
15.如何将多个mysqladmin命令组合在一起？
在下面的示例中，您可以组合 process-list、status 和 version 命令以将所有输出放在一起，如下所示。
# mysqladmin -u root -ptmppassword process status version +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 43 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+ Uptime: 3135 Threads: 1 Questions: 80 Slow queries: 0 Opens: 15 Flush tables: 3 Open tables: 0 Queries per second avg: 0.25 mysqladmin Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686 Copyright (C) 2000-2006 MySQL AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 5.1.25-rc-community Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 52 min 15 sec复制
您还可以使用如下所示的简短形式：
# mysqladmin -u root -ptmppassword pro stat ver复制
使用选项 -h，连接到远程 MySQL 服务器并执行如下所示的 mysqladmin 命令。
# mysqladmin -h 192.168.1.12 -u root -ptmppassword pro stat ver复制
