Create a Separate Database and Store Configs (Recommended)

Pre-upgrade

Before proceeding with the upgrade, ensure to execute the following command:

create database service_conf_bck;
use service_conf_bck;
CREATE TABLE IF NOT EXISTS configs_prior_patch AS
SELECT
    cc.config_id,
    cc.type_name,
    cc.config_data,
    cc.config_attributes,
    service_name,
    sc.version,
    sc.note,
    (SELECT MAX(version) FROM ambari.serviceconfig s
     WHERE s.service_name = sc.service_name) AS max_version
FROM
    ambari.serviceconfig sc
INNER JOIN
    ambari.serviceconfigmapping scm
ON
    sc.service_config_id = scm.service_config_id
INNER JOIN
    ambari.clusterconfig cc
ON
    cc.config_id = scm.config_id
WHERE
    sc.service_name IN (<service-name-1>,<service-name-2>,.....);

Post-upgrade

After completing the upgrade, to restore the configurations for a specific service, execute the following code:

update clusterconfig a set config_data = (select config_data from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM service_conf_bck.configs_prior_patch WHERE service_name = <service-name>) and service_name = <service-name>),
    config_attributes = (select config_attributes from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM serviceconfig WHERE service_name = <service-name>) and service_name = <service-name>)
    where config_id in (select config_id from serviceconfigmapping where service_config_id in (select service_config_id from serviceconfig where service_name = <service-name> and version =
    (select max(version) from serviceconfig where service_name = <service-name>)) );

For example, in the below code we have taken a backup for configurations of NiFi, Tez, and Spark3:

create service_conf_bck;
use service_conf_bck;

CREATE TABLE IF NOT EXISTS configs_prior_patch AS
SELECT
    cc.config_id,
    cc.type_name,
    cc.config_data,
    cc.config_attributes,
    service_name,
    sc.version,
    sc.note,
    (SELECT MAX(version) FROM ambari.serviceconfig s
     WHERE s.service_name = sc.service_name) AS max_version
FROM
    ambari.serviceconfig sc
INNER JOIN
    ambari.serviceconfigmapping scm
ON
    sc.service_config_id = scm.service_config_id
INNER JOIN
    ambari.clusterconfig cc
ON
    cc.config_id = scm.config_id
WHERE
    sc.service_name IN ('NIFI','TEZ','SPARK3');

After the upgrade, to restore NIFI configurations, the following command was run in mysql:

update clusterconfig a set config_data = (select config_data from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM service_conf_bck.configs_prior_patch WHERE service_name = 'NIFI') and service_name = 'NIFI'),
    config_attributes = (select config_attributes from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM serviceconfig WHERE service_name = 'NIFI') and service_name = 'NIFI')
    where config_id in (select config_id from serviceconfigmapping where service_config_id in (select service_config_id from serviceconfig where service_name = 'NIFI' and version =
    (select max(version) from serviceconfig where service_name = 'NIFI')) );