1.查看MySQL所有用户
select * from mysql.user\G
2.创建用户
#第一种方法 格式：create user '用户名'@'连接地址' identified by '密码'; #第二种方法 格式：grant 权限 on 库.表 to '用户名'@'连接地址' identified by '密码'; 连接地址： localhost：代表本地 192.168.1.%：代表一网段 %：代表任意地址
3.修改用户密码
#第一种方法 格式：create user '用户名'@'连接地址' identified by '密码'; #第二种方法 格式：grant 权限 on 库.表 to '用户名'@'连接地址' identified by '密码'; 连接地址： localhost：代表本地 192.168.1.%：代表一网段 %：代表任意地址
4.删除用户
#第一种方法 drop from user '用户名'@'地址' #第二种方法 delete from mysql.user where host='地址' and user='用户名'; #刷新权限 flush privileges;
5.用户root忘记密码
1.进入mysql配置文件
[root@localhost ~]# vi /etc/my.cnf #最后一行添加 skip-grant-tables
2.重启服务
[root@localhost ~]# systemctl restart mysqld
3.进入MySQL修改密码
[root@localhost ~]# mysql -uroot
6.撤销权限
revoke 权限 on *.* from '用户名'@'连接地址'
7.查看当前用户权限
mysql> show grants for '用户名'@'连接地址';
8.MySQL权限
格式：grant 权限 on 库.表 to '用户名'@'连接地址' identified by '密码'; 权限： 使用：show privileges查看所用权限 mysql> show privileges; +-------------------------+---------------------------------------+-------------------------------------------------------+ | Privilege | Context | Comment | +-------------------------+---------------------------------------+-------------------------------------------------------+ | Alter | Tables | To alter the table | | Alter routine | Functions,Procedures | To alter or drop stored functions/procedures | | Create | Databases,Tables,Indexes | To create new databases and tables | | Create routine | Databases | To use CREATE FUNCTION/PROCEDURE | | Create temporary tables | Databases | To use CREATE TEMPORARY TABLE | | Create view | Tables | To create new views | | Create user | Server Admin | To create new users | | Delete | Tables | To delete existing rows | | Drop | Databases,Tables | To drop databases, tables, and views | | Event | Server Admin | To create, alter, drop and execute events | | Execute | Functions,Procedures | To execute stored routines | | File | File access on server | To read and write files on the server | | Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess | | Index | Tables | To create or drop indexes | | Insert | Tables | To insert data into tables | | Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) | | Process | Server Admin | To view the plain text of currently executing queries | | Proxy | Server Admin | To make proxy user possible | | References | Databases,Tables | To have references on tables | | Reload | Server Admin | To reload or refresh tables, logs and privileges | | Replication client | Server Admin | To ask where the slave or master servers are | | Replication slave | Server Admin | To read binary log events from the master | | Select | Tables | To retrieve rows from table | | Show databases | Server Admin | To see all databases with SHOW DATABASES | | Show view | Tables | To see views with SHOW CREATE VIEW | | Shutdown | Server Admin | To shut down the server | | Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. | | Trigger | Tables | To use triggers | | Create tablespace | Server Admin | To create/alter/drop tablespaces | | Update | Tables | To update existing rows | | Usage | Server Admin | No privileges - allow connect only | +-------------------------+---------------------------------------+-------------------------------------------------------+ 31 rows in set (0.00 sec)
