前言
当前mysql版本为5.7,执行正文下面的语句报错:
You have an error in your SQL syntax; check the manual that corresponds to yourMySQL server version for the right syntax to use near 'IDENTIFIED BY 'xxxxxx' WITH GRANT OPTION' at line 1
去掉identified by '密码'。
这类问题,注意mysql相应版本支持的语法。
新建用户
格式:
create user '用户名'@'localhost' identified by '密码';
eg:
1.mysql->create user 'test'@'localhost' identified by '123';2.mysql->create user 'test'@'192.168.7.22' identified by '123';3.mysql->create user 'test'@'%' identified by '123';
host="localhost"为本地登录用户,
host="ip"为ip地址登录,
host="%",为外网ip登录
删除用户
格式:
drop user '用户名'@'host';
host为创建用户时的host
eg:
drop user 'wj'@'localhost';
授权
格式:
grant privileges on databasename.tablename to 'username'@'host' IDENTIFIED BY 'PASSWORD';
eg:
/*授予用户通过外网IP对于该数据库的全部权限*/grant all privileges on `test`.* to 'test'@'%' ;/*授予用户在本地服务器对该数据库的全部权限*/grant all privileges on `test`.* to 'test'@'localhost'; grant select on test.* to 'user1'@'localhost'; /*给予查询权限*/grant insert on test.* to 'user1'@'localhost'; /*添加插入权限*/grant delete on test.* to 'user1'@'localhost'; /*添加删除权限*/grant update on test.* to 'user1'@'localhost'; /*添加权限*/flush privileges; /*刷新权限*/
但是,配置好后,还是访问不了。又查了资料,全都说就是这样设置的。
最后发现,防火墙开启的,关闭后,就可以访问啦!!