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

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

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

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

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

一、information_schema 数据库表说明

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/profileUrl=xxx.xxx.xxx.xxxUser=userPasswd=passwdDate=`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* <

例如：

#!/bin/bash mysql -uroot -ppassword <

优点：

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。