Literate-database-work

1. Header Properties
2. Using SQL Code Blocks
3. More Interesting Queries
4. 总结
5. Footnotes:

我曾被要求搞清楚OpenStack的Keystone服务(它的授权与认证组件)是如何使用MySQL的. OpenStack的文档一向零散,因此我决定直接研究它的数据库,而不是阅读源代码.

我当然不是DBA,但我觉得我可以看一下某个开发系统的数据并能够很好的解决所遇到的BUG. 我探索数据库的方法与他人有点不一样,我使用的是自创的 Literate Devops 方法. 我在前一段时间已经介绍过这种方法了,现在我再以另一个案例为例,分享这种方法.

1 Header Properties

我通过SSH登陆到控制节点并创建一个隧道与我本地系统的3306端口连接:^1

ssh -L 3306:controller:3306 controller

然后我打开最新的笔记(org-mode格式)并创建一个新标题. 标题后时一个可折叠的属性drawer. 其中包含了数据库连接的信息(这些信息是我从远程系统的配置文件中找到的).

** MySQL Analysis
  :PROPERTIES:
  :engine:   mysql
  :dbhost:   localhost
  :database: keystone
  :dbuser:   keystone
  :dbpassword: d97d880017c8b965
  :cmdline:  --protocol=tcp
  :exports:  both
  :END:

注意 cmdline 属性的值. 若mysql主机为 localhost 的话,则MySQL连接器会尝试通过local file socket来建立与数据库的连接. 由于这其实是个转发端口,因此我需要明确指定使用TCP协议创建连接.

2 Using SQL Code Blocks

现在我可以开始我的以文字为导向的探索. 由于我假定我的结果会被发送给同事阅读, my prose was for them as much as me…

Not knowing anything about the token properties in the Keystone
database structure, I jumped into the database to expose a bit of the
schema. What follows is a summary of my exploration as well as some
recommendations we can use to ascertain its health.

First, here are the tables associated with the =keystone= database:

文档中每个端口后面都跟着一个SQL代码块.^2 例如:

#+BEGIN_SRC sql
  SHOW tables;
#+END_SRC

这种方式的美妙之处在于,我可以通过按下 C-c C-c 来执行改代码块中的代码,并将查询数据库的结果以org-mode中表格的形式插入到文档中:

#+RESULTS:
| Tables_in_keystone     |
|------------------------|
| credential             |
| domain                 |
| endpoint               |
| group                  |
| group_domain_metadata  |
| group_project_metadata |
| migrate_version        |
| policy                 |
| project                |
| role                   |
| service                |
| token                  |
| trust                  |
| trust_role             |
| user                   |
| user_domain_metadata   |
| user_group_membership  |
| user_project_metadata  |

基于这个结果,我继续往下探索. 名为user的表格看起来蛮有意思的:

The =user= table has the following schema:

#+BEGIN_SRC sql
  SHOW columns FROM user;
#+END_SRC

其结果给了我很多查询的灵感:

#+RESULTS:
| Field              | Type         | Null | Key | Default | Extra |
|--------------------+--------------+------+-----+---------+-------|
| id                 | varchar(64)  | NO   | PRI | NULL    |       |
| name               | varchar(255) | NO   |     | NULL    |       |
| extra              | text         | YES  |     | NULL    |       |
| password           | varchar(128) | YES  |     | NULL    |       |
| enabled            | tinyint(1)   | YES  |     | NULL    |       |
| domain_id          | varchar(64)  | NO   | MUL | NULL    |       |
| default_project_id | varchar(64)  | YES  |     | NULL    |       |

并且当我导出我的org-mode文件时,这些表格都被渲染的很不错:

Field	Type	Null	Key	Default
id	varchar(64)	NO	PRI	NULL
name	varchar(255)	NO		NULL
extra	text	YES		NULL
password	varchar(128)	YES		NULL
enabled	tinyint(1)	YES		NULL
domain_id	varchar(64)	NO	MUL	NULL
default_project_id	varchar(64)	YES		NULL

3 More Interesting Queries

我无意于向你展示我探索的细节(因为这只是一个例子而已,用于想你展示这种基于org-mode的literate devops概念的威力). 但是,由于我输入的SQL语句时直接发送到数据库的,因此我还可以使用MySQL特有的语法:

 Clearly we are seeing a lot of expired tokens. How old is the oldest
 expire token?

 #+BEGIN_SRC sql
   SELECT expires,
    (UNIX_TIMESTAMP(expires) - UNIX_TIMESTAMP(NOW()))/60 AS minutes_ago,
    (UNIX_TIMESTAMP(expires) - UNIX_TIMESTAMP(NOW()))/60/60 AS hours_ago
   FROM token
   ORDER BY expires DESC
   LIMIT 1
 #+END_SRC

 #+RESULTS:
 | expires             | minutes_ago |   hours_ago |
 |---------------------+-------------+-------------|
 | 2015-04-08 18:49:42 |   1438.2500 | 23.97083333 |

Huh. =1439= is /almost/ 24 hours ago. Is that our policy? Actually, it
is indeed a configurable policy. Set to 24 hours in case long running
stories cache that token.

4 总结

最终结果很有趣,我将其导出成HTML格式作为邮件内容,并由此展开最初的讨论, 然后最终会记入我们的Wiki系统.

导出的文档可以在这里看到 (为防你好奇,我修改掉了原始数据). 你也可以在这看到原始的org文档.

这种方法的另一个副作用时当我通过skype与远程的同事讨论数据库时,我可以与他共享Emacs屏幕,然后重新运行这些查询语句病获得输出结构. 然后将他的想法记录下来以备以后讨论.

5 Footnotes:

你需要配置MySQL使之允许远程连接上数据库.

方法是,编辑 /etc/mysql/my.cnf 文件, 将 bind-address 改为 0.0.0.0. 若能将本机地址添加到数据库服务器上的 /etc/hosts 中,使之能进行反向查找,那也是有助远程连接数据库的.

若不知道如何创建一个可以从任何主机连接上系统的数据库用户账户,请参照下面代码:

CREATE USER 'howard'@'%' IDENTIFIED BY 'byebye';
GRANT ALL PRIVILEGES ON *.* TO 'howard'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

之后,你可以试着使用CLI客户端进行连接了:

mysql -h localhost -P 3306 -u howard -p=byebye --protocol=tcp -e "show tables;"

若出现下面的错误:

Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'howard'@'HABRAMS-02' (using password: YES)

请执行下面的查询语句来检查数据库用户账户:

SELECT user, host FROM mysql.user;

返回的结果可能类似这样的:

user	host
howard	%
root	10.0.2.2
root	127.0.0.1
root	::1

root

localhost

Still having troubles, re-run the CREATE USER SQL statement with the following hosts:

% … should allow all.
localhost … isn’t really what you want
The hostname of your local system

Be careful with adding entries that you don’t need, for it appears that for MySQL, order matters, and some combination will be chosen for you.

In order to use sql as a Babel formatting language, you have to specify it in the org-babel-load-languages list. In my case, I don’t use sql enough, so M-x load-library and then entering ob-sql is sufficient.

Or:

(require 'sql)
(require 'ob-sql)

EMACS-DOCUMENT