1:查看当前的用户
select Host,User,Password from mysql.user;
查询结果:
+-------------------------+------+-------------------------------------------+
| Host | User | Password |
+-------------------------+------+-------------------------------------------+
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| iz2zedroc0yv8zryqdiqxlz | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| iz2zedroc0yv8zryqdiqxlz | | |
+-------------------------+------+-------------------------------------------+
6 rows in set (0.00 sec)
2:创建一个用户
create user jacob identified by '123456';
结果:
Query OK, 0 rows affected (0.00 sec)
表示sql语句运行ok,这里0行生效,不用管
再次查询用户表:
mysql> select Host,User,Password from mysql.user;
+-------------------------+-------+-------------------------------------------+
| Host | User | Password |
+-------------------------+-------+-------------------------------------------+
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| iz2zedroc0yv8zryqdiqxlz | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| iz2zedroc0yv8zryqdiqxlz | | |
| % | jacob | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------+-------+-------------------------------------------+
7 rows in set (0.01 sec)
3:配置权限
创建完成了,在程序里面连接发现还是没有权限,刚才我们只是创建了用户,还没有对这个用户分配权限
grant all privileges on *.* to 'jacob'@'%'identified by '123456' with grant option;
注释:
all代表接受所有操作,比如 select,insert,delete....; *.* 代表所有库下面的所有表;% 代表这个用户允许从任何地方登录;为了安全期间,这个%可以替换为你允许的ip地址;
4:刷新mysql用户权限相关表
flush privileges;
5:如果远程连接还是不行那就执行下面的
我以为就此收工了,等等,程序怎么还是连不上去,还是 access deny ;
难道端口不是3306吗,打开 mysql配置文件,是默认的3306,接着往下面看,发现一个关键地方;
bind-address = 127.0.0.1
原来这里mysql默认绑定了本地ip,不接受其他来源;注释掉,重启mysql 一切OK;
6:修改用户密码
update mysql.user set password=password('新密码') where User="test" and Host="%";
7:删除用户
delete from mysql.user where User='linjianqing' and Host='%';
如果创建用户时,报:
ERROR 1396 (HY000): Operation CREATE USER failed for 'linjianqing'@'%'
则把语句改成
create user 'linjianqing'@'%' identified by '123456';
创建数据库
create database mmall default character set utf8 COLLATE utf8_general_ci;
显示数据库
show databases;
删除数据库
drop database mmall;
给这个数据库赋予用户的权限
grant all privileges on mmall.* to 'jacob'@'%' identified by '123456';
将sql文件导入数据库
在导入之前一定要进入数据库
use mmall;
显示表
show tables;
导入
source /developer/mmall.sql;
验证,查看表
select * from mmall_product\G;
\G是格式化的意思