How to get the Root Cause when cfops MySQL Backup Fails with Status 2

Products

VMware Tanzu Application Service for VMs

Issue/Introduction

This article provides ways to get more information when you try to backup the MySQL tile using cfops tool and fails with status 2.

While trying to backup the MySQL with the sample command below, users get the error:

$ ./cfops backup --opsmanagerhost XX.XXX.XX.XX --adminuser admin --adminpass xxxxxxx --opsmanageruser xxxxx --opsmanagerpass xxxxxxx -d /CloudFoundry_backup --tile mysql-tile 
2017/01/09 15:05:13 [DEBUG] plugin: plugin address: unix /tmp/plugin692586449 
2017/01/09 15:05:27 E0109 15:05:27.740826 28702 createCliCommand.go:49] there was an error: Process exited with status 2 running backup on mysql-tile tile:tile

Environment

Resolution

1. Check the exact command that cfops is trying to execute by running backup but with log level set as debug

$ LOG_LEVEL=debug ./cfops backup

It will give off sample command below

2017/01/09 22:57:00 D0109 22:57:00.546866 26325 main.go:75] Starting mysql dump
2017/01/09 22:57:13 D0109 22:57:13.365917 26325 mysqldump.go:68] mysqldump Dump called:  /var/vcap/packages/mariadb/bin/mysqldump -u root -h localhost --password=a9e8c49c448ee4dc5d05 --all-databases Process exited with status 2
2017/01/09 22:57:13 D0109 22:57:13.365971 26325 main.go:77] Dump finished Process exited with status 2
2017/01/09 22:57:13 D0109 22:57:13.365993 26325 main.go:82] Finished backup of mysql-tile Process exited with status 2
2017/01/09 22:57:13 D0109 22:57:13.397896 26309 backuprestore_rpc.go:14] done calling plugin.backup:  Process exited with status 2
2017/01/09 22:57:13 E0109 22:57:13.397979 26309 createCliCommand.go:49] there was an error: Process exited with status 2 running backup on mysql-tile tile:tile

2. If the debug logs do not state the details of the error, try manually backing up using steps here. Please note that the results may vary and this is only an example. You can attach the result of the manual backup when you open a support ticket or you can work with your MySQL Database Administrator.

# /var/vcap/data/packages/mariadb/xxxxxxxxxxxx/bin/mysqldump -u root -p -h 10.135.32.197 --all-databases > user_databases.sql

Enter password:

mysqldump: Got error: 1449: "The user specified as a definer ('zvtP4cwZkXpaKcV7'@'%') does not exist" when using LOCK TABLES

Additional Information

Error 1449 Explained:

Got error: 1449: "The user specified as a definer ('xxxxxxxxxxx'@'%') does not exist" when using LOCK TABLES

This means that root user does not have access to all databases. To resolve this error:

Run ssh or bosh ssh into the MySQL node
Issue the command mysql -u root -p -h localhost
Run the following to show privileges: show grants for 'root'@'localhost';
Issue the following commands

$ GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'xxx' WITH GRANT OPTION;
$ GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION;
$ FLUSH PRIVILEGES;

If you want further info on those commands, please refer to MySQL documentation or consult with your MySQL Database Admin.