这篇文章上次修改于 2437 天前,可能其部分内容已经发生变化,如有疑问可询问作者。 给 root 用户设置密码: ``` mysqladmin -u root password ``` 修改 root 用户的密码: ``` mysqladmin -u root password Enter password: ```` root@localhost 和 root@127.0.0.1 的区别: 前者是通过 sock 连接,而后者是通过 TCP 连接。 查看数据库中的用户: ``` select host, user from mysql.user; ``` 查看当前用户权限: ``` show grants; ``` 查看指定用户权限: ``` show grants for @; ``` 普通用户权限(查询、插入、更新、删除 表数据): ``` grant select, insert, update, delete on test.* to test@'%'; ``` 开发人员权限: 创建、修改、删除 表的权限: ``` grant create, alter, drop on test.* to dev@'192.168.0.%'; ``` 外键: references 临时表: create temporary tables 索引: index 创建视图: create view 查看视图源代码: show view 创建存储过程: create routine 修改存储过程: alter routine 函数: execute 普通数据库管理员权限: ``` grant all on test.* to testdba@'localhost'; ``` 顶级权限: ``` grant all on *.* to dba@'localhost'; ``` 顶级权限加授权权限: ``` grant all on *.* to root@'localhost' with grant option; ``` 撤销权限: ``` revoke on . from ''@''; ``` 撤销所有权限: ``` revoke all on *.* from dba@'localhost'; ``` “授权”权限: ``` grant all on test.* to testdba@localhost with grant option; revoke grant option on test.* from testdba@localhost; ``` 添加用户: ``` grant all on . to ''@'' identified by ''; flush privileges; ``` 删除用户: ``` delete from mysql.user where user='' and host=''; flush privileges; ``` 更改用户主机: ``` update mysql.user set host='127.0.0.1' where user='test'; ``` 修改用户密码: ``` set password for ''@'' = password(''); ``` 忘记 root 密码重置: 在 my.cnf 文件的 [mysqld] 段添加 skip-grant-tables 后重启,可以免密码登陆,然后重置 root 的密码, ``` update mysql.user set password=password('') where user='root'; ```
没有评论