文档章节

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

blackfoxya
 blackfoxya
发布于 06/19 11:47
字数 2909
阅读 5
收藏 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
博文 64
码字总数 89900
作品 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

没有更多内容

加载失败,请刷新页面

加载更多

Spring Boot 使用parent方式引用时 获取值属性方式默认@

采用官网提供的 引入依赖时,application.properties中的值变量,无法通过${}获取,这是由于Spring Boot 已经将字符方式调整为 <resource.delimiter>@</resource.delimiter>。故如果在,工程中...

翱翔fly
13分钟前
1
0
HTTPS证书验证流程及SSL证书生成步骤【附nginx开启https配置】

------------------------------------------------------------ HTTPS证书验证流程(极简化版) 1.客户端向服务端请求证书(server.crt) 2.服务端下发证书(server.crt) 3.客户端用预制的...

xiaomin0322
14分钟前
1
0
预编译sql语句和参数化sql能有效防止——sql注入攻击——

预编译和参数化处理sql原理类似: 参数化处理sql 是将参数进行了字符串化处理,把参数中的单引号,井号#,等都做了转义处理,进而在执行sql语句时不会被当做sql中的定界符和指定符来执行。 ...

fang_faye
15分钟前
1
0
springboot最全配置解释

​​​​​​​# ----------------------------------------# 核心属性# ----------------------------------------# 文件编码banner.charset= UTF-8# 文件位置banner.locati...

废柴大叔
16分钟前
57
1
(大致了解下)从单机到2000万QPS: 知乎Redis平台发展与演进之路

导读:知乎存储平台团队基于开源Redis 组件打造的知乎 Redis 平台,经过不断的研发迭代,目前已经形成了一整套完整自动化运维服务体系,提供很多强大的功能。本文作者是是该系统的负责人,文...

Ocean_K
23分钟前
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部