How to dump a PAS MySQL Database

Products

• VMware Tanzu SQL

Issue/Introduction

This article describes how to dump a PAS MySQL database.

Environment

Resolution

There are two different procedures depending on whether Allow Remote Admin Access is selected in PAS tile Settings>Internal MySQL.

Procedure 1 

This procedure works regardless of whether Allow Remote Admin Access is selected.

1. To login into the the Ops Manager VM, refer to the following link for instructions to use bosh ssh to log in to the VMs in your deployment with SSH: https://docs.pivotal.io/pivotalcf/2-4/customizing/trouble-advanced.html#bosh-ssh
2. Become root (to avoid Permission denied in find): mysql:~$ sudo -i
3. Find mysqldump: mysql:~# find / -type f -name mysqldump
4. /var/vcap/data/packages/pxc/<id 1>/bin/mysqldump
5. /var/vcap/data/packages/pxc/<id 2>/bin/mysqldump
6. Use mysqldump to dump table: mysql:~# /var/vcap/data/packages/pxc/<id 1>/bin/mysqldump --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf <table> > /tmp/<filename>.sql
7. mysql:~# exit
8. mysql:~$ exit
9. Copy SQL file to Ops Manager: opsman $ bosh -e <env> d <cf deploy> scp mysql/0:/tmp/<filename>.sql.

Procedure 2

This doesn't work if Allow Remote Admin Access is disabled.

1. To login into the the Ops Manager VM, refer to the following link for instructions to use bosh ssh to log in to the VMs in your deployment with SSH: https://docs.pivotal.io/pivotalcf/2-4/customizing/trouble-advanced.html#bosh-ssh
2. Grab the MySQL server credentials (this is one way): cat /var/vcap/jobs/pxc-mysql/config/mylogin.cnf
3. mysql:~# exit
4. mysql:~$ exit
5. Use mysqldump to dump table: opsman $ mysqldump -h <mysql/0 vm ip> -uroot -p <database> > <filename>.sql
6. Enter your password from the credentials that you grabbed.