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')) );