1.通过mysql数据库的user表查看用户相关信息
mysql> use mysql;
mysql> select host,user from user;
查看用户相关信息
2.创建数据库
mysql> create database [databasename] default character set utf8 collate utf8_general_ci;
3.创建新用户
mysql> create user 'userName'@'%' identified by 'passwr';
语法：
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
例子:
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';
user表中host列的值的意义
% 匹配所有主机
localhost localhost不会被解析成IP地址，直接通过UNIXsocket连接
127.0.0.1 会通过TCP/IP协议连接，并且只能在本机访问；
::1 ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1
4.对userName用户给予针对指定数据库进行增删改查的权限
mysql> grant select,insert,update,delete,create on
[databasename].* to 'userName';
flush privileges;
注意：修改完权限以后 一定要刷新服务，或者重启服务，刷新服务用：FLUSH PRIVILEGES
5.通过show grants命令查看权限，若想要在原来的基础上增加权限则继续执行grant
mysql> grant drop on [databasename].* to 'userName';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'userName';
+----------------------------------------------------------------------------------------------------+
| Grants for 'userName'@% |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON . TO 'userName'@'%' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON [databasename].* TO 'userName'@'%' |
+----------------------------------------------------------------------------------------------------+
6.通过revoke命令移除用户的相关权限
mysql> revoke drop on [databasename].* from 'userName';
mysql> show grants for 'userName';
+----------------------------------------------------------------------------------------------------+
| Grants for 'userName'@% |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON . TO 'userName'@'%' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON [databasename].* TO 'userName'@'%' |
+----------------------------------------------------------------------------------------------------+
7.修改用户密码
mysql>update mysql.user set password=password('新密码') where User="demo" and Host="localhost";
mysql>flush privileges;
8.删除用户
mysql>Delete FROM user Where User='demo' and Host='localhost';
mysql>flush privileges;
mysql>drop database demoDB; //删除用户的数据库
删除账户及权限：>drop user 用户名@'%';
>drop user 用户名@ localhost;
参考
