mysql用户管理及权限

用户名结构：username@host

用户名：16个字符以内

host：ip  hostname  network

host：www.magedu.com

Ip:172.16.10.177

网络地址:172.16.0.0/255.255.0.0

 --skip-name-resolve

支持通配符：172.16.%.%

%.magedu.com

通配符：

_:匹配任意单个字符 172.16.0._  

%:匹配任意字符

创建用户：

1、 create user username@host [identified by ‘password’]

create user ‘username’@’host’identified by ‘password’;

drop user ‘username’@’host’;

2、 grant

grant all privileges on db.* to username@’%’;

grant 权限 on *.*|db.*|db.table|db.procedure to username@’host’;

flush privileges;

3、 insert into mysql.user

删除用户：

DROP USER ‘username@host’

RENAME USER old_name TO new_name

Mysql用户密码修改

1. # mysqladmin –u USERNAME –h HOSTNAME password ‘NEW_PASS’-p
2. mysql> set password for ‘USERNAME’@’HOSTNAME’=PASSWORD(‘new_pass’);
3. mysql>update mysql.user set PASSWORD=PASSWORD(‘new_pass’) WHERE mysql.user = ‘root’;

mysql> FLUSH PRIVILEGES;

编辑一个家目录下的my.cnf 免去每次输入密码。

 

忘记管理员密码如何处理：

Step1：停掉mysqld服务 service mysqld stop

Step2：修改mysql服务器启动脚本（/etc/init.d/mysqld）,添加红色部分

   

 

Step3：启动mysqld 服务 service mysqld start

Step4：进入mysql，修改密码

   Update user set Password=PASSWORD(‘123456’) WHERE User=’root’;

Step5: 停掉mysqld服务并恢复mysqld脚本，去掉刚刚加的语句

Step6：启动mysqld服务

授权：

grant pri1,pri2,….on db_name.tb_name to ‘username’@’host’[identified by ‘password’];

revoke pri1,pri2,….on db_name.tb_name from username’@’host’;

 

grant all privileges on db.* to username@’%’;

grant 权限 on *.*|db.*|db.table|db.procedure to username@’host’;

flush privileges;

 

 

查看用户权限

Show grants for ‘username’@’host’;

show grants for ‘jerry’@’%’;