Linux中19个MySQL数据库管理命令

mysqladmin是一个MySQL服务器命令行工具，它由Database Administrators执行一些基本的命令诸如更改 root 密码、监控 mysql 进程、重新加载权限、检查服务器状态等任务。

1. 如何更改 MySQL Root密码？

如果你想更改MySQLroot 密码，然后你需要键入以下命令。

旧密码123456
新密码rumenz123

> mysqladmin -u root -p 123456 password rumenz123

2. 如何检查 MySQL Server是否运行？

查看MySQL服务器运行状态


 
> mysqladmin -u root -p ping
 
Enter password:
mysqld is alive

3. 查看MySQL版本？

显示MySQL版本以及当前运行状态。


> mysqladmin -u root -p version
 
Enter password:
mysqladmin  Ver 8.0.27 for macos10.14 on x86_64 (Homebrew)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Server version  8.0.27
Protocol version 10
Connection  Localhost via UNIX socket
UNIX socket  /tmp/mysql.sock
Uptime:   5 days 5 hours 46 min 40 sec
 
Threads: 2  Questions: 140408  Slow queries: 0  Opens: 727  Flush tables: 3  Open tables: 624  Queries per second avg: 0.310

4. 查看 MySQL服务器的当前状态？

查看当前MySQL服务器状态。mysqladmin命令显示状态uptime，threads和queries.


> mysqladmin -u root -p  status
 
Uptime: 452778  Threads: 2  Questions: 140406  Slow queries: 0  Opens: 727  Flush tables: 3  Open tables: 624  Queries per second avg: 0.310

5. 查看所有 MySQL服务器变量和值的状态？

检查MySQL服务器变量和值


> mysqladmin -u root -p extended-status
 
Enter password:
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 3           |
| Aborted_connects                         | 3           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 0           |
| Binlog_stmt_cache_disk_use               | 0           |
| Binlog_stmt_cache_use                    | 0           |
| Bytes_received                           | 6400357     |
| Bytes_sent                               | 2610105     |
| Com_admin_commands                       | 3           |
| Com_assign_to_keycache                   | 0           |
| Com_alter_db                             | 0           |
| Com_alter_db_upgrade                     | 0           |
| Com_alter_event                          | 0           |
| Com_alter_function                       | 0           |
| Com_alter_procedure                      | 0           |
| Com_alter_server                         | 0           |
| Com_alter_table                          | 0           |
| Com_alter_tablespace                     | 0           |
+------------------------------------------+-------------+

6. 查看所有 MySQL服务器的变量和值？

查看MySQL变量和值


> mysqladmin  -u root -p variables
 
Enter password:
+---------------------------------------------------+----------------------------------------------+
| Variable_name                                     | Value                                        |
+---------------------------------------------------+----------------------------------------------+
| auto_increment_increment                          | 1                                            |
| auto_increment_offset                             | 1                                            |
| autocommit                                        | ON                                           |
| automatic_sp_privileges                           | ON                                           |
| back_log                                          | 50                                           |
| basedir                                           | /usr                                         |
| big_tables                                        | OFF                                          |
| binlog_cache_size                                 | 32768                                        |
| binlog_direct_non_transactional_updates           | OFF                                          |
| binlog_format                                     | STATEMENT                                    |
| binlog_stmt_cache_size                            | 32768                                        |
| bulk_insert_buffer_size                           | 8388608                                      |
| character_set_client                              | latin1                                       |
| character_set_connection                          | latin1                                       |
| character_set_database                            | latin1                                       |
| character_set_filesystem                          | binary                                       |
| character_set_results                             | latin1                                       |
| character_set_server                              | latin1                                       |
| character_set_system                              | utf8                                         |
| character_sets_dir                                | /usr/share/mysql/charsets/                   |
| collation_connection                              | latin1_swedish_ci                            |
+---------------------------------------------------+----------------------------------------------+

7.查看MySQL服务器的所有运行进程？


> mysqladmin -u root -p processlist
 
Enter password:
+-------+---------+-----------------+---------+---------+------+-------+------------------+
| Id    | User    | Host            | db      | Command | Time | State | Info             |
+-------+---------+-----------------+---------+---------+------+-------+------------------+
| 18001 | rsyslog | localhost:38307 | rsyslog | Sleep   | 5590 |       |                  |
| 18020 | root    | localhost       |         | Query   | 0    |       | show processlist |
+-------+---------+-----------------+---------+---------+------+-------+------------------+

8. 在 MySQL 服务器中创建数据库？

创建数据库。


> mysqladmin -u root -p create rumenz_test
 
Enter password:

9. 在 MySQL 服务器中删除数据库？

通过mysqladmin创建数据库


> mysqladmin -u root -p drop rumenz_test
 
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 'rumenz_test' database [y/N] y
Database "rumenz_test" dropped

10. 重新加载/刷新 MySQL 权限？

reload命令告诉服务器重新加载授权表。refresh命令刷新所有表并重新打开日志文件。


> mysqladmin -u root -p reload
> mysqladmin -u root -p refresh

11. 安全关闭 MySQL 服务器？


> mysqladmin -u root -p shutdown
 
Enter password:

12. MySQL Flush 常用命令

flush-hosts：从主机缓存中刷新所有主机信息。
flush-tables: 刷新所有表。
flush-threads：刷新所有线程缓存。
flush-logs：刷新所有信息日志。
flush-privileges：重新加载授权表（与重新加载相同）。
flush-status：清除状态变量。


# mysqladmin -u root -p flush-hosts
# mysqladmin -u root -p flush-tables
# mysqladmin -u root -p flush-threads
# mysqladmin -u root -p flush-logs
# mysqladmin -u root -p flush-privileges
# mysqladmin -u root -p flush-status

13. 杀死正在休眠的 MySQL 客户端进程？

找出休眠的进程


> mysqladmin -u root -p processlist
 
Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 5  | root | localhost |    | Sleep   | 14   |       |      |
| 8  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

用kill和process ID杀死


> mysqladmin -u root -p kill 5
 
Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 12 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

如果需要kill多个进程，用逗号分隔多个process ID

> mysqladmin -u root -p kill 5,10

14、同时运行多个mysqladmin命令？

同时执行多个 mysqladmin 命令


> mysqladmin  -u root -p processlist status version
 
Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 8  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
Uptime: 3801  Threads: 1  Questions: 15  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 003
mysqladmin  Ver 42 Distrib 28, for Linux on i686
Copyright (c) 2000, 2012, 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          28
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 1 hour 3 min 21 sec
 
Threads: 1  Questions: 15  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 003

15.连接远程mysql服务器

连接远程MySQL服务器，使用-h(host）和IP Address参数

> mysqladmin  -h rumenz.com -u root -p

16.在远程MySQL服务器上执行命令

如果想查看远程的MySQL服务器的status

> mysqladmin  -h rumenz.com -u root -p status

17. 在从服务器上启动/停止 MySQL 主从复制？

要在从服务器上启动/停止MySQL 复制，使用以下命令。

> mysqladmin  -u root -p start-slave

> mysqladmin  -u root -p stop-slave

18. 将 MySQL 服务器的 Debug Information 存储到日志中？

将有关正在使用的锁、已用内存和查询使用情况的调试信息写入MySQL日志文件(包括有关事件调度程序的信息)。


> mysqladmin  -u root -p debug
 
Enter password:

19.查看mysqladmin帮助

了解更多myslqadmin命令选项和用法。

> mysqladmin --help

相关阅读:
脑洞golang embed 的使用场景
 【软件测试】自动化测试如何管理测试数据
 直播预告 | 全新定义业务观测新范式，让稳定更有力量
 进销存软件排行榜前十名！
C语言迭代法求一个数的平方根。迭代公式：Xn+1=(Xn+a/Xn)/2，其中a是输入的数字
 ACM近年区域赛的所有题型
 【html5期末大作业】基于HTML+CSS+JavaScript管理系统页面模板
 因果推断之初介绍 + 案例分析
 Mac环境下反编译apk
Java线程池内含2023最新面试题
原文地址：https://blog.csdn.net/zfw_666666/article/details/126846970