What is the safest way to migrate Ambari from PostGreSQL to MySQL?

[#1]

We have a few clusters that were installed using Ambari running with a PostGreSQL back-end (the default) but need to standardise on a DBA-managed MySQL set-up. What is the recommended method to convert Ambari to use MySQL?

The clusters are Kerberised and all components are running in HA mode.

[#2 · Accepted solution]

@Nik Lam

Following two articles will give little more detailed steps to accomplish the same task.

[1] MySQL setup for Ambari

https://docs.hortonworks.com/HDPDocuments/Ambari-2.2.2.0/bk_ambari_reference_guide/content/_using_am...

[2] How do I change an existing Ambari DB from Postgres to MySQL?

http://www.hadoopadmin.co.in/bigdata/how-do-i-change-an-existing-ambari-db-postgres-to-mysql/

Nothing much from Ambari Side however there are some tools available like following which might help in dealing with some of the special cases like "\N" (NULL) replacement etc.

https://dbconvert.com/postgresql/mysql/

.

[#3]

Thanks @Jay SenSharma. Basically, there is no official process for doing this, and the best thing would have been to ensure you do not ever choose the wrong database technology when you are first installing Ambari!

If you can get an experienced DBA to carry this out, that would be the safest way to approach it.

The conversion process documented hadoopadmin.co.in is useful for a high-level procedure, however with Ambari 2.2.2 I found there are a significant number of changes that needed to be made to the SQL source before it was compatible with MySQL, including:

• Commenting out SET statements at the top of the dump file.
• Changing the case of some table names - e.g. qrtz_blob_triggers in PostGreSQL becomes QRTZ_BLOB_TRIGGERS in MySQL
• Pre-creating some tables that are created during Ambari run-time with names such as ds_jobimpl_6

Definitely worth having a test environment for this operation if you cannot afford to lose your cluster and all the data in it. If you're not deeply familiar with PostGreSQL/MySQL SQL differences then the conversion tool suggestion is a good one.