Change External Database Hostname

[#1]

Greetings,

We are using Cloudera Director, with databases stored on AWS RDS.

Director was using the IP address of the RDS database, as opposed to the hostname. I recently enabled Multi-AZ on the RDS database, and now it has multiple IP addresses.

How can I update the config for the external database in Director so that new clusters are created against the RDS database's hostname instead of the IP address?

I tried the unsupported database update mechanism via the director API at `PUT /api/v12/environments/{environment}/databaseServers/{externalDatabaseServer}` (filled in obviously) with a bunch of different json body contents. However, director just responds with a `UnsupportedExternalDatabaseServerUpdateException` regardless of the format I give.  I've tried enabling debug logging to see if there is additional information in application.log, but I can't seem to find the good stuff.

I tried opening director's database, but the tables containing database config information are encrypted in-row (gibberish in the cells).

I'm hoping someone can send a sample API call that could take an existing external mysql database, and just pop in a new hostname. My alternative seems to be re-creating my entire manager + clusters from scratch with a new database template. This will bring a lot of sadness.

It's director 2.8.1.

[#2]

Hello! Unfortunately, there's no way to do this in Director as you're suggesting. This API call is a placeholder, and doesn't support any actual updates.

Do you have the option of engaging support for this? That would be my recommended path forward.

It is possible to remove the encryption from Director's database (https://www.cloudera.com/documentation/director/2-8-x/topics/director_db_encryption.html), but any sort of DB editing is entirely unsupported.

[#3]

@Mike Wilson

I know the above is unsupported, but may I ask -- are there any known-issues which make using the "refreshExternalDatabaseServersOnStart" config setting a Bad Thing? Seems to have saved my bacon.

[#4]

UPDATE -- See my new post below. This post is incomplete.

______

To anyone else that is stuck:

1. systemctl stop cloudera-director-server

2. mysql -h <rds_endpoint_of_director_db> --ssl-ca=/etc/cloudera-director-server/rds-combined-ca-bundle.pem --ssl-verify-server-cert -u director -p

3. use director;
   UPDATE SERVER_CONFIGS SET CONFIG_VALUE = 'true' WHERE CONFIG_KEY = 'refreshExternalDatabaseServersOnStart';

4. systemctl start cloudera-director-server; tail -f /var/log/cloudera-director-server/application.log

This seems to have worked.

[#5 · Accepted solution]

Quick update -- there's actually more to do.

THIS IS ALL UNSUPPORTED AND AT YOUR OWN RISK. BE WARNED!

Disable DB Encryption

1. Stop cloudera-director-server.
   

   systemctl stop cloudera-director-server

2. Disable database encryption with these settings in /etc/cloudera-director-server/application.properties.  This assumes a default install, with default keys.
   

   lp.encryption.twoWayCipher: transitional
   lp.encryption.twoWayCipherConfig: desede;ZGVmYXVsdGRpcmVjdG9yZGVzZWRla2V5|passthrough;

3. Start cloudera-director-server and wait a few minutes so that the database decrypts.
   

   systemctl start cloudera-director-server

4. Log in to the director database.
   

   mysql -h  --ssl-ca=/etc/cloudera-director-server/rds-combined-ca-bundle.pem --ssl-verify-server-cert -u director -p

5. Run the following query to verify that the database is decrypted. You should see plaintext values in the response.
   

   USE director;
   SELECT TEMPLATE FROM EXTERNAL_DATABASE_SERVERS;

Here is an example response (truncated and redacted):

+-------...-----+
| TEMPLATE ...  |
| {"name":"my-manager-db","type":"MYSQL","host":"1.2.3.4","port":null,"adminUser":"my_username","adminPassword":"my_password","config":{"engineVersion":"5.6.37","allocatedStorage":"20","multiAZ":"false","vpcSecurityGroupIds":"sg-1234567a","dbSubnetGroupName":"my-rds-subnet-group","instanceClass":"db.m4.large","storageEncrypted":"true"},"tags":{}} |
+-------...-----+

Update the database

Before we actually edit the hostname, we should have Director update the misc database properties.

1. Stop cloudera-director-server.
   

   systemctl stop cloudera-director-server

2. Log in to the director database.
   

   mysql -h  --ssl-ca=/etc/cloudera-director-server/rds-combined-ca-bundle.pem --ssl-verify-server-cert -u director -p

3. Run the following query to enable 'refreshExternalDatabaseServersOnStart'.
   

   USE director;
   UPDATE SERVER_CONFIGS SET CONFIG_VALUE = 'true' WHERE CONFIG_KEY = 'refreshExternalDatabaseServersOnStart';

4. Start cloudera-director-server.
   

   systemctl start cloudera-director-server

5. Login to the director web interface and check the database servers. In a few minutes, you should see updated properties (versions become accurate, etc).
6. Once the databases look updated, stop cloudera-director-server.
   

   systemctl stop cloudera-director-server

7. Run the following query to disable 'refreshExternalDatabaseServersOnStart'. Otherwise, director will overwrite the new hostname with the old IP address on every startup.
   

   USE director;
   UPDATE SERVER_CONFIGS SET CONFIG_VALUE = 'false' WHERE CONFIG_KEY = 'refreshExternalDatabaseServersOnStart';

8. We will need to update both the 'TEMPLATE' and 'EXTERNAL_DATABASE_SERVER' properties. I prefer to work on one field at a time.
   Run the following query gather the current database template properties, which will will be modifying and then updating.
   

   USE director;
   SELECT TEMPLATE FROM EXTERNAL_DATABASE_SERVERS;

9. Carefully copy the output into a text editor, and update the json accordingly.
10. Validate the new property using jq. You should see pretty json output.
    

    echo '{new long json string}' | jq .

11. Run the following query update the database template property. BE WARNED that if you have multiple DB servers listed, it's super important that the WHERE clause be correct or you'll overwrite all the DB's with the new value.
    

    USE director;
    UPDATE EXTERNAL_DATABASE_SERVERS SET TEMPLATE = '{new long json string}' WHERE NAME = 'my-manager-db';

12. Perform the same steps for the EXTERNAL_DATABASE_SERVER field. Example:
    

    SELECT EXTERNAL_DATABASE_SERVER FROM EXTERNAL_DATABASE_SERVERS;

    
    

    <echo '{new long strong}' | jq .

    
    

    UPDATE EXTERNAL_DATABASE_SERVERS SET EXTERNAL_DATABASE_SERVER = '{new long strong}' WHERE NAME = 'my-manager-db';

13. Start cloudera-director-server and hope for the best.

If everything looks good -- stop cloudera server, re-enable encryption, and start cloudera server back up.

[#6]

Wow! I'm impressed. Thank you for the detailed instructions, and good work!

I would like to reiterate that anyone who follows this advice does it at their own risk, just in case you missed jcpuskar's warning . 🙂