Mysql 用户管理

#建库、建表、添加数据
CREATE DATABASE chapter08;
USE chapter08;
CREATE TABLE student(
id int primary key auto_increment,
name varchar(20),
age int
);
INSERT INTO student(name,age) VALUES ('Tom',20);
INSERT INTO student(name,age) VALUES ('Jack',16);
INSERT INTO student(name,age) VALUES ('Lucy',18);
#查询结果
SELECT * FROM student;

【例8-1】
mysqldump -uroot -pitcast chapter08>C:/backup/chapter08_20140305.sql

-----------------------------------------------------------------------------------------
8.1.2 数据的还原

（1） 删除数据库
DROP DATABASE chapter08;

（2） 创建数据库
CREATE DATABASE chapter08;

（3） 还原数据
mysql -uroot -pitcast chapter08 <C:/backup/chapter08_20140305.sql

-----------------------------------------------------------------------------------------
8.2.2 创建普通用户
#GRANT创建用户
GRANT SELECT ON chapter08.student TO 'user1'@'localhost' IDENTIFIED BY '123';
#查看用户
SELECT host,user,password FROM user;

#CREATE USER语句创建用户
CREATE USER 'user2'@'localhost' IDENTIFIED BY '123';
#查看用户
SELECT host,user,password FROM user;

#INSERT语句创建用户
INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject)
VALUES('localhost','user3',PASSWORD('123'),'','','');

#查看用户
SELECT host,user,password FROM user;

-----------------------------------------------------------------------------------------
8.2.3 删除普通用户
#使用DROP USER语句删除用户
DROP USER 'user1'@'localhost';

#DELETE语句删除用户
DELETE FROM mysql.user WHERE Host='localhost' AND User='user2';

-----------------------------------------------------------------------------------------
8.2.4 修改用户密码

#使用mysqladmin命令修改root用户密码
mysqladmin -u root -p password mypwd1

#使用UPDATE语句修改root用户密码
UPDATE mysql.user SET Password=PASSWORD('mypwd2') WHERE User='root' and Host='localhost';

#使用SET语句修改root用户的密码
SET PASSWORD=password('mypwd3');

#使用GRANT语句修改普通用户密码
GRANT USAFE ON *.* TO 'username'@'localhost' IDENTIFIED BY [PASSWORD]'new_password';

#使用UPDATE语句修改普通用户的密码
UPDATE mysql.user set Password=PASSWORD('new_password')
WHERE User='username' and Host='hostname';

#使用SET语句修改普通用户的密码
SET PASSWORD FOR'username'@'hostname'=PASSWORD('new_password');

#普通用户修改密码
SET PASSWORD=PASSWORD('new_password');
-----------------------------------------------------------------------------------------
8.3.2 授予权限
GRANT INSERT,SELECT ON *.* TO 'user4'@'localhost' IDENTIFIED BY '123'
WITH GRANT OPTION;
#查看
use mysql;
SELECT Host,User,Password,Insert_priv,Select_priv,Grant_priv FROM mysql.user
WHERE user='user4'\G

#使用SHOW GRANTS语句查询root用户的权限
SHOW GRANTS FOR 'root'@'localhost';

#使用REVOKE语句收回user4用户的INSERT权限

REVOKE INSERT ON *.* FROM 'user4'@'localhost';