CS数据库的相关操作
时间:2022-03-10 17:02
备注:我们对于CS数据库的操作多数情况下只是查询操作,尽量不要在数据库中直接修改记录的值
第一、首先备份数据库
1、进入mysql数据库
[root@master ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 188 Server version: 5.1.61-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> |
2、查看当前的数据库列表
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | cloud | | cloud_usage | | cloudbridge | | glance | | mysql | | nova | +--------------------+ 7 rows in set (0.00 sec) mysql>exit; |
3、备份所有的数据库
[root@master ~]# mysqldump -u root -p -A --events > ./all_db_20140427.sql Enter password: |
备注:
-A :表示备份所有的数据库
--enents :该选项为了避免在备份mysql(包含用户名称及授权表的数据库)时报错
特别注意的是:当重新安装mysql,需要在mysql中创建原有的数据库(mysql、information_schema默认存在)都存在才能进行恢复
第二、更改虚拟机vm的状态
1、进入mysql并切换到cloud数据库
[root@master ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 190 Server version: 5.1.61-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> use cloud; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed |
2、当前的vm列表
备注:此处我们以查看“From-Xen-29”虚拟机的信息为例
- 查询指定虚拟机所有信息
备注:此处我们查看name是“From-Xen-29”的虚拟机的信息
mysql> select * from vm_instance where name="From-Xen-29" \G; *************************** 1. row *************************** id: 21 name: From-Xen-29 uuid: 693a72f8-5f8d-4c92-acd2-1826d4449d41 instance_name: i-2-21-VM state: Running vm_template_id: 203 guest_os_id: 57 private_mac_address: 06:c9:50:00:00:0e private_ip_address: 10.30.18.29 pod_id: 1 data_center_id: 1 host_id: 1 last_host_id: 1 proxy_id: NULL proxy_assign_time: NULL vnc_password: cOheL3tWc6T/cNYULcoF+tdjDgTcDgbXQqb2xjcdc4E= ha_enabled: 0 limit_cpu_use: 0 update_count: 3 update_time: 2014-04-26 01:23:39 created: 2014-04-26 01:23:15 removed: NULL type: User vm_type: User account_id: 2 domain_id: 1 service_offering_id: 2 reservation_id: 1c5d691c-af25-4b36-a355-d1aed3e30fa9 hypervisor_type: XenServer 1 row in set (0.00 sec) |
4、只查询虚拟机状态信息
备注:此处我们只查看了name是“From-Xen-29”的虚拟机的id和state状态信息
mysql> select id,state from vm_instance where name="From-Xen-29" ; +----+---------+ | id | state | +----+---------+ | 21 | Running | +----+---------+ 1 row in set (0.00 sec) mysql> |
5、更改虚拟机的状态
mysql> update vm_instance set state=‘Stopped‘ where id=21; Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
备注:一般情况下不要修改数据库中记录的值。主要用于在CloudStack界面中虚拟机显示不正常状态的修改:例如将Expuning 修改成 Stopped
第三、管理IP被占用
备注:管理IP不能像来宾IP一样进行二次添加。如果管理IP分配的过少,且在mysql数据库中被占用导致管理IP不足
mysql> select * from op_dc_ip_address_alloc; mysql> update op_dc_ip_address_alloc set nic_id=null,reservation_id=null,taken=null; mysql> commit; |
第四、查看卷信息
备注:卷都是存储在主存储上的
mysql> select id,name,path,folder,volume_type from volumes where state="ready" \G; *************************** 1. row *************************** id: 1 name: ROOT-1 path: be954476-935f-4d7a-a8a1-021d8882a39d folder: /export/primary1 volume_type: ROOT *************************** 2. row *************************** id: 2 name: ROOT-2 path: 29fa2289-e368-4507-a94e-0ceec3f64a55 folder: /export/primary1 volume_type: ROOT *************************** 3. row *************************** id: 4 name: From-Xen-32-E path: 97946a32-c7ac-4462-830b-8118625d426b folder: /export/primary1 volume_type: DATADISK *************************** 4. row *************************** id: 5 name: From-Xen-30-E path: 61aab5bc-0194-494b-a2b6-bbbc6fc80abc folder: /export/primary1 volume_type: DATADISK *************************** 5. row *************************** id: 6 name: From-Xen-29-E.vhd path: d8a44de4-caf2-48b0-93c6-83737af4ba61 folder: /export/primary1 volume_type: DATADISK *************************** 6. row *************************** id: 8 name: ROOT-4 path: 02ee677a-4cc8-4da8-be7c-9e45a2ff5e0d folder: /export/primary1 volume_type: ROOT *************************** 7. row *************************** id: 25 name: ROOT-21 path: d62bd6ab-9485-47a6-8c30-413567b01ff3 folder: /export/primary1 volume_type: ROOT *************************** 8. row *************************** id: 28 name: ROOT-24 path: 25e7160e-4fb3-4bab-9d81-d45789aa4cbc folder: /export/primary1 volume_type: ROOT *************************** 9. row *************************** id: 29 name: ROOT-25 path: f6886c71-da8f-461a-8576-88ce54752f01 folder: /export/primary1 volume_type: ROOT 9 rows in set (0.00 sec) |
第五、查看模板信息
mysql> select id,name,removed from vm_template where format="vhd" and type="user"\G; *************************** 1. row *************************** id: 202 name: XP-SP2 removed: NULL *************************** 2. row *************************** id: 203 name: From-Xen-29 removed: NULL *************************** 3. row *************************** id: 204 name: From-Xen-32 removed: NULL *************************** 4. row *************************** id: 205 name: From-Xen-30 removed: NULL 4 rows in set (0.00 sec) |
第六、查看快照信息
备注:我们只查看状态是“backedup”的快照信息
mysql> select name,backup_snap_id from snapshots where status="backedup"\G; *************************** 1. row *************************** name: Feom-Xen-32_ROOT-25_20140426072726 backup_snap_id: b84fcc71-e731-4a33-9c81-8c8a82b8f5a4 *************************** 2. row *************************** name: From-Xen-30_ROOT-24_20140426073350 backup_snap_id: 70f0ed98-4416-481f-8c85-df2cfaef4e33 *************************** 3. row *************************** name: From-Xen-29_ROOT-21_20140426074236 backup_snap_id: 0ae4c760-04ad-4e98-9434-3f6194b734e6 *************************** 4. row *************************** name: From-Xen-29_From-Xen-29-E.vhd_20140426074703 backup_snap_id: 5ce7cdf7-ac9e-40f0-8312-67b63af68b55 *************************** 5. row *************************** name: From-Xen-30_From-Xen-30-E_20140426075223 backup_snap_id: 3db95d31-1698-4477-b12a-0c15d147fff2 *************************** 6. row *************************** name: Feom-Xen-32_From-Xen-32-E_20140426075617 backup_snap_id: ffa76d2d-09d7-4370-9468-e4530502a9ca 6 rows in set (0.00 sec) |
备注:更改vm的ip地址
1、涉及表 user_ip_address和nics
- 更改表记录操作
update user_ip_address set public_ip_address=‘1.1.1.1‘ where public_ip_address=‘$OLDIP‘;
update user_ip_address set public_ip_address=‘$OLDIP‘ where public_ip_address=‘$NEWIP‘;
update user_ip_address set public_ip_address=‘$NEWIP‘ where public_ip_address=‘1.1.1.1‘;
update nics set ip4_address=‘$NEWIP‘ where ip4_address=‘$OLDIP‘ and state=‘Reserved‘;
3、重启vm虚拟机
4、如有相应的vrouter正在运行,需要删除重建该vrouter
第七、cloud数据库中的表名称
mysql> show tables ; +--------------------------------------+ | Tables_in_cloud | +--------------------------------------+ | account | | account_details | | account_network_ref | | account_vlan_map | | alert | | async_job | | autoscale_policies | | autoscale_policy_condition_map | | autoscale_vmgroup_policy_map | | autoscale_vmgroups | | autoscale_vmprofiles | | cluster | | cluster_details | | cluster_vsm_map | | cmd_exec_log | | conditions | | configuration | | console_proxy | | counter | | data_center | | data_center_details | | dc_storage_network_ip_range | | disk_offering | | domain | | domain_network_ref | | domain_router | | elastic_lb_vm_map | | event | | external_firewall_devices | | external_load_balancer_devices | | firewall_rules | | firewall_rules_cidrs | | guest_os | | guest_os_category | | guest_os_hypervisor | | host | | host_details | | host_pod_ref | | host_tags | | hypervisor_capabilities | | inline_load_balancer_nic_map | | instance_group | | instance_group_vm_map | | keystore | | launch_permission | | load_balancer_stickiness_policies | | load_balancer_vm_map | | load_balancing_rules | | mshost | | mshost_peer | | netapp_lun | | netapp_pool | | netapp_volume | | network_external_firewall_device_map | | network_external_lb_device_map | | network_offerings | | network_rule_config | | networks | | nics | | ntwk_offering_service_map | | ntwk_service_map | | op_dc_ip_address_alloc | | op_dc_link_local_ip_address_alloc | | op_dc_storage_network_ip_address | | op_dc_vnet_alloc | | op_ha_work | | op_host | | op_host_capacity | | op_host_transfer | | op_host_upgrade | | op_it_work | | op_lock | | op_networks | | op_nwgrp_work | | op_pod_vlan_alloc | | op_user_stats_log | | op_vm_ruleset_log | | ovs_tunnel_interface | | ovs_tunnel_network | | physical_network | | physical_network_isolation_methods | | physical_network_service_providers | | physical_network_tags | | physical_network_traffic_types | | pod_vlan_map | | port_forwarding_rules | | port_profile | | private_ip_address | | project_account | | project_invitations | | projects | | remote_access_vpn | | resource_count | | resource_limit | | resource_tags | | router_network_ref | | s2s_customer_gateway | | s2s_vpn_connection | | s2s_vpn_gateway | | secondary_storage_vm | | security_group | | security_group_rule | | security_group_vm_map | | sequence | | service_offering | | snapshot_policy | | snapshot_schedule | | snapshots | | ssh_keypairs | | stack_maid | | static_routes | | storage_pool | | storage_pool_details | | storage_pool_host_ref | | storage_pool_work | | swift | | sync_queue | | sync_queue_item | | template_host_ref | | template_spool_ref | | template_swift_ref | | template_zone_ref | | upload | | usage_event | | user | | user_ip_address | | user_statistics | | user_vm | | user_vm_details | | version | | virtual_router_providers | | virtual_supervisor_module | | vlan | | vm_instance | | vm_template | | vm_template_details | | volume_host_ref | | volumes | | vpc | | vpc_gateways | | vpc_offering_service_map | | vpc_offerings | | vpn_users | +--------------------------------------+ 143 rows in set (0.01 sec) |
第八、account及user相关
备注1:CS默认的账户只有两个,可以手动建立多个账户并赋予不同的角色(user、admin)
备注:需要注意的是表中admin既是一个可以用于管理登录的账号,也是一个具有管理权限的角色
1、account表
备注:主要用于存放账号的相关信息
mysql> select * from account \G; *************************** 1. row *************************** id: 1 account_name: system uuid: 6d527226-710a-4d62-8f19-3f5f2640d42e type: 1 domain_id: 1 state: enabled removed: NULL cleanup_needed: 0 network_domain: NULL default_zone_id: NULL *************************** 2. row *************************** id: 2 account_name: admin uuid: 1f8b679f-a327-43e0-8af9-726d3d76ccc9 type: 1 domain_id: 1 state: enabled removed: NULL cleanup_needed: 0 network_domain: NULL default_zone_id: NULL 2 rows in set (0.00 sec) |
2、account_details表
备注:默认为空表
mysql> select * from account_details \G; Empty set (0.00 sec) |
3、account_network_ref表
mysql> select * from account_network_ref \G; *************************** 1. row *************************** id: 1 account_id: 1 network_id: 200 is_owner: 1 *************************** 2. row *************************** id: 2 account_id: 1 network_id: 201 is_owner: 1 *************************** 3. row *************************** id: 3 account_id: 1 network_id: 202 is_owner: 1 *************************** 4. row *************************** id: 4 account_id: 1 network_id: 203 is_owner: 1 *************************** 5. row *************************** id: 5 account_id: 1 network_id: 204 is_owner: 1 5 rows in set (0.00 sec) |
4、account_vlan_map
mysql> select * from account_vlan_map \G; Empty set (0.00 sec) |
5、user表
mysql> select * from user \G; *************************** 1. row *************************** id: 1 uuid: 5196c6cc-799f-4ab6-bbe6-b709bd21cd1a username: system password: account_id: 1 firstname: system lastname: cloud email: NULL state: enabled api_key: NULL secret_key: NULL created: 2014-04-25 12:58:10 removed: NULL timezone: NULL registration_token: NULL is_registered: 0 *************************** 2. row *************************** id: 2 uuid: 09e32a37-0c88-4009-9deb 相关推荐
电脑软件热门排行今日推荐热门手游 |