管理 MySQL 服务器的 Mysql admin 命令的 15 个实际用法
【摘要】 管理 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>2. 如何检查 MySQL Server 是否启动并运行？
# mysqladmin -u root -p ping
Enter password:
mysqld is alive3. 我如何知道我正在运行什么版本的 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.254. 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.25status 命令显示以下信息：
- 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 refresh11.关闭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 TO15.如何将多个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
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）