Linux中19个MySQL数据库管理命令
mysqladmin
是一个MySQL
服务器命令行工具,它由Database Administrators
执行一些基本的命令诸如更改 root 密码、监控 mysql 进程、重新加载权限、检查服务器状态等任务。
1. 如何更改 MySQL Root密码?
如果你想更改MySQL
root
密码,然后你需要键入以下命令。
-
旧密码
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
相关文章
- Mysql远程连接数据库报错排查:pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '**.**.**.**' (timed out)")
- Linux(Centos)下安装MySQL
- 【Mysql 学习】Linux RPM 注意事项
- 【C/C++学院】(23)Mysql数据库编程--C语言编程实现mysql客户端
- 【Linux】在linux上java工具jps jstat jinfo等命令找不到怎么办
- Linux - mysql 异常: ERROR! MySQL is not running, but lock file (/var/lock/subsys/mysql) exists
- linux - mysql 异常:Ignoring query to other database
- linux - mysql:查看 mysql 是否安装成功
- 在 linux 中连接 mysql 数据库
- MySQL选择数据库use与mysql_select_db使用详解
- Linux下远程连接MySQL数据库
- python-django-linux上mysql的安装和配置_20191124
- Ruby on Rails 3 Can't connect to local MySQL server through socket '/tmp/mysql.sock' on linux
- 使用mysql-connector-python操作MYSQL数据库
- linux(ubuntu) mysql安装使用
- Linux mysql添加用户,删除用户,以及用户权限
- Mysql 如果有多个可选条件怎么加索引_MySQL|mysql-索引
- MySQL用户权限(Host,User,Password)管理(mysql.user)
- 【已解决】linux下mysql报Failed to restart mysqld.service: Unit not found
- MYSQL导入数据报错|MYSQL导入超大文件报错|MYSQL导入大数据库报错:2006 - MySQL server has gone away
- linux下 安装mysql教程
- L82.linux命令每日一练 -- 第11章 Linux系统管理命令 -- dmidecode和lspci
- L61.linux命令每日一练 -- 第九章 Linux进程管理命令 -- renice和nohup
- L53.linux命令每日一练 -- 第八章 Linux磁盘与文件系统管理命令 -- dd和mount
- Groonga开源搜索引擎——列存储做聚合,没有内建分布式,分片和副本是随mysql或者postgreSQL作为存储引擎由MySQL自身来做分片和副本的
- MySQL缺失mysql_config文件
- mysql 数据库 分表后 怎么进行分页查询?Mysql分库分表方案?
- Mysql报错:Can't connect to local MySQL server through socket '/tmp/mysql.sock'
- Linux 使用docker搭建MySQL服务的一些细节问题
- Linux系统中安装mysql注意事项
- 【MySql】MySQL数据库--什么是MySQL的回表 ?
- Linux系统之安装mysql数据库