How to change the passwords in MySQL DB Users table after resetting the root password in safe mode

[#1]

We just did a reset to the root user password for mysql using the mysqld_safe method. After resetting the password now we are unable to login to the ambari UI. Kindly, let us know the procedure to password reset for ambari admin,hive, and other components in mysql users table.

[#2]

@Phani Kumar Yadavilli

Can you first logon to Mysql and check if the databases hive ,oozie etc were not deleted

mysql -u root -p{rootpassword}

check the db's

show databases;

Then alter the desired users' password

SET PASSWORD FOR 'hive'@'localhost' = PASSWORD('hive');

Retest

[#3]

Hi Geoffrey, thank you for the quick response. Will these steps work for a cluster setup. I mean, I am not using VM. HDP is installed in the dev cluster.

[#4]

@Phani Kumar Yadavilli

I assume you are NOT talking about the service accounts but MySQL accounts related to the databases that are used by hive,oozie,ranger etc these components are independent of the usual hadoop users like yarn,hdfs,oozie,ambari-qa etc. If that is the case you can use the root option to become any user see below

# su - hdfs 
[hdfs@hdp01~]$ id 
uid=518(hdfs) gid=504(hadoop) groups=504(hadoop),506(hdfs)

Please let me now it that answers your question or else I can elaborate.

[#5]

@Phani Kumar Yadavilli

Ambari Database password can be seen in the file "/etc/ambari-server/conf/password.dat" (by default it is : bigdata). Please take a look at ambari.properties.

# cat /etc/ambari-server/conf/ambari.properties  | grep jdbc
custom.mysql.jdbc.name=mysql-connector-java.jar
server.jdbc.connection-pool=internal
server.jdbc.database=postgres
server.jdbc.database_name=ambari
server.jdbc.postgres.schema=ambari
server.jdbc.user.name=ambari
server.jdbc.user.passwd=/etc/ambari-server/conf/password.dat   

.

If you are able to login to ambari Database like following then you should be able to reset Ambari Admin UI password to default to username = admin and password = admin using the following command:

# mysql -u ambari -pbigdata ambari
mysql> update ambari.users set 
user_password='538916f8943ec225d97a9a86a2c6ec0818c1cd400e09e03b660fdaaec4af29ddbb6f2b1033b81b00' where user_name='admin';

Then restart the ambari server

# ambari-server stop
# ambari-server start

Once you are able to login to ambari UI then you should eb able to reset the Hive Metastore MySQL db password as well as mentioned in the screenshot:

.

[#6]

@Phani Kumar Yadavilli

Ambari Database password can be seen in the file "/etc/ambari-server/conf/password.dat" (by default it is : bigdata). Please take a look at ambari.properties.

# cat /etc/ambari-server/conf/ambari.properties  | grep jdbc
custom.mysql.jdbc.name=mysql-connector-java.jar
server.jdbc.connection-pool=internal
server.jdbc.database=postgres
server.jdbc.database_name=ambari
server.jdbc.postgres.schema=ambari
server.jdbc.user.name=ambari
server.jdbc.user.passwd=/etc/ambari-server/conf/password.dat   

.

If you are able to login to ambari Database like following then you should be able to reset Ambari Admin UI password to default to username = admin and password = admin using the following command:

# mysql -u ambari -pbigdata ambari
mysql> update ambari.users set 
user_password='538916f8943ec225d97a9a86a2c6ec0818c1cd400e09e03b660fdaaec4af29ddbb6f2b1033b81b00' where user_name='admin';

Then restart the ambari server

# ambari-server stop
# ambari-server start

Once you are able to login to ambari UI then you should eb able to reset the Hive Metastore MySQL db password as well as mentioned in the screenshot:

.