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