openstack 管理三十六 - openstack 资源查询常用 sql

作用

直接通过查询 openstack 数据库, 获得相应的常见查询结果
查询用户使用中主机, 及其主机对应信息
查询用户使用中存储, 及其存储对应信息

查询用户对应主机

参考例子

mysql> select a.display_name instnace from nova.instances a,  keystone.user b 
where b.id=a.user_id 
  and a.vm_state in ('active', 'building', 'stopped') 
  and a.deleted = 0 
  and b.name='terry.zeng';
+---------------------------------------+
| instnace                              |
+---------------------------------------+
| terry-zskvt.xxxxxxx.xxx               |
| cloud-logmon-rmasm.xxxxxxx.xxx        |
| cloud-logmon-detzu.xxxxxxx.xxx        |
| cloud-logmon-rzje2.xxxxxxx.xxx        |
| cloud-logmon-rejat.xxxxxxx.xxx        |
| cloud-logmon-tgshs.xxxxxxx.xxx        |
| cloud-logmon-9kpm4.xxxxxxx.xxx        |
| cloud-logmon-ragyg.xxxxxxx.xxx        |
| cloud-logmon-zwvff.xxxxxxx.xxx        |
| cloud-logmon-vnagw.xxxxxxx.xxx        |
| cloud-logmon-eqre9.xxxxxxx.xxx        |
| cloud-logmon-wrji8.xxxxxxx.xxx        |
| cloud-logmon-fjizf.xxxxxxx.xxx        |
| cloud-logmon-w5icm.xxxxxxx.xxx        |
| terryzeng-gz-qa-dns-vid7e.xxxxxxx.xxx |
| terry-elk-exquv.xxxxxxx.xxx           |
| pdns-admin-ctuxy.xxxxxxx.xxx          |
| terry-w6im9.xxxxxxx.xxx               |
| terryzeng-gz-qa-dns-d4yzu.xxxxxxx.xxx |
| terry-test-uq2pu.xxxxxxx.xxx          |
| terry.rhel7.xxxxxxx.xxx               |
+---------------------------------------+
21 rows in set (0.07 sec)

利用主机名查询对应id, ip 地址等相关信息

参考语法

mysql> select a.display_name instance, b.name user, 
  a.vcpus cpu, a.memory_mb, a.node hypervisor, 
  c.name tenant, e.ip_address, a.uuid 
from nova.instances a, keystone.user b, 
   keystone.project c, neutron.ports d, neutron.ipallocations e 
where a.vm_state in ('active', 'building', 'stopped') 
  and a.deleted = 0 
  and a.user_id = b.id 
  and a.project_id = c.id 
  and d.device_id = a.uuid 
  and e.port_id = d.id 
  and a.display_name='terry-elk-exquv.xxxxxxx.xxx';
+-----------------------------+------------+------+-----------+-----------------------------------+--------+---------------+--------------------------------------+
| instance                    | user       | cpu  | memory_mb | hypervisor                        | tenant | ip_address    | uuid                                 |
+-----------------------------+------------+------+-----------+-----------------------------------+--------+---------------+--------------------------------------+
| terry-elk-exquv.xxxxxxx.xxx | terry.zeng |    2 |      4096 | hh-yun-compute-130190.xxxxxxx.xxx | DEV    | xx.xxx.253.92 | 21138755-1405-42d4-9b32-8c2d025940ac |
+-----------------------------+------------+------+-----------+-----------------------------------+--------+---------------+--------------------------------------+
1 row in set (0.07 sec)

查询某用户云盘与实例之间关系

参考语法

mysql> select c.id, c.volume_name, c.status, c.size_GB, d.display_name  
from  ( 
  select a.id, a.display_name volume_name, a.status, 
    a.size size_GB, a.instance_uuid 
  from cinder.volumes a, keystone.user b 
  where a.user_id=b.id 
  and a.status in ('in-use','available','detaching','attaching') 
  and b.name='terry.zeng' ) c
left join nova.instances d 
on c.instance_uuid = d.uuid;
+--------------------------------------+-------------+-----------+---------+---------------------------------------+
| id                                   | volume_name | status    | size_GB | display_name                          |
+--------------------------------------+-------------+-----------+---------+---------------------------------------+
| 096fbef4-65c9-4e14-b7d7-2a9c15cc9520 | elkdisk2    | in-use    |     220 | cloud-logmon-rmasm.xxxxxxx.xxx        |
| 0bed542e-18a5-4f2a-bda7-0f2e97ad988e | tthome      | in-use    |     500 | terry-w6im9.xxxxxxx.xxx               |
| 33e5a1de-68dc-4b08-8580-a4ba99fb8338 | elkdata7    | in-use    |    3000 | cloud-logmon-fjizf.xxxxxxx.xxx        |
| 36963634-3682-4865-b8b8-7584f608ec8d | terrydata   | in-use    |     500 | terry-w6im9.xxxxxxx.xxx               |
| 5e951555-05f7-4388-9116-38f52a8a6fb5 | elkdata4    | in-use    |    3000 | cloud-logmon-9kpm4.xxxxxxx.xxx        |
| 6d66f057-db81-442f-a426-ef1e52e450f1 | t2          | in-use    |      10 | terryzeng-gz-qa-dns-vid7e.xxxxxxx.xxx |
| 7039530f-c5b3-4d3c-9661-96f27d20e526 | elkdata5    | in-use    |    3000 | cloud-logmon-eqre9.xxxxxxx.xxx        |
| 7cd03f87-2b62-451e-aa83-9b0ce22050c8 | elkdata6    | in-use    |    3000 | cloud-logmon-wrji8.xxxxxxx.xxx        |
| 80d8ea33-0947-4538-b337-bfcbb36f190b | elkdata2    | in-use    |    3000 | cloud-logmon-rejat.xxxxxxx.xxx        |
| 9ac1af5b-0fe5-4abc-9c4c-b95e2397ec57 | elkdisk1    | in-use    |     220 | cloud-logmon-detzu.xxxxxxx.xxx        |
| aadd0c9e-4372-4a0a-8dd6-5813290306c9 | elkdata3    | in-use    |    3000 | cloud-logmon-tgshs.xxxxxxx.xxx        |
| d8e3b6e3-ab5c-4913-9cfb-db1a6172a923 | terrytest   | available |      50 | NULL                                  |
| fa45aff9-b580-48af-ab98-54f819827e4c | elkdata1    | in-use    |    3000 | cloud-logmon-rzje2.xxxxxxx.xxx        |
+--------------------------------------+-------------+-----------+---------+---------------------------------------+
13 rows in set (0.02 sec)

注意
由于需要进行 volume 与 instance 的结合查询, 所以当前把 volume 信息当做一个子查询执行
由于部分 volume 并不一定正在挂载中, 因此利用 left join 把没有挂载的存储也显示出来