The following databases are no longer used:
I can just drop them myself; this ticket is for the database people to enumerate other cleanup steps that should accompany the drops.
| Subject | Repo | Branch | Lines +/- | |
|---|---|---|---|---|
| production-m5: Remove unused grants. | operations/puppet | production | +0 -75 |
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Resolved | Andrew | T242455 Investigate options to improve CloudVPS backend database architecture | |||
| Resolved | Marostegui | T261152 Drop openstack databases from m5-master |
Before we drop things I'd appreciate having a DBA confirm that there's no longer any activity to any of those. I've checked myself but it's nice to have a second opinion before deleting things.
Thanks Andrew for cleaning this up.
Some of those databases are empty. The other ones, indeed are showing no activity for around a month:
root@db1133:/home/marostegui# for i in `cat databases_to_drop`; do echo $i; ls -lhtr /srv/sqldata/$i | grep ibd | tail -n5 ; done designate designate_old -rw-rw---- 1 mysql mysql 240K Jul 10 02:02 zone_transfer_accepts.ibd -rw-rw---- 1 mysql mysql 112K Jul 13 23:03 service_statuses.ibd -rw-rw---- 1 mysql mysql 528K Jul 13 23:09 zones.ibd -rw-rw---- 1 mysql mysql 28M Jul 13 23:11 records.ibd -rw-rw---- 1 mysql mysql 30M Jul 13 23:11 recordsets.ibd glance glance_old -rw-rw---- 1 mysql mysql 112K Mar 26 19:21 metadef_namespace_resource_types.ibd -rw-rw---- 1 mysql mysql 128K Mar 26 20:26 image_tags.ibd -rw-rw---- 1 mysql mysql 96K Apr 16 18:13 alembic_version.ibd -rw-rw---- 1 mysql mysql 560K May 20 05:09 image_locations.ibd -rw-rw---- 1 mysql mysql 8.0M May 20 05:10 images.ibd keystone keystone_old -rw-rw---- 1 mysql mysql 176K May 8 20:40 revocation_event.ibd -rw-rw---- 1 mysql mysql 192K Jul 4 05:11 user.ibd -rw-rw---- 1 mysql mysql 128K Jul 4 05:11 nonlocal_user.ibd -rw-rw---- 1 mysql mysql 128K Jul 10 00:27 project.ibd -rw-rw---- 1 mysql mysql 12M Jul 13 06:29 assignment.ibd neutron neutron_old -rw-rw---- 1 mysql mysql 10M Jul 13 23:08 securitygrouprules.ibd -rw-rw---- 1 mysql mysql 496K Jul 13 23:09 standardattributes.ibd -rw-rw---- 1 mysql mysql 640K Jul 13 23:09 securitygroupportbindings.ibd -rw-rw---- 1 mysql mysql 416K Jul 13 23:09 ml2_port_binding_levels.ibd -rw-rw---- 1 mysql mysql 8.0M Jul 13 23:09 ipallocations.ibd nova_api_eqiad1 nova_api_eqiad1_old -rw-rw---- 1 mysql mysql 208K Jul 10 20:27 resource_providers.ibd -rw-rw---- 1 mysql mysql 304M Jul 10 21:20 request_specs.ibd -rw-rw---- 1 mysql mysql 40M Jul 10 21:20 instance_mappings.ibd -rw-rw---- 1 mysql mysql 14M Jul 10 21:20 consumers.ibd -rw-rw---- 1 mysql mysql 832K Jul 10 21:20 allocations.ibd nova_cell0_eqiad1 nova_cell0_eqiad1_old -rw-rw---- 1 mysql mysql 192K Jul 8 23:30 block_device_mapping.ibd -rw-rw---- 1 mysql mysql 400K Jul 9 00:20 instance_faults.ibd -rw-rw---- 1 mysql mysql 208K Jul 9 00:31 instance_extra.ibd -rw-rw---- 1 mysql mysql 240K Jul 9 00:33 instance_system_metadata.ibd -rw-rw---- 1 mysql mysql 10M Jul 9 00:34 instances.ibd nova_eqiad1 nova_eqiad1_old -rw-rw---- 1 mysql mysql 29M Jul 10 21:20 instance_info_caches.ibd -rw-rw---- 1 mysql mysql 136M Jul 10 21:20 instance_extra.ibd -rw-rw---- 1 mysql mysql 92M Jul 10 21:20 instance_actions.ibd -rw-rw---- 1 mysql mysql 80M Jul 10 21:20 block_device_mapping.ibd -rw-rw---- 1 mysql mysql 28M Jul 10 21:20 virtual_interfaces.ibd
Once you are ready to drop them, I would recommend to use:
drop database if exists
We should also clean up the grants (I can take care of that)
I will remove now the backup grants from the multiinstances hosts. FYI, the following backups will continue running:
labsdbaccounts labswiki nodepooldb nova striker
Mentioned in SAL (#wikimedia-operations) [2020-08-31T13:41:53Z] <andrewbogott> dropping many databases from m5, as per T261152
All these must go - can you confirm @Andrew ?
+-----------+--------------------------------+ | user | host | +-----------+--------------------------------+ | glance | % | | keystone | % | | neutron | % | | nova | 10.64.20.13 | | nova | 10.64.20.25 | | designate | 10.64.32.180 | | designate | 10.64.48.43 | | glance | 127.0.0.1 | | keystone | 127.0.0.1 | | nova | 127.0.0.1 | | designate | 208.80.154.11 | | designate | 208.80.154.12 | | nova | 208.80.154.132 | | designate | 208.80.154.135 | | nova | 208.80.154.23 | | nova | 208.80.154.85 | | nova | 208.80.154.92 | | designate | 208.80.155.117 | | designate | 2620:0:861:1:208:80:154:11 | | designate | 2620:0:861:2:208:80:154:135 | | neutron | cloudcontrol1003.wikimedia.org | | neutron | cloudcontrol1004.wikimedia.org | | nova | labnet1001.eqiad.wmnet | | nova | labnet1002.eqiad.wmnet | | glance | localhost | | keystone | localhost | | neutron | localhost | | nova | localhost | +-----------+--------------------------------+ 28 rows in set (0.001 sec)
Change 623681 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] production-m5: Remove unused grants.
Mentioned in SAL (#wikimedia-operations) [2020-09-02T07:05:17Z] <marostegui> Drop unused grants on m5 T261152
This has been executed:
drop user if exists 'glance'@'%'; drop user if exists 'keystone'@'%'; drop user if exists 'neutron'@'%'; drop user if exists 'nova'@'10.64.20.13'; drop user if exists 'nova'@'10.64.20.25'; drop user if exists 'designate'@'10.64.32.180'; drop user if exists 'designate'@'10.64.48.43'; drop user if exists 'glance'@'127.0.0.1'; drop user if exists 'keystone'@'127.0.0.1'; drop user if exists 'nova'@'127.0.0.1'; drop user if exists 'designate'@'208.80.154.11'; drop user if exists 'designate'@'208.80.154.12'; drop user if exists 'nova'@'208.80.154.132'; drop user if exists 'designate'@'208.80.154.135'; drop user if exists 'nova'@'208.80.154.23'; drop user if exists 'nova'@'208.80.154.85'; drop user if exists 'nova'@'208.80.154.92'; drop user if exists 'designate'@'208.80.155.117'; drop user if exists 'designate'@'2620:0:861:1:208:80:154:11'; drop user if exists 'designate'@'2620:0:861:2:208:80:154:135'; drop user if exists 'neutron'@'cloudcontrol1003.wikimedia.org'; drop user if exists 'neutron'@'cloudcontrol1004.wikimedia.org'; drop user if exists 'nova'@'labnet1001.eqiad.wmnet'; drop user if exists 'nova'@'labnet1002.eqiad.wmnet'; drop user if exists 'glance'@'localhost'; drop user if exists 'keystone'@'localhost'; drop user if exists 'neutron'@'localhost'; drop user if exists 'nova'@'localhost';
I have a backup of the grants in case we need to revert for any reason.
Change 623681 merged by Marostegui:
[operations/puppet@production] production-m5: Remove unused grants.