1.新建普通用户


--create语句

mysql> create user 'test1'@'localhost' identified by 'test1';


--使用insert插入

mysql> insert into mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) values('localhost','test2',password('test2'),'','','');

mysql> flush privileges;


--使用grant语句

mysql> grant select on *.* to 'test3'@'localhost' identified by 'test3';

mysql> flush privileges;


2.删除普通用户


--drop删除

mysql> drop user 'test1'@'localhost';


--使用delete从表中删除

mysql> delete from mysql.user where host='localhost' and user='test2';

mysql> flush privileges;


3.root修改密码


----root修改root密码

--mysqladmin修改

[root@test ~]# mysqladmin -uroot -p password

Enter password: 

New password: 

Confirm new password:


--登陆后使用update修改

mysql> update mysql.user set password=password('root') where host='localhost' and user='root';

mysql> flush privileges;


--登陆后使用set修改

mysql> set password=password('root')

mysql> flush privileges;


----root修改普通用户密码

--set语句

mysql> set password for 'test3'@'localhost'=password('test3');


--update更新mysql.user表

mysql> update mysql.user set password=password('test3') where host='localhost' and user='test3';

mysql> flush privileges;


--grant语句

mysql> grant select on *.* to 'test3'@'localhost' identified by 'test3';

grant修改密码后,之前对用户使用旧密码的授权还在


4.普通用户自己修改密码


mysql> set password=password('test4');

普通用户一般不能使用mysqladmin


5.root密码丢失


[root@test ~]# vim /etc/my.cnf

[mysqld]

skip-grant-tables


[root@test ~]# /etc/init.d/mysqld restart

[root@test ~]# mysql

mysql> 

mysql> update mysql.user set password=password('root') where host='localhost' and user='root';

mysql> flush privileges;


注:使用mysql-5.5.30