Linux中数据库管理的20个MySQL(Mysqladmin)命令
Linux中数据库管理的20个MySQL(Mysqladmin)命令
入门笔记
发布于 2022-06-02 15:48:08
发布于 2022-06-02 15:48:08
运行总次数：0
1.如何设置MySQL Root密码？
如果你有全新安装的
MySQL服务器，则不需要任何密码即可以 root 用户身份连接它。设置MySQLroot 用户的密码，使用以下命令。
# mysqladmin -u root password `YOURNEWPASSWORD`
2. 如何更改 MySQL Root 密码？
如果你想更改或更新
MySQLroot 密码，那么你需要输入以下命令。例如，假设你的旧密码是123456改成rumenz123.
# mysqladmin -uroot -p123456 password rumenz123
3. 如何检查 MySQL Server 是否正在运行？
找出是否
MySQL服务器已启动并正在运行，请使用以下命令。
# mysqladmin -u root -p ping
Enter password:
mysqld is alive
4. 如何查看我运行的是哪个 MySQL 版本？
以下命令显示
MySQL版本以及当前的运行状态。
# mysqladmin -u root -p version
Enter password:
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: 7 days 14 min 45 sec
Threads: 2 Questions: 36002 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 059
5. 如何查看 MySQL 服务器的当前状态？
要了解当前状态
MySQL服务器，使用以下命令。
# mysqladmin -u root -ptmppassword status
Enter password:
Uptime: 606704 Threads: 2 Questions: 36003 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 059
6. 如何查看所有 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 |
+------------------------------------------+-------------+
7. 如何查看所有 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 |
+---------------------------------------------------+----------------------------------------------+
8.如何查看MySQL服务器的所有运行进程？
以下命令将显示所有运行过程
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 |
+-------+---------+-----------------+---------+---------+------+-------+------------------+
9. 如何在 MySQL 服务器中创建数据库？
在中创建新数据库
MySQL服务器，使用如下所示的命令。
# mysqladmin -u root -p create databasename
Enter password:
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 18027
Server version: 28 MySQL Community Server (GPL) by Remi
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.
Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| `databasename` |
| mysql |
| test |
+--------------------+
8 rows in set (01 sec)
mysql>
10. 如何删除 MySQL 服务器中的数据库？
将数据库放入
MySQL服务器，使用以下命令。你将被要求确认按y。
# mysqladmin -u root -p drop databasename
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 'databasename' database [y/N] y
Database "databasename" dropped
11. 如何重新加载/刷新 MySQL 权限？
reload命令告诉服务器重新加载授权表。refresh命令刷新所有表并重新打开日志文件。
# mysqladmin -u root -p reload;
# mysqladmin -u root -p refresh
12. 如何安全关闭 MySQL 服务器？
去关机
MySQL服务器安全，键入以下命令。
mysqladmin -u root -p shutdown
Enter password:
你还可以使用以下命令来启动/停止 MySQL 服务器。
# /etc/init.d/mysqld stop
# /etc/init.d/mysqld start
13. 一些有用的 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
14. 如何杀死休眠的 MySQL 客户端进程？
使用以下命令打印睡眠
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
15.如何同时运行多个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
16.如何连接远程mysql服务器
连接远程
MySQL服务器，使用-h(host） 和IP Address远程机器。
# mysqladmin -h 1126 -u root -p
17.如何在远程MySQL服务器上执行命令
假设你想看
status远程的MySQL服务器，那么命令就是。
# mysqladmin -h 1126 -u root -p status
18.如何在从服务器上启动/停止MySQL复制？
要在从服务器上启动/停止MySQL 复制，请使用以下命令。
# mysqladmin -u root -p start-slave
# mysqladmin -u root -p stop-slave
19.如何将MySQL服务器调试信息存储到日志中？
它告诉服务器将有关使用中的锁、已用内存和查询使用情况的调试信息写入
MySQL日志文件
# mysqladmin -u root -p debug
Enter password:
20.如何查看mysqladmin的选项和用法
了解更多选项和用法
myslqadmin命令使用 help 命令，如下所示。它将显示可用选项列表。
# mysqladmin --help
相关文章
MySQL基本数据库管理命令
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2021-09-27，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
