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; 查看连接信息
