SQL精进1_适用于MySQL管理

/ Database / 没有评论 / 955浏览

============================基础=======================================

基础

查看数据库

 show databases;

简单建立数据库

 create database lijinghua;     #这样直接建立所采用的字符集和编译安装指定的字符集相同

指定字符集建库

CREATE DATABASE oldgirl CHARACTER SET gbk COLLATE gbk_chinese_ci;  ### COLLATE为校队规则

查看建库语句

 show create database oldgirl\G

修改字符集

 ALTER DATABASE oldgirl CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

删库

 drop database lijinghua;

切库

 use lijinghua;

查看当前所在的库

 select database();

查看库里的表

 show tables;

查看用户

 select user,host from mysql.user;

查看表的字段

 desc lijinghua;

进入mysql中使用linux命令?

 mysql> system pwd
/root

创建用户

============================创建用户====================================

限制网段创建用户及授权所有权限

CREATE USER 'rootziyoudao'@'172.18.252.%' IDENTIFIED BY '123456';
grant all privileges on *.* to 'rootziyoudao' identified by '123456';
flush privileges;

不限制网段创建用户及授权database1查询权限

CREATE USER 'rootziyoudao'@'%' IDENTIFIED BY '123456';
grant select on database1.* to 'rootziyoudao' identified by '123456';

flush privileges;

运维人员常用

grant all on *.* to 'rootziyoudao'@'localhost' identified by '123456';
flush privileges;

注意

其实用户可主机域都可以不带单引号的,但是密码必须加单引号,另外的话,创建用户不需要flush privileges。

删除用户

============================删除用户==========================

drop删除

drop user 用户名@'%';

delete删除

如果drop删除不了用户,很可是是用户或主机部分是特殊字符或大写内容等,此时可以用下面方式删除,以bbs用户,172.16.1.%主机为例,具体处理命令及操作过程为:

delete from mysql.user where user='bbs' and host='172.16.1.%'; 
flush privileges;

更改密码

=============================更改密码======================================

跳表(重启)

 skip-grant-tables

MySQL5.7更改密码

use mysql;
update user set authentication_string=PASSWORD("这里输入你要改的密码") where User='root'; #更改密码
update user set plugin="mysql_native_password"; #如果没这一行可能也会报一个错误,因此需要运行这一行
flush privileges; #更新所有操作权限

use mysql;
update user set authentication_string=PASSWORD("123456") where User='root'; 
update user set plugin="mysql_native_password"; 
flush privileges; 

#登录进去 还需要重新set下密码
SET PASSWORD = PASSWORD('123456');
flush privileges; 

MySQL5.6及以下

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

update mysql.user set password=password('123456') where user='root' and host='%';
flush privileges;


GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "123456" WITH GRANT OPTION; 
flush privileges;	

查看

=================================查看=========================

MySQL的变量分为以下两种:

1)系统变量:配置MySQL服务器的运行环境,可以用show variables查看

2)状态变量:监控MySQL服务器的运行状态,可以用show status查看

查看所有存在的用户及授权的地址范围

 SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
select user,host from mysql.user;

查看用户拥有什么权限(注意区段等)

show grants for dbuser@'%';
show grants for dbuser@'localhost';

注意哦,不同的区段可以拥有不同的密码及所授予的权限

查看binlog

只查看第一个binlog文件的内容

show binlog events;

查看指定binlog文件的内容

show binlog events in 'mysql-bin.000002';

查看当前正在写入的binlog文件

show master status\G

获取binlog文件列表

show binary logs; 

授权

============================授权=====================================

远程授权用户所有权限

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "swl19960706" WITH GRANT OPTION; 
flush privileges;	

远程授权用户查询权限

grant select on *.* to @'%' identified by "123456" WITH GRANT OPTION ;
flush privileges;	

限制网段授权用户权限

grant select,create,insert,update,delete,alter,drop,lock tables  on dbname.* to deployop@'192.168.1.%' identified by 'password'; 
flush privileges;

花样授权操作

给个别的库权限

grant select,create,insert,update,delete,alter,drop,lock tables  on dbname.* to deployop@localhost identified by 'memect'; 
flush privileges;

给连接外网权限

grant select,create,insert,update,delete,alter,drop,lock tables  on dbname.* to deployop@'%' identified by 'memect'; 
flush privileges;

给所有的库权限

·

grant select,create,insert,update,delete,alter,drop,lock tables  on  *.* to deployop@'localhost' identified by 'memect'; 
flush privileges;

取消权限

======================取消授权============================================

收回权限(当然要先知道他有什么权限)

 REVOKE INSERT ON *.* FROM rootziyoudao@localhost;
 flush privileges;

注意

我们可以在一些场景下取消删除的权限

 revoke create,drop on *.* from 'rootziyoudao'@'172.16.1.%';
 

End

修改于2018.07.26

for name in mysql -e "show databases;"|sed 1d do mysqldump -uroot -p'password' -B $name done