📝 主旨内容

权限认证的过程

• 从mysql.user表中验证host(连接主机)、user(连接用户名)和password(连接密码)

• 验证通过后，按照以下顺序获取权限:mysql.user → mysql.db → mysql.tables_priv → mysql.column_priv

账户、权限管理

# 创建u1@localhost账户，密码设置为123,默认只有USAGE权限，若要其他权限需用GRANT赋权
mysql> CREATE USER 'u1'@'localhost' IDENTIFIED BY '123';

# 赋予用户u1@localhost所有库的权限.
mysql>GRANT ALL privileges ON *.* to 'u1'@'localhost';

# 赋予用户u1@localhost所有库的权限，且赋予grant权限.
mysql>GRANT ALL privileges ON *.* to 'u1'@'localhost' WITH GRANT OPTION;

# 赋予用户u1@localhost所有库的权限，且赋予grant权限,密码为123.
mysql>GRANT ALL privileges ON *.* to 'u1'@'localhost' IDENTIFIED BY '123' WITH GRANT OPTION;

# 创建账户u2，可以从任意IP登陆，只对test1库有SELECT、UPDATE权限,且密码为123
mysql>GRANT SELECT,UPDATE ON test1.* to 'u2'@'%' IDENTIFIED BY '123';

# 创建账户u3@%，赋予SUPER、PROCESS、FILE权限
mysql>GRANT SUPER,PROCESS,FILE ON *.* to 'u3'@'%';

# 修改账户密码
# 方法1：SET PASSWORD
mysql> SET PASSWORD FOR 'u2'@'%' = PASSWORD('234');

# 方法2：UPDATE
mysql> UPDATE mysql.user SET password=PASSWORD('123') WHERE USER='root' AND HOST='localhost';
mysql>FLUSH PRIVILEGES;

# 方法3：ALTER
mysql>ALTER USER 'u1'@'localhost' IDENTIFIED BY ‘123’;
mysql>FLUSH PRIVILEGES;

# 方法4：linux命令行修改密码
[root@localhost]# mysqladmin -uroot -p123(旧密码) password 234(新密码)

# 方法5：修改user表(不建议)
mysql> INSERT INTO mysql.user (Host,User,Password) VALUES('localhost', 'u1','PASSWORD(234)');
mysql> FLUSH PRIVILEGES;

# 方法6：GRANT(不建议)
mysql> GRANT USAGE ON *.* TO 'u1'@'localhost' IDENTIFIED BY 'xyz';

# 查看用户u1@localhost权限
mysql> SHOW GRANTS FOR 'u1'@'localhost';

# 回收账户u1@localhost权限
mysql>REVOKE SELECT ON *.* FROM 'u1'@'localhost';

# 删除账户
mysql>DROP USER 'u1'@'localhost';

重置root密码

• 关闭mysql进程

shell> kill `pidof mysqld

• 修改mysql配置

shell> vim /path/to/my.cnf # default: /etc/my.cnf
# 添加下面2行配置
skip-grant-tables #禁用权限认证
skip-networking   #禁用网络认证

• 登录mysql，并刷新权限

shell> mysql
mysql> FLUSH PRIVILEGES;

• 修改root密码

MySQL 5.7.6 and later:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

MySQL 5.7.5 and earlier:
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

• 删除配置，重启mysql

shell> vim /path/to/my.cnf # default: /etc/my.cnf
# 删除下面2行配置
skip-grant-tables #禁用权限认证
skip-networking   #禁用网络认证

shell> /etc/init.d/mysqld restart

🤗 总结归纳

📎 参考文章