How to access or perform a remote backup of PCF MySQL Service-Instance Databases

Products

• VMware Tanzu Application Service for VMs

Issue/Introduction

This article explains the process to access or perform the backup of a PCF MySQL service-instance database explicitly.

Error

Cannot access or perform backups of MySQL service-instance databases remotely.

Environment

Cause

Starting PCF MySQL 1.9, the user admin remote access to MySQL databases is disabled by default.

Resolution

In order to perform a full automated backup of the MySQL databases, enable Automated Backups for MySQL tile.

To access or perform remote backups of PCF MySQL databases, enable remote access for user admin. See MySQL Server Configuration document for details.

With remote admin user disabled, to access or perform explicit backups of MySQL service instance backups, use the service-keys to generate remote users. Follow these steps to generate service-keys for each MySQL instance:

• Login to the cf CLI to the foundation API and target the Org/Space where the desired service instances is present.
• Create a service-key for the desired MySQL service instance:

$ cf create-service-key <service-instance-name> <any-key-name>

For example:

$ cf create-service-key mysql-spring spring-key
Creating service key spring-key for service instance mysql-spring as admin...
OK

• The username and password can be found in the above service-key details:

$ cf service-key <service-instance-name> <any-key-name>

For example:

$ cf service-key mysql-spring spring-key
Getting key spring-key for service instance mysql-spring as admin...

{
 "hostname": "10.##.##.5",
 "jdbcUrl": "jdbc:mysql://10.##.##.5:3306/cf_e2d148a8_####_####_####_2431f57037e5?user=UfSVqCTC5iwI3iuE\u0026password=xFne#######idn",
 "name": "cf_e2d148a8_####_####_####_2431f57037e5",
 "password": "xFneIRH3f4ow2idn",
 "port": 3306,
 "uri": "mysql://UfSV#####iwI3iuE:xFne###[email protected]:3306/cf_e2d148a8_1baa_####_####_2431f57037e5?reconnect=true",
 "username": "UfSVqCTC5iwI3iuE"
}

• The above example is connecting to the service instance mysql-spring. We created a service-key spring-key with username and password displayed in the details above. The hostname above is the MySQL tile broker IP Address.
• The above username and password can be used to access or perform backups of the service-instance database.
• In this example, mysql-spring service instance has database named

cf_e2d148a8_1baa_4961_b314_2431f57037e5

• It can be accessed using the mysql command below:

$ mysql -u <username> -p<password> -h <IP_of_mysql_node_or_proxy>

• In this example, 10.##.##.8 being the MySQL tile proxy IP address:

$ mysql -u UfSVqCTC5iwI3iuE -pxFneIRH3f4ow2idn -h 10.##.##.8

• To perform remote backup, mysqldump command is used:

mysqldump -u <username> -p<password> -h <IP_of_mysql_node_or_proxy> --single-transaction --skip-add-locks > <backup-name>.sql

• In this example, 10.10.10.8 being the MySQL tile proxy IP address:

mysqldump -u UfSVqCTC5iwI3iuE -pxFneIRH3f4ow2idn -h10.##.##.8 --single-transaction --skip-add-locks > spring-db-backup.sql