创建用户3种常用方式
CREATE USER 'cactiuser'@'localhost' IDENTIFIED BY '111111'; //该用户权限不大
insert into mysql.user(user,password) values('test','111111'); //压根没权限登入进去
grant all on *.* to cacit@localhost identified by 'password';
更改密码2种方式
mysqladmin -u用户名 -p旧的密码 password 新密码
mysql> UPDATE mysql.user SET password=PASSWORD('111111') WHERE user='root'; //可以全部小写
更改用户名:
mysql> update mysql.user set user="新用户名" where user="root"; 将用户名为root的改为新用户名
=====================================================================================
授权
GRANT ALL ON . TO 'cactiuser'@'%';
GRANT ALL PRIVILEGES ON . TO 'newuser'@'localhost';
GRANT ALL PRIVILEGES ON . TO 'cactiuser'@'%' IDENTIFIED BY '111111';
grant all on . to 'cactiuser'@'localhost' identified by '111111';
grant all privileges on . to 'yangxin'@'%' identified by 'yangxin123456' with grant option;
grant select,create,drop,update,alter on . to 'yangxin'@'localhost' identified by 'yangxin0917' with grant option;
FLUSH PRIVILEGES;
解释说明
ALL PRIVILEGES:表示开放所有权限,如增删查改,可以直接写ALL
select,create,drop,update,alter:表示增删查改等权限
ON database.table:表示开放哪个数据库的哪个表,.为所有数据库,所有表
to ”用户名”@”登录IP或域名”: %表示没有限制,ge:”ya”@”192.168.0.%”,表示ya用户只能在192.168.0IP段登录
identified by:指定用户的登录密码
with grant option:表示允许用户将自己的权限授权给其它用户
查看权限
show grants for 'yangxin'@'localhost';
回收权限
删除yangxin这个用户的create权限,该用户将不能创建数据库和表。
revoke create on . from 'yangxin@localhost';
FLUSH PRIVILEGES;
=======================================================
mysql忘记root密码配置
一.在mysql中配置vi /etc/my.cnf
[mysqld]
skip-grant-tables
二.进入mysql,并设置密码为123456
mysql -uroot
mysql>use mysql;
mysql>update user set password=PASSWORD('123456') where user='root';update mysql.user set authentication_string=password('123456') where user='root'; #修改密码成功
mysql>flush privileges;
三.重新改回配置