How to export a Database Dump of the App Usage Service Database

Products

• VMware Tanzu Platform - Cloud Foundry

Issue/Introduction

When investigating issues with the App Usage Service in VMware Tanzu Application Service (TAS) / Elastic Application Runtime (EAR), Support may request a database dump of the app_usage_service database.

This article provides a step-by-step procedure to safely export the MySQL database dump without locking tables, ensuring minimal disruption to active production workloads.

 

Prerequisites:

• Access to the BOSH Director CLI targeting the deployment.
• Admin or root privileges on the targeted MySQL VM.
• Sufficient disk space in /tmp (or an alternative target directory) to store the database dump.

Resolution

Step 1: Access the MySQL VM

Use BOSH to SSH into the primary MySQL node (mysql/0) of your Cloud Foundry deployment:

$ bosh -d <cf-GUID> ssh mysql/0

Once logged in, escalate to root privileges:

$ sudo -i

 

Step 2: Locate the MySQL Binary Path

Depending on your tile/deployment version, the path to the installed MySQL binaries may vary. Search for the mysqldump executable:

# find / -name mysqldump -print

Example:

/var/vcap/data/packages/percona-xtradb-cluster-8.4/01a2df########/bin/mysqldump
/var/vcap/data/packages/percona-xtradb-cluster-8.0/b29854########/bin/mysqldump

Note: Note the directory path returned (e.g., /var/vcap/data/packages/percona-xtradb-cluster-8.4/01a2df########/bin/). You will use this path for the remaining commands.

 

Step 3: Verify the Database Connection (Optional)

Confirm that you can connect to MySQL and that the app_usage_service database is present:

# /var/vcap/data/packages/<pxc-package-path>/bin/mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf -e "SHOW DATABASES;"

Example:

# /var/vcap/data/packages/percona-xtradb-cluster-8.4/01a2df########/bin/mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf -e "SHOW DATABASES;"
+---------------------+
| Database            |
+---------------------+
| app_usage_service   |
| autoscale           |
| blockstorage        |
| ccdb                |
| credhub             |
| diego               |
| information_schema  |
| locket              |
| metrics_db          |
| monitor             |
| mysql               |
| networkpolicyserver |
| notifications       |
| performance_schema  |
| routing             |
| silk                |
| sys                 |
| uaa                 |
+---------------------+

 

Step 4: Generate the Database Dump

Run mysqldump using the deployment's default login credentials.

Important: Always include the --single-transaction flag. This flag executes a START TRANSACTION SQL statement before dumping data, allowing InnoDB tables to be read in a consistent state without locking the tables during the operation—which is crucial for large databases.

# /var/vcap/data/packages/<pxc-package-path>/bin/mysqldump  --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf   app_usage_service  --single-transaction > /tmp/usage_service_dump.sql

Example:

# /var/vcap/data/packages/percona-xtradb-cluster-8.4/01a2df########/bin/mysqldump --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf app_usage_service --single-transaction > /tmp/usage_service_dump.sql

 

Step 5: Verify the Dump File

Confirm that the SQL dump file was generated successfully and check its size:

# ls -lh /tmp/usage_service_dump.sql