I’ve forgotten my MySQL root password. How do I get it back?

Getting the MySQL root password back is diffiicult (sha-1 hash).

Resetting the password is a better solution.
The below instructions are known to work with Bright 7.3 and later.

NOTE:  When stopping the database service, whether it is MySQL or MariaDB, any service that relies on the database service must first be stopped (e.g. cmdaemon, slurmdbd, etc.).

NOTE:  On RHEL/Rocky Linux 9 and Ubuntu 18.04 and later versions, a password for the root MySQL/MariaDB user is not required when accessing the database service while logged into the head node as root.

RHEL/CentOS 7 instructions:

To do a reset of the password, stop all applications that use the MySQL service during the reset operation. Stop the mysql service and start it in safe mode during the reset.

The following commands on the head node (tested for CentOS 7) show how such a reset session can be carried out:

# systemctl stop mariadb.service  
# mysqld_safe --skip-grant-tables &
# mysql -u root
MariaDB [(none)]> use mysql;
MariaDB [(none)]> update user set password=PASSWORD("newpass") where
User='root';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> quit
# killall mysqld_safe mysqld
# systemctl start mariadb.service

RHEL/Rocky Linux 8 instructions:

# systemctl stop mariadb.service  
# mysqld_safe --skip-grant-tables &
# mysql -u root
MariaDB [(none)]> use mysql;
MariaDB [(none)]> update user set authentication_string=PASSWORD('newpass') where User='root';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> quit
# killall mysqld_safe mysqld
# systemctl start mariadb.service

Ubuntu 16.04 instructions:

# systemctl stop mysql 
# mkdir /var/run/mysqld 
# chown mysql.mysql /var/run/mysqld 
# mysqld_safe --skip-grant-tables

mysql> flush privileges;
mysql> alter user 'root'@'localhost' identified by 'MyNewPass';
mysql> exit;

Once password has been changed, kill the mysql and mysqld_safe processes and start the mysql service again. 

# pkill mysqld_safe && sleep 10 &&systemctl start mysql