Mysql创建、账户操作

1、Mysql 查看当前用户名

--方法一
select user();

+----------------+
| user()         |
+----------------+
| xxxxxxxxxxxxxx |
+----------------+
1 row in set (0.00 sec)
--方法二
status;

--------------
mysql  Ver 14.14 Distrib 5.6.34, for Linux (x86_64) using  EditLine wrapper

Connection id:      6
Current database:
Current user:       xxxxxxxxxxxxxxxxxxxxxx
SSL:            xxxxxxxxxxxxxxxxxxxxxx
Current pager:      xxxxxxxxxxxxxxxxxxxxxx
Using outfile:      ''
Using delimiter:    ;
Server version:     5.6.34 Source distribution
Protocol version:   10
Connection:     xxxxxxxxxxxxxxxxxxxxxx
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:        xxxxxxxxxxxxxxxxxxxxxx
Uptime:         45 min 10 sec

Threads: 1  Questions: 27  Slow queries: 0  Opens: 67  Flush tables: 1  Open tables: 60  Queries per second avg: 0.009
--------------

2、创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password'; 

3、用户授权

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION

--GRANT ALL ON *.* TO 'pig'@'%';  --把所有库的所有权限授予pig账户
--GRANT select ON test.* TO 'pig'@'%';  --test库的select权限授予pig账户
--privileges - 用户的操作权限,SELECT , INSERT , UPDATE .
--如果要授予所的权限则使用ALL.;databasename - 数据库名,tablename-表名,
--如果要授予该用户对所有数据库和表的相应操作权限则可用*表示, *.*. 

4、撤销用户权限

REVOKE privilege ON databasename.tablename FROM 'username'@'host'; 
--REVOKE SELECT ON *.* FROM 'pig'@'%';
--假如你在给用户'pig'@'%'授权的时候是这样的(或类似的):GRANT SELECT ON test.user TO 'pig'@'%', 
--则在使用REVOKE SELECT ON *.* FROM 'pig'@'%';命令并不能撤销该用户对test数据库中user表的SELECT 操作.
--相反,如果授权使用的是GRANT SELECT ON *.* TO 'pig'@'%';
--REVOKE SELECT ON test.user FROM 'pig'@'%';命令也不能撤销该用户对test数据库中user表的Select 权限.

4、删除用户

use mysql;
delete from user where user='user' and host='localhost';
flush privileges;

5、导入本地文件进Mysql

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';

参考资料

1、问问:怎样查看MySql的当前用户名为什么啊?

2、MySQL创建用户与授权方法

3、Mysql创建、删除用户

2017-07-28 21:15 30 数据库
Comments
Write a Comment