第17章 mysql系统库应用示例荟萃

17.1.1　查看全局权限

mysql> grant select on *.* to test_global@'%' identified by 'test';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> use mysql
Database changed
mysql> select * from user where user='test_global'\G
......
1 row in set (0.00 sec)

mysql> select * from db where user='test_global'\G
Empty set (0.02 sec)

mysql> select * from tables_priv where user='test_global'\G
Empty set (0.00 sec)

mysql> select * from columns_priv where user='test_global'\G
Empty set (0.00 sec)

17.1.2　查看库级别权限

mysql> create database test_grant;
Query OK, 1 row affected (0.01 sec)

mysql> grant select on test_grant.* to test_db@'%' identified by 'test';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from user where user='test_db'\G
......
1 row in set (0.00 sec)

mysql> select * from db where user='test_db'\G
......
1 row in set (0.00 sec)

mysql> select * from tables_priv where user='test_db'\G
Empty set (0.00 sec)

mysql> select * from columns_priv where user='test_db'\G
Empty set (0.00 sec)

17.1.3　查看表级别权限

mysql> use test_grant
Database changed
mysql> create table test_table_grant(id int);
Query OK, 0 rows affected (0.03 sec)

mysql> grant select on test_grant.test_table_grant to test_table@'%' identified by 'test';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> select * from mysql.user where user='test_table'\G
......
1 row in set (0.00 sec)

mysql> select * from mysql.db where user='test_table'\G
Empty set (0.00 sec)

mysql> select * from mysql.tables_priv where user='test_table'\G
......
1 rows in set (0.00 sec)

mysql> select * from mysql.columns_priv where user='test_table'\G
Empty set (0.00 sec)

17.1.4　查看字段级别权限

mysql> grant select(id) on test_grant.test_table_grant to test_column@'%' identified by 'test';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> select * from mysql.user where user='test_column'\G
......
1 row in set (0.00 sec)

mysql> select * from mysql.db where user='test_column'\G
Empty set (0.00 sec)

mysql> select * from mysql.tables_priv where user='test_column'\G
......
1 row in set (0.00 sec)

# 通过对mysql.tables_priv表的查询发现，test_column用户对test_table_grant表具有字段级别的Select权限。由于字段级别权限是针对具体的字段授予的，这些信息需要通过mysql.columns_priv表来查询，如下所示
mysql> select * from mysql.columns_priv where user='test_column'\G
......
1 row in set (0.00 sec)

• 提示：以上示例中未涉及的权限管理请读者自行尝试，这里不再赘述。

17.2.1 查看表统计信息

mysql> use test
Database changed
mysql> create table test_stat(id int not null primary key auto_increment);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from mysql.innodb_table_stats where table_name='test_stat'\G
......
1 row in set (0.00 sec)

17.2.2　查看索引统计信息

mysql> alter table test_stat add column test1 int,add unique index i_test1(test1);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table test_stat add column test2 int,add index i_test2(test2);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

17.3　查看SQL日志信息

mysql> set global log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> set global general_log=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global long_query_time=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 4                  |
+-----------------+
1 row in set (0.00 sec)

mysql> use test
Database changed
mysql> lock table test_stat read;
Query OK, 0 rows affected (0.00 sec)

mysql> select connection_id();
......
1 row in set (0.00 sec)

mysql> select connection_id();
......
1 row in set (0.00 sec)

mysql> use test
Database changed
mysql> begin;# 显式开启一个事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_stat values(1,1,1);# 被阻塞

# general_log表
mysql> select * from mysql.general_log;
......
19 rows in set (0.00 sec)

# 以上数据表明，general_log表中的数据是在MySQL服务器接收到语句之后一开始执行就立即记录的。现在我们查询slow_log表
mysql> select * from mysql.slow_log;
......
12 rows in set (0.00 sec)
# 此时发现slow_log表中没有INSERT语句的慢查询日志记录

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_stat values(1,1,1);
Query OK, 1 row affected (2 min 12.87 sec)

# general_log表
mysql> select * from mysql.general_log;
......
22 rows in set (0.00 sec)    

# slow_log表
mysql> select * from mysql.slow_log;
......
14 rows in set (0.00 sec)

# 注意：此时会话3的事务仍未提交，所以在慢查询日志表中记录的语句与事务无关，只需要语句执行完成即会进行记录

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

# general_log表
mysql> select * from mysql.general_log;
......
25 rows in set (0.00 sec)

# slow_log表
mysql> select * from mysql.slow_log;
......
15 rows in set (0.00 sec)

• 温馨提示：关于mysql schema库更详细的内容，可参阅微信公众号“沃趣技术”。或详见开源文档：mysql schema详解

---

上一篇：第16章　mysql系统库之日志记录表 | 下一篇：第18章　复制技术的演进