作用
直接通过查询 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 把没有挂载的存储也显示出来