MySQL创建用户及授权

# 创建测试数据库,使用UTF8字符集

CREATE DATABASE `testdb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;


1. 以管理员身份登录mysql

$ mysql -u root -p

Enter password:


2. 选择mysql数据库

> use mysql


3. 创建用户并设定密码

#备注# 若需要远程登录则将localhost替换为百分号(%)或固定的IP地址

> CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';


4. 授权

# *.* 授权用户所有对象所有权限

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; 

#为用户赋予操作数据库testdb的所有权限

GRANT ALL PRIVILEGES ON testdb.* TO 'test'@'localhost' IDENTIFIED BY '123456' WITH GRANT OPTION;

查看已有权限

show grants for 'username'@'host';

select * from mysql.user where user='cactiuser' \G

取消权限

语法:Revoke privileges (columns) on what from user ;

revoke all on *.* from username@host


6. 使操作生效

flush privileges;


7、使用exit退出后就可以用新用户登录了

mysql -u test -p