Pivotal Knowledge Base

Follow

How to access or perform a remote backup of PCF MySQL service-instance databases

Environment

PCF MySQL 1.9.0 and above

Purpose

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

Error:

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

Cause

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

Resolution

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.10.10.5",
"jdbcUrl": "jdbc:mysql://10.10.10.5:3306/cf_e2d148a8_1baa_4961_b314_2431f57037e5?user=UfSVqCTC5iwI3iuE\u0026password=xFneIRH3f4ow2idn",
"name": "cf_e2d148a8_1baa_4961_b314_2431f57037e5",
"password": "xFneIRH3f4ow2idn",
"port": 3306,
"uri": "mysql://UfSVqCTC5iwI3iuE:xFneIRH3f4ow2idn@10.10.10.5:3306/cf_e2d148a8_1baa_4961_b314_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.10.10.8 being the MySQL tile proxy IP address:
$ mysql -u UfSVqCTC5iwI3iuE -pxFneIRH3f4ow2idn -h 10.10.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.10.10.8 --single-transaction --skip-add-locks > spring-db-backup.sql

 

Comments

Powered by Zendesk