一、Linux下 Mysql8.0 密码重置
1.进入 my.cnf，在[mysqld]的段中加上一句: skip-grant-tables。跳过权限。
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
port = 3306
socket = /tmp/mysql.sock
# bind-address = 127.0.0.1
# bind-address = 0.0.0.0
[mysqld]
port = 3306
server-id = 3306
user = mysql
socket = /tmp/mysql.sock
#跳过权限
skip-grant-tables
2.重新启动mysqld，清空密码。
[docker]
1.查找运行的mysql容器实例
# docker ps
2.重启容器实例
# docker restart ID
[mysql 实例]
# service mysql restart
[进入Mysql] 清空密码
# mysql -h localhost -u root
# bin/mysql -h 127.0.0.1 -P 3306 -u root -pxxxxxx
MySQL [(none)]> use mysql;
Database changed
MySQL [mysql]> update user set authentication_string='' where user='root' and host= 'localhost';
MySQL [mysql]> select user,host,plugin,authentication_string from user where user='root' and host= 'localhost';
MySQL [mysql]> exit;
3.注掉 skip-grant-tables，重启服务。
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
port = 3306
socket = /tmp/mysql.sock
# bind-address = 127.0.0.1
# bind-address = 0.0.0.0
[mysqld]
port = 3306
server-id = 3306
user = mysql
socket = /tmp/mysql.sock
#跳过权限
# skip-grant-tables
4.重置密码
1.重启mysql
[docker]
a.查找运行的mysql容器实例
# docker ps
b.重启容器实例
# docker restart ID
[mysql 实例]
# service mysql restart
2.进入mysql
# mysql -u root
# mysql -h localhost -u root
3.进入mysql后, 切换数据库至mysql
MySQL [(none)]> use mysql;
Database changed
4.重置密码,并刷新权限
MySQL [mysql]> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
MySQL [mysql]> flush privileges;
二、Mysql用户授权
1.用户授权
# grant #auth on #databaseName.#table to '#userName'@'#host';
#auth 代表权限，如下:
+ all privileges 全部权限
+ select 查询权限
+ select,insert,update,delete 增删改查权限
+ select,[…]增…等权限
#databaseName 代表数据库名#table 代表具体表，如下
+ *.*代表全部表
+A.B 代表具体A,B表
#userName 代表用户名
#host 代表访问权限，如下
+ %代表通配所有host地址权限(可远程访问)
+ localhost为本地权限(不可远程访问)
+ 指定特殊Ip访问权限 如10.138.106.102
2.查看用户授权
SHOW GRANTS FOR 'username'@'hostname';
SHOW GRANTS FOR 'dsp2waff'@'%';
栗子：
MySQL [mysql]> select user,host,plugin,authentication_string from user where user = 'dsp2';
MySQL [mysql]> create user 'dsp2waff'@'%';
MySQL [mysql]> alter user 'dsp2waff'@'%' identified with mysql_native_password by 'xtdesoft';
MySQL [mysql]> grant select,execute on dsp.* to 'dsp2waff'@'%';
MySQL [mysql]> flush privileges;
MySQL [mysql]> grant select on waff_mini_test.* to 'waffmini'@'%';
MySQL [mysql]> flush privileges;
MySQL [mysql]> SHOW GRANTS FOR 'dsp2waff'@'%';
MySQL [mysql]> create user 'waff'@'%';
MySQL [mysql]> alter user 'waff'@'%' identified with mysql_native_password by 'xtdesoft';
MySQL [mysql]> grant all privileges on waff.* to 'waff'@'%';
MySQL [mysql]> flush privileges;
参考与引用
(46条消息) Linux版mysql8.0强制重置密(详细步骤)，再也不怕密码忘记了!_十年少i的博客-CSDN博客 (46条消息) mysql 8.0 创建用户并赋权限_小丶达的博客-CSDN博客_mysql8.0赋予权限
