DBA-mysql-用户控制

创建：

 CREATE USER 'jeffrey'@'localhost'  IDENTIFIED BY 'new_password' PASSWORD EXPIRE;

授权：

Grant all on *.* to 'jeffrey'@'localhost';

创建并授权

Grant all privileges on *.* to root1@'%' identified by "root" with grant option;

创建用户:
CREATE USER 'jacky'@'localhost' identified BY 'mypass';
GRANT SELECT ,UPDATE ON *.* TO 'testUser'@'localhost' identified BY 'testpwd';
INSERT INTO mysql.user(host,user,password) VALUES ('localhost','customer1',password('customer1'))
*identified with只能在MYSQL5.5.7及以上版本使用,identified with和identified by是互斥的
*CREATE USER语句的操作会被记录到服务器日志文件或者操作历史文件中 cat ~/.mysql_history
*查出哈希值 SELECT password('mypass'); 再使用 CREATE user 'tom'@'localhost' identified BY password 'B292FED686394CC81F802198C941D43EF0E4FB62'; 再赋值

赋予权限
grant all privileges on *.* to root@'(localhost或192.168.1.1或%)' identified by "password" with grant option;
flush privileges;
show grants;

回收权限
revoke delete on *.* from 'root'@'localhost';
REVOKE INSERT ON *.* FROM 'grantUser'@'localhost';

INSERT,  SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER

 

 

 

查看用户
select host,user,password from user; 重命名:rename user 'jack'@'%' to 'jim'@'%';

删除用户
drop user 'root'@'localhost'; 或 DELETE FROM mysql.user WHERE `Host`='localhost' and `User`='testUser'

修改密码
SET PASSWORD=PASSWORD("123456")
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
mysqladmin -uroot -p123456 password 1234abcd
update user set PASSWORD = PASSWORD('1234abcd') where user = 'root';
grant USAGE ON *testUser*TO 'localhost' identified BY '123456';

找回root密码
mysqld_safe --skip-grant-tables user=mysql
/etc/init.d/mysql start-mysqld --skip-grant-tables

基础查看操作
show databases; show tables; show columns from 表 ; desc 表; show create table 表

删除整个表数据
truncate table 表名 或delete from 表名
*效率上truncate比delete快，但truncate删除后不记录mysql日志，不可以恢复数据。

删除某条表数据
delete from ofUser where username='admin';
插入某条表数据
insert into ofUser (username,plainPassword,creationDate,modificationDate) values('admin','admin','1464292787204','1464292787204');
================================================================

命令行下具体用法如下： 

mysqldump -u用戶名 -p密码 -d 数据库名 表名 > 脚本名;
导出整个数据库结构和数据
mysqldump -h localhost -uroot -p123456 database > dump.sql
导出单个数据表结构和数据
mysqldump -h localhost -uroot -p123456 database table > dump.sql
导出整个数据库结构（不包含数据）
mysqldump -h localhost -uroot -p123456 -d database > dump.sql
导出单个数据表结构（不包含数据）
mysqldump -h localhost -uroot -p123456 -d database table > dump.sql