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命令