1.mysql数据库表结构
1)mysql数据库的所有表:
![](http://images0.cnblogs.com/blog/581861/201412/122202345872398.png)
2)user表的结构
![](http://images0.cnblogs.com/blog/581861/201412/122202356039042.png)
3)db表结构
![](http://images0.cnblogs.com/blog/581861/201412/122202361037128.png)
4)权限表(tables_priv和columns_priv表)
①tables_priv表结构:
![](http://images0.cnblogs.com/blog/581861/201412/122202364626470.png)
②columns_priv表结构:
![](http://images0.cnblogs.com/blog/581861/201412/122202367282486.png)
6)procs_priv表结构
![](http://images0.cnblogs.com/blog/581861/201412/122202369316313.png)
2.账户管理
1)使用命令行命令登入mysql
![](http://images0.cnblogs.com/blog/581861/201412/122202372901357.png)
2)新增用户
①使用grant user语句新增用户
![](http://images0.cnblogs.com/blog/581861/201412/122202375096656.png)
②使用insert语句添加用户
![](http://images0.cnblogs.com/blog/581861/201412/122202377287658.png)
③使用GRANT语句新建普通用户
![](http://images0.cnblogs.com/blog/581861/201412/122202379782202.png)
3)删除用户
①使用drop user语句删除用户
![](http://images0.cnblogs.com/blog/581861/201412/122202382906529.png)
②使用delete语句删除用户
![](http://images0.cnblogs.com/blog/581861/201412/122202389151887.png)
4)root修改自己的密码
①使用mysqladmin工具更改密码(注:密码一定要用双引号括起来)
![](http://images0.cnblogs.com/blog/581861/201412/122202391812201.png)
②使用update语句更改user表
![](http://images0.cnblogs.com/blog/581861/201412/122202396961760.png)
③使用set命令更改密码
![](http://images0.cnblogs.com/blog/581861/201412/122202399313830.png)
5)root用户修改普通用户的密码
①用set命令修改密码
![](http://images0.cnblogs.com/blog/581861/201412/122202401651602.png)
②使用update语句更改user表
![](http://images0.cnblogs.com/blog/581861/201412/122202403846901.png)
![](http://images0.cnblogs.com/blog/581861/201412/122202406658688.png)
③使用grant语句更改密码
![](http://images0.cnblogs.com/blog/581861/201412/122202409006460.png)
6)普通用户修改自己的密码
①使用set语句修改
![](http://images0.cnblogs.com/blog/581861/201412/122202411813545.png)
②使用mysqladmin工具修改密码
![](http://images0.cnblogs.com/blog/581861/201412/122202414151318.png)
7)root用户密码丢失的解决方法
①使用--skip-grant-tables选项启动MySQL服务
windows:
mysqld --skip-grant-tables
mysqld-nt --skip-grant-tables
net start mysql --skip-grant-tables
linux:
mysqld_safe --skip-grant-tables user=mysql
/etc/init.d/mysql start --mysqld --skip-grant-tables
②用update语句修改密码,加载权限表
例子:
![](http://images0.cnblogs.com/blog/581861/201412/122202416346617.png)
![](http://images0.cnblogs.com/blog/581861/201412/122202419158403.png)
![](http://images0.cnblogs.com/blog/581861/201412/122202421506175.png)
![](http://images0.cnblogs.com/blog/581861/201412/122202425256990.png)
![](http://images0.cnblogs.com/blog/581861/201412/122202429007804.png)
3、权限管理
1)user表的权限列
![](http://images0.cnblogs.com/blog/581861/201412/122202441811062.jpg)
2)授权
语法:
GRANT priv_type [(column_list)] ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password'] [,user [IDDENTIFIED BY [PASSWORD] 'password']]...[WITH with_option [with_option]...]
with_option参数:
a、GRANT OPTION:被授权的用户可以将这些权限赋予给别的用户;
b、MAX_QUERIES_PER_HOUR_count:设置每个小时可以执行count次查询;
c、MAX_UPDATES_PER_HOUR_count:设置每个小时可以执行count次更新;
d、MAX_CONNECTIONS_PER_HOUR_count:设置每个小时可以建立count次连接;
e、MAX_USER_CONNECTIONS_PER_count:设置单个用户可以同时具有的count连接数;
例子:
![](http://images0.cnblogs.com/blog/581861/201412/122202451816233.png)
![](http://images0.cnblogs.com/blog/581861/201412/122202454319776.png)
3)收回权限
收回指定权限的REVOKE语句的基本语法是:
REVOKE priv_type [(column_list)]... ON database.table FROM user [,user]...
收回全部权限的REVOKE语句的基本语法是:
REVOKE ALL PRIVILEGES,GRANT OPTION FROM user [,user]...
例子:
![](http://images0.cnblogs.com/blog/581861/201412/122202457904820.png)
4)查看用户权限
①查看user表
②使用show grant语句
基本语法:SHOW GRANTS FOR 'username'@'hostname'
例子:
![](http://images0.cnblogs.com/blog/581861/201412/122202462902907.png)
![](http://images0.cnblogs.com/blog/581861/201412/122202466032936.png)