How to backup and restore app_usage_service database in TAS (Tanzu Application Service)

Products

• VMware Tanzu Platform - Cloud Foundry

Issue/Introduction

Users have a need to backup the app_usage_service database, especially sometimes there is a need to do so before purging the historical data. 

This article explains how to backup and restore the app_usage_service database. 

Resolution

Backup the database:

1. Follow the steps in Backing up your Tanzu Operations Manager deployments with BBR for the most detailed instruction. The following command would need to be performed to generate backup files for TAS instances.

bbr deployment \
--target BOSH-DIRECTOR-IP \
--username BOSH-CLIENT \
--password BOSH-PASSWORD \
--deployment DEPLOYMENT-NAME \
--ca-cert PATH-TO-BOSH-SERVER-CERTIFICATE \
backup

2. Once above command completes, a folder with multiple tar files will be generated. 

$ ls -l cf-abc123_xyz456
total 6927808
-rw-rw-r-- 1 ubuntu ubuntu      10240 Sep 12 04:38 backup_restore-0-backup-restore-notifications.tar
-rw-rw-r-- 1 ubuntu ubuntu      20480 Sep 12 04:38 backup_restore-0-backup-restore-pcf-autoscaling.tar
-rw-rw-r-- 1 ubuntu ubuntu    1249280 Sep 12 04:38 backup_restore-0-bbr-cfnetworkingdb.tar
-rw-rw-r-- 1 ubuntu ubuntu   45496320 Sep 12 04:38 backup_restore-0-bbr-cloudcontrollerdb.tar
-rw-rw-r-- 1 ubuntu ubuntu      30720 Sep 12 04:38 backup_restore-0-bbr-credhubdb.tar
-rw-rw-r-- 1 ubuntu ubuntu      10240 Sep 12 04:38 backup_restore-0-bbr-routingdb.tar
-rw-rw-r-- 1 ubuntu ubuntu     102400 Sep 12 04:38 backup_restore-0-bbr-uaadb.tar
-rw-rw-r-- 1 ubuntu ubuntu   14172160 Sep 12 04:38 backup_restore-0-bbr-usage-servicedb.tar
-rw-rw-r-- 1 ubuntu ubuntu      11477 Sep 12 04:42 metadata
-rw-rw-r-- 1 ubuntu ubuntu 7032954880 Sep 12 04:39 nfs_server-0-blobstore.tar

3. Uncompress the backup_restore-0-bbr-usage-servicedb.tar file and then you will see a file named usage-service-backup being generated.

Recover the database:

4. Connect to the mysql server, e.g,. 

mysql -h localhost -P 3306 -u root -p

5. Create a database named app_usage_service and use it.

mysql> create database app_usage_service;
Query OK, 1 row affected (0.00 sec)

mysql> use app_usage_service;
Database changed

6. Use the file generated in the step#3 to recover the database data.

mysql> source PATH_TO_THE_FILE/usage-service-backup;

7. Confirm the tables and data were recovered.

mysql> show tables;
+--------------------------------------+
| Tables_in_app_usage_service          |
+--------------------------------------+
| app_events                           |
| app_events_fetcher_job_run_logs      |
                ......
| task_usage_rollover_job_run_logs     |
| worker_check_ins                     |
+--------------------------------------+
29 rows in set (0.00 sec)