1、在新用户中创建数据库
a、新建用户
进入root账户:>mysql -uroot -p;
创建新用户方法有二
第一种:
mysql>grant all privileges on *.* to 'username'@'%' identified by '1234;
mysql>grant all privileges on *.* to 'username'@'localhost' identified by '1234;
第一种后者此种亲测有效
第二种:
mysql>insert into mysql.user(Host, User, Password) values('localhost', 'username',password('1234'));
刷新系统权限表
mysql>flush privileges;
mysql>exit;
2、创建数据库为用户授权
登录root>mysql -uroot -p;
mysql>create database dbname;
mysql>grant all on dbname.* to 'yp'@'%' identified by '1234';
mysql>flush privileges;
创建数据库并编码:
mysql>CREATE DATABASE dbname DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
3、说明localhost允许本机访问,%允许其他网络访问
4、查看用户及权限
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
5、查看指定用户权限 SHOW GRANTS FOR user;
5、回收用户全部权限
mysql>REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'localhost';