MySQL简单操作命令(用户权限，增删改查)

1、给MySQL添加密码

[root@db01 data]$ mysqladmin -uroot -p password 123456

2、进入MySQL，查询用户名主机及密码

[root@db01 data]$ mysql -uroot -p
Enter password: 123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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[(none)]>select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

mysql[(none)]>select user,host ,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

本次命令集合

mysql -uroot -p
select user,host from mysql.user;
select user,host ,authentication_string from mysql.user; #查询用户名密码

3、查看链接线程

mysql[(none)]>show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
| 12 | root | localhost | NULL | Query   |    0 | starting | show processlist |
| 13 | root | localhost | NULL | Sleep   |    4 |          | NULL             |
+----+------+-----------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

mysql[(none)]>show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
| 12 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)

4、简单操作命令

flush privileges;                               更新配置
desc mysql.user;                                查看数据库表中的列
show databases;                                 查看数据库
show tables;                                    查看数据库中的表

5、用户的基本管理操作

create user oldguo@'10.0.0.%';               创建用户
drop user oldguo@'10.0.0.%';                 删除用户
select user,host from mysql.user;            查找用户
show grants for oldguo@'10.0.0.%'            查看用户权限
select database();                           查看当前所在数据库
alter user oldguo@'10.0.0.%' identified by '123';                 修改用户密码
create user oldboy@'10.0.0.%' identified by '123';                建立用户时创建密码
revoke delete on wordpress.* from oldguo@'10.0.0.%';              收回用户权限
grant all on *.* to oldguo@'10.0.0.%' identified by '123';        授权用户权限  

超级管理员用户密码忘记如何操作

1、关闭数据库
systemctl stop mysql
2、将数据库启动到无验证模式
mysqld_safe --skip-grant-tables --skip-networking &
跳过验证表以及跳过网络的远程连接，并且后台运行
3、加载模块
flush privileges;
4、修改root密码
alter user root@'localhost' identified by '123'；
5、重启数据库

6、MySQL自带命令

mysql
        -u
        -p
        -h                指定连接主机IP地址
        -P                指定端口号
        -S                使用sock方式登录
        -e                免交互执行SQL语句
        -V                查看MySQL版本信息
        <
        --help            查询MySQL帮助

socket：文件默认路径/tmp/mysql.sock
        mysql -uroot -p123 -S /tmp/mysql.sock
注意：通过socket(本地登录)文件登录的用户必须有localhost权限
TCP/IP：
        mysql -uroot -p -h10.0.0.51 -P3306
show processlist;         查看连接信息