文档章节

mysql 中的信息数据库以及 shell操作sql

blackfoxya
 blackfoxya
发布于 06/19 11:47
字数 2909
阅读 4
收藏 0
点赞 0
评论 0

Information_schema 是 MySQL 自带的信息数据库,里面的“表”保存着服务器当前的实时信息。它提供了访问数据库元数据的方式。

什么是元数据呢?元数据是关于数据的数据,如数据库名或表名,列的数据类型,或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。

在 MySQL 中,把 information_schema 看作是一个数据库,确切说是信息数据库。其中保存着关于MySQL 服务器所维护的所有其他数据库的信息。如数据库名,数据库的表,表栏的数据类型与访问权 限等。在 INFORMATION_SCHEMA 中,有数个只读表。它们实际上是视图,而不是基本表,因此,你将无法看到与之相关的任何文件。

一、information_schema 数据库表说明

SCHEMATA

提供了当前mysql实例中所有数据库的信息。

是show databases的结果取之此表。

TABLES

提供了关于数据库中的表的信息(包括视图)。

详细表述了某个表属于哪个schema,表类型,表引擎,创建时间等信息。

是show tables from schemaname的结果取之此表。

COLUMNS

提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。

是show columns from schemaname.tablename的结果取之此表。

STATISTICS 提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。
USER_PRIVILEGES(用户权限)表 给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。
SCHEMA_PRIVILEGES(方案权限)表 给出了关于方案(数据库)权限的信息。该信息来自mysql.db授权表。是非标准表。
TABLE_PRIVILEGES(表权限)表 给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。
COLUMN_PRIVILEGES(列权限)表 给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。
CHARACTER_SETS(字符集)表 提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。
COLLATIONS 提供了关于各字符集的对照信息。
COLLATION_CHARACTER_SET_APPLICABILITY 指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。
TABLE_CONSTRAINTS 描述了存在约束的表。以及表的约束类型。
KEY_COLUMN_USAGE 描述了具有约束的键列。
ROUTINES

提供了关于存储子程序(存储程序和函数)的信息。

此时,ROUTINES表不包含自定义函数(UDF)。名为“mysql.proc name”的列

指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。

VIEWS 给出了关于数据库中的视图的信息。需要有show views权限,否则无法查看视图信息。
TRIGGERS 提供了关于触发程序的信息。必须有super权限才能查看该表

1.1 user_privilege

1)IS_GRANTABLE字段

用 root 账号执行如下语句

a) 
grant all privileges on *.* to `myuser`@localhost with grant option;

b) 
select user,select_priv from mysql.user where user='myuser';
+------------------------------+----------------------------+
| user                         | select_priv                |
+------------------------------+----------------------------+
| myuser                       | Y                          |
+------------------------------+----------------------------+

c) 
select PRIVILEGE_TYPE, IS_GRANTABLE from information_schema.user_privileges where grantee like '\'myuser\'@\'localhost\'' and PRIVILEGE_TYPE = ‘SELECT’;
+------------------------------+----------------------------+
| PRIVILEGE_TYPE               | IS_GRANTABLE               |
+------------------------------+----------------------------+
| SELECT                       | NO                         |
+------------------------------+----------------------------+

(本文中 grant 后都接 flush privileges , 不赘述)

说明 :  从 b) 看 myuser 已经有了 select_priv。而 c) 中 IS_GRANTABLE 是 NO,这不是显示错误。 实际上,IS_GRANTABLE 并非表示用户是否“拥有此权限”,而是表示用户是否拥有“将此权限赋予其他用户”的权限。它对应的是 mysql.user 表中的 grant_priv 字段,此时为 NO。

2)显示规则

当我们创建一个新用户 create user myuser2 ;  时,在 mysql.user 中看到这个用户的所有权限都为NO,此时 user_privileges 增加一行

+---------------------+-----------------------+--------------------------+---------------------+
| GRANTEE             | TABLE_CATALOG         | PRIVILEGE_TYPE           | IS_GRANTABLE        |
| 'myuser2'@'%'       | NULL                  | USAGE                    | NO                  |
+---------------------+-----------------------+--------------------------+---------------------+

什么时候显示 USAGE? 从这篇文章中我们知道对应的显示控制代吗在 sql/sql_show.cc。 对应的函数为 fill_schema_schema_privileges。

简单分析源码得到规则如下

a. PRIVILEGE_TYPE

1) 当该用户没有权限,或只有grant_priv的时候,PRIVILEGE_TYPE显示为USAGE;
2) 否则按顺序显示被赋予的权限,每行一个,这些权限包括
(UPDATE_ACL | SELECT_ACL | INSERT_ACL | DELETE_ACL | CREATE_ACL | DROP_ACL | GRANT_ACL | REFERENCES_ACL | INDEX_ACL | ALTER_ACL | CREATE_TMP_ACL |  LOCK_TABLES_ACL | EXECUTE_ACL | CREATE_VIEW_ACL | SHOW_VIEW_ACL | CREATE_PROC_ACL | ALTER_PROC_ACL | EVENT_ACL | TRIGGER_ACL), 但GRANT权限不显示。

b. IS_GRANTABLE

若该用户有 grant_priv 权限,则在列出的所有行的 IS_GRANTABLE 都显示 YES ,否则显示 NO 。

3)权限控制问题

用户是否拥有给其他用户赋权的权限,取决于这个用户本身是否拥有 grant_priv 权限。用一个字段控制一批权限,这样就联想到可能有一个“权限混乱“的现象。

首先,授权必然是要有范围限制的。用户 A 赋权给用户 B,这些赋予的权限不能超过 A 的权限范围。

看以下的操作序列。使用root账户登录。

mysql> grant select,insert,delete,update on *.* to `grant_u2`@localhost  ;     
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.user_privileges where grantee like '%gran_u%';
Empty set (0.01 sec)

mysql> select GRANTEE, PRIVILEGE_TYPE, IS_GRANTABLE from information_schema.user_privileges where grantee like '%grant_u%';
+--------------------------------+----------------------------+--------------------+
| GRANTEE                        | PRIVILEGE_TYPE             | IS_GRANTABLE       |
+--------------------------------+----------------------------+--------------------+
| 'grant_u1'@'localhost'         | SELECT                     | YES                |
| 'grant_u2'@'localhost'         | SELECT                     | NO                 |
| 'grant_u2'@'localhost'         | INSERT                     | NO                 |
| 'grant_u2'@'localhost'         | UPDATE                     | NO                 |
| 'grant_u2'@'localhost'         | DELETE                     | NO                 |
+--------------------------------+----------------------------+--------------------+

说明:上面的操作中,我们给 grant_u1 赋了查询权限且 with grant option. 给 grant_u2 赋了增删改查权限,但没有 grant 权限。从 information_schema.user_privileges 看出目前权限状态正常。

之后用 grant_u1登录, 将 select 的“赋权”权限赋给 grant_u2。

mysql> grant select on *.* to `grant_u2`@localhost with grant option  ;
Query OK, 0 rows affected (0.00 sec)

mysql> select GRANTEE, PRIVILEGE_TYPE, IS_GRANTABLE from information_schema.user_privileges where grantee like '%grant_u%';
+--------------------------------+----------------------------+--------------------+
| GRANTEE                        | PRIVILEGE_TYPE             | IS_GRANTABLE       |
+--------------------------------+----------------------------+--------------------+
| 'grant_u1'@'localhost'         | SELECT                     | YES                |
| 'grant_u2'@'localhost'         | SELECT                     | YES                |
| 'grant_u2'@'localhost'         | INSERT                     | YES                |
| 'grant_u2'@'localhost'         | UPDATE                     | YES                |
| 'grant_u2'@'localhost'         | DELETE                     | YES                |
+--------------------------------+----------------------------+--------------------+
5 rows in set (0.00 sec)

从结果看出,grant_u2 用户拥有了对增删改查的赋权权限。这个已经超出了 grant_u1 的权限范围。

进一步的,再用 grant_u2 登录,执行 grant select,insert,delete,update on *.* to `grant_u1`@localhost with grant option ; 则 grant_u1 用户也拥有了增删改查的赋权权限。

实际上,root 账号设置的权限中,grant_u1、grant_u2 都没有对增删改的赋权权限,但经过上述操作后,这两个用户的权限都扩大了,且超过了原有权限的并集。

4)分析

这个问题的根源,在于 MySQL 在设计上用一个 grant_priv 来控制是否有赋权权限,而每个概念上将每个权限分开。导致在 grant_u1 将“查询赋权”权限赋给 grant_u2 的时候,附带的将其他权限也带进去了。

二、shell 中操作 SQL

在 shell 开发中,很多时候我们需要操作 mysql 数据库(比如:查询数据、导出数据等),但是我们又无法进入 mysql 命令行的环境,就需要在shell 环境中模拟 mysql 的环境,使用 mysql 相关命令

2.1 方案1

mysql -uuser -ppasswd -e "insert LogTable values(...)"  

优点:语句简单,可以跟多条sql语句,用;号隔开

缺点:支持的sql相对简单

例子:

#!/bin/bash
#source /etc/profile

Url=xxx.xxx.xxx.xxx
User=user
Passwd=passwd
Date=`date +"%Y-%m-%d"`

mysql -h$Url -u$User -p$Passwd database-e "

use database;
SELECT a,b from TABLES where table_schema = 'table' ORDER BY b desc;

" >/root/sh/a.$Date.txt

2.2 方案2

准备一个 sql 脚本,名字为 update.sql,例如:

CREATE TABLE `user` (  
  `id` varchar(36) NOT NULL COMMENT '主键',  
  `username` varchar(50) NOT NULL COMMENT '用户名',  
  `password` varchar(50) NOT NULL COMMENT '用户密码',  
  `createdate` date NOT NULL COMMENT '创建时间',  
  `age` int(11) NOT NULL COMMENT '年龄',  
  PRIMARY KEY  (`id`)  
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户信息表';  

DROP TABLE IF EXISTS `visit_log`;  
CREATE TABLE `visit_log` (  
  `id` varchar(36) character set utf8 NOT NULL,  
  `type` int(11) NOT NULL,  
  `content` text character set utf8 NOT NULL,  
  `createdate` date NOT NULL,  
  PRIMARY KEY  (`id`)  
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='访问日志';  

新建一个 update_mysql.sh,内容如下:

use chbdb;  

source update.sql;

然后执行如下命令:

cat update_mysql.sh | mysql --user=root -ppassword  

优点:支持复杂的sql脚本

缺点:

1) 需要两个文件:update.sql 和 update_mysql.sh

2) 一旦中间出错,之后脚本就不会执行,例如:

如果第一张表已经存在,则会报出如下异常:

ERROR 1050 (42S01) at line 1 in file: 'update.sql': Table 'user' already exists

然后脚本退出,第二张表也就无法创建。

2.3 方案3

新建一个shell脚本,格式如下:

#!/bin/bash  

mysql -u* -h* -p* <<EOF
    Your SQL script. 
EOF

例如:

#!/bin/bash  

mysql -uroot  -ppassword <<EOF  

   use chbdb;

    CREATE TABLE user (  
  id varchar(36) NOT NULL COMMENT '主键',  
  username varchar(50) NOT NULL COMMENT '用户名',  
  password varchar(50) NOT NULL COMMENT '用户密码',  
  createdate date NOT NULL COMMENT '创建时间',  
  age int(11) NOT NULL COMMENT '年龄',  
  PRIMARY KEY  (`id`)  
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户信息表';

EOF

优点:

1) 支持复杂的sql脚本

2) 无需其它额外文件

缺点:

1) 表名、字段不能使用单引号,需要修改原有sql语句

2) 一旦中间出错,之后脚本就不会执行,例如:

如果第一张表已经存在,则会报出如下异常:

ERROR 1050 (42S01) at line 1 in file: 'update.sql': Table 'user' already exists

然后脚本退出,第二张表也就无法创建。

2.4 方案4

准备一个 sql 脚本,如 update.sql,然后执行如下命令:

mysql -uroot -ppassword < update.sql

优点:支持复杂的sql脚本

缺点:

1)  一旦中间出错,之后脚本就不会执行,例如:

如果第一张表已经存在,则会报出如下异常:

ERROR 1050 (42S01) at line 1 in file: 'update.sql': Table 'user' already exists

然后脚本退出,第二张表也就无法创建。

三、数据库性能状态

-- 查看当前会话状态

show STATUS;

-- 查看全局状态

show global status;

-- 查看当前会话执行的各项命令统计 com_XXX

show status like 'Com_%';

-- 查看针对InnoDB存储引擎状态的统计

SHOW GLOBAL STATUS LIKE 'Innodb_%';

-- Innodb_rows_deleted   删除的行的汇总数量
-- Innodb_rows_inserted  插入的行的汇总数量
-- Innodb_rows_read      读取的行的汇总数量
-- Innodb_rows_updated   更新的行的汇总数量。无论事务提交还是回滚,都进行累加

-- 查看试图连接mySQL服务器的次数

show global status like 'connections';

-- 查看索引使用情况

show status like 'Handler_read%';

-- Handler_read_rnd_next  该值高,说明查询没有用到索引
-- Handler_read_key      值太低说明索引被使用的次数低,没多大意义
-- Handler_read_next     该值高,说明查询运行低效

-- 查询表级锁争用情况

show status like 'table%';

-- Table_locks_waited 该值高,说明存在较严重的表级锁争用

当上面出现锁等待的情况下,使用putty连接输入以下命令

show global status like 'innodb_row_lock%';

-- Innodb_row_lock_current_waits:当前正在等待锁定的数量;
-- Innodb_row_lock_time:         从系统启动到现在锁定总时间长度;
-- Innodb_row_lock_time_avg:     每次等待所花平均时间;
-- Innodb_row_lock_time_max:     从系统启动到现在等待最常的一次所花的时间;
-- Innodb_row_lock_waits:        系统启动后到现在总共等待的次数;

-- 输入以下命令,查看全局的表锁

show global status like 'table%';

-- Table_locks_waited      该值比较高,则说明存在着较严重的表级锁争用情况。
-- Table_locks_immediate   表示立即释放表锁数

等待的锁不多,就不需要使用InnoDb

-- 如果查看到锁争用情况严重,可以再查看当前执行的SQL

show processlist ;

-- 查看mySQL Server参数

show variables;

select @@profiling;
show profiles;

-- 查看innode的性能

show status like 'Innodb_buffer_pool_%';

information_shcema下的三张表(通过这三张表可以更新监控当前事物并且分析存在的锁问题)  

SELECT * from information_schema.INNODB_TRX;
SELECT * from information_schema.INNODB_locks;
SELECT * from information_schema.innodb_lock_waits;

-- innodb_trx        ( 打印innodb内核中的当前活跃(ACTIVE)事务) 
-- innodb_locks      ( 打印当前状态产生的innodb锁 仅在有锁等待时打印) 
-- innodb_lock_waits (打印当前状态产生的innodb锁等待 仅在有锁等待时打印)

 

 

参考资料

1. 在information_schema中“创建”和维护表

2. MySQL权限控制的设计缺陷?

3. MySQL中information_schema是什么

4. shell处理mysql增、删、改、查

5. shell下执行mysql命令 

 

© 著作权归作者所有

共有 人打赏支持
blackfoxya
粉丝 2
博文 44
码字总数 75066
作品 0
长沙
运维
通过shell脚本进行数据库操作

在做一些日常的运维工作的时候,经常需要些一些shell脚本进行设备性能以及其它参数的监控。在过去的一年工作中,接触到的比较多的是对数据库中某些信息的监控。 于是就想到了用shell+mysql+c...

沉默的子明
2016/07/29
26
0
TokuDB在生产环境的应用场景(zabbix也可以)

一 、背景介绍 近年来,TokuDB作为MySQL的大数据(Big Data)存储引擎受到人们的普遍关注。其架构的核心基于一种新的叫做分形树(Fractal Trees)的索引数据结构,该结构是缓存无关的,即使索...

weiyanwei412
2017/12/14
0
0
PHP开发会员系统

学习PHP很久啦,对PHP语言也熟悉啦,想设计简单的会员系统,内容包括:创建数据库和表、会员注册、会员登录、会员权限设置。涉及到的知识点:md5加密,Cookie/Session创建、使用、销毁等。下...

crossmix
2015/10/05
312
1
不像 MySQL 的 MySQL:MySQL 文档存储介绍

MySQL 文档存储 可以跳过底层数据结构创建、数据规范化和其它使用传统数据库时需要做的工作,直接存储数据。 MySQL 可以提供 NoSQL JSON 文档存储Document Store了,这样开发者保存数据前无需...

19%
06/24
0
0
MYSQL5.6 mysqldump备份与恢复

MYSQL5.6学习——mysqldump备份与恢复 MYSQL备份 冷备份:停止服务进行备份,即停止数据库的写入 热备份:不停止服务进行备份(在线) l mysql的MyIsam引擎只支持冷备份,InnoDB支持热备份,...

xiaocao13140
05/29
0
0
shell辅助管理网站后台数据

shell 辅助管理数据库技术关键在于掌握MySQL基本语句就能轻松通过shell操作MySQL并操作数据流,在linux里操作文本数据最方便了,因为有强大的awk程序,所以普遍的做法是将数据流导入临时文件...

cjp路人
2014/03/12
0
0
SQLmap注入获取Webshell及系统权限研究

使用sqlmap除了能进行sql注入渗透测试外,其还提供了强大的命令执行功能可以进行udf提权、MSSQL下xp_cmdshell提权,在条件允许的情况下,可以获取操作系统shell和SQL shell,有的还可以直接获...

simeon2005
05/16
0
0
Shell脚本中执行sql语句操作mysql的5种方法

Shell脚本中执行sql语句操作mysql的5种方法 投稿:junjie 字体:[增加 减小] 类型:转载 时间:2014-10-31 我要评论 对于自动化运维,诸如备份恢复之类的,DBA经常需要将SQL语句封装到shell...

梁东升
2016/07/13
0
0
使用sqlmap对某php网站进行注入实战及安全防范

使用sqlmap对某php网站进行注入实战 一般来讲一旦网站存在sql注入漏洞,通过sql注入漏洞轻者可以获取数据,严重的将获取webshell以及服务器权限,但在实际漏洞利用和测试过程中,也可能因为服...

simeon2005
06/29
0
0
phalapi-进阶篇5(数据库读写分离以及多库使用)

先在这里感谢phalapi框架创始人@dogstar,为我们提供了这样一个优秀的开源框架. 读写分离是我们常用的一种解决方案,它可以解决大量读操作的时候数据库瓶颈的问题,我们在真正开发一个项目的过程...

喵了_个咪
2015/11/24
1K
4

没有更多内容

加载失败,请刷新页面

加载更多

下一页

vue-router懒加载

1. vue-router懒加载定义 当路由被访问的时候才加载对应组件 2. vue-router懒加载作用 当构建的项目比较大的时候,懒加载可以分割代码块,提高页面的初始加载效率。 ###3. vue-router懒加载实...

不负好时光
11分钟前
0
0
庆祝法国队夺冠:用Python放一场烟花秀

天天敲代码的朋友,有没有想过代码也可以变得很酷炫又浪漫?今天就教大家用Python模拟出绽放的烟花庆祝昨晚法国队夺冠,工作之余也可以随时让程序为自己放一场烟花秀。 这个有趣的小项目并不...

猫咪编程
13分钟前
0
0
SpringBoot | 第七章:过滤器、监听器、拦截器

前言 在实际开发过程中,经常会碰见一些比如系统启动初始化信息、统计在线人数、在线用户数、过滤敏高词汇、访问权限控制(URL级别)等业务需求。这些对于业务来说一般上是无关的,业务方是无需...

oKong
27分钟前
4
0
存储结构分四类:顺序存储、链接存储、索引存储 和 散列存储

存储结构分四类:顺序存储、链接存储、索引存储 和 散列存储 存储结构分四类:顺序存储、链接存储、索引存储 和 散列存储。 顺序结构和链接结构适用在内存结构中。 顺序表每个单元都是按物理...

DannyCoder
37分钟前
1
0
Firefox 61已经为Ubuntu 提供支持

最新和最好的Mozilla Firefox 61 “Quantum”网络浏览器已经为Ubuntu Linux操作系统的用户提供了支持,现在可以通过官方软件库进行更新。 Mozilla于2018年6月26日发布了Firefox 61版本,该版...

六库科技
今天
0
0
Win10升级后执行系统封装(Sysprep)报错

开始封装 一年多以前开始给公司封装Win10系统,便于统一给公司电脑初始化携带各种软件的系统,致力于装完既可以开发的状态。那时候最新的版本是Win10 1703版本,自然就以他为母盘,然后结合V...

lyunweb
今天
40
0
php 性能优化

#什么情况下会遇到性能问题 PHP 语法使用的不恰当

to_be_better
今天
0
0
Jenkins 构建触发器操作详解

前言 跑自动化用例每次用手工点击jenkins出发自动化用例太麻烦了,我们希望能每天固定时间跑,这样就不用管了,坐等收测试报告结果就行。 一、定时构建语法 * * * * * (五颗星,中间用空格隔...

覃光林
今天
0
0
IDEA配置技巧

超详细设置Idea类注释模板和方法注释模板 idea去掉注解param下划线 JetBrains全系列破解

AK灬
今天
0
0
rsync通过服务同步/Linux系统日志/screen工具

rsync通过服务同步 分为服务端(机器A) 和客户端(机器B) 机器A操作编辑/etc/rsyncd.conf配置文件 [root@yolks1 ~]# vim /etc/rsyncd.conf 文件中添加以下配置 port=873 ...

Hi_Yolks
今天
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部