Broadcom - Gateway 10.x Appliance : How to reset Database Root password in MySQL 8.x

Products

• CA API Gateway

Issue/Introduction

Unable to access MySQL console as ROOT due to lost and/or unknown password. 

Attempted to reset the password by following the below KB article, however, it appears not working with MySQL 8.x version.

https://knowledge.broadcom.com/external/article/92946/how-to-reset-mysql-internal-root-passwor.html

Environment

Release : 10.0

Component :

Cause

The procedure to reset DB Root password is slightly different in MySQL 8.x from what used to be in MySQL 5.x.

Resolution

Start MySQL service with the --skip-grant-tables option and alter the ROOT password with the following steps:

• First, make sure that the MySQL service is stopped.

# systemctl stop mysqld.service   

• Then start MySQL service with the following options appended into the command line:

# mysqld --skip-grant-tables --user=mysql &

• At this stage, you should be able to connect MySQL console (without entering credentials) by simply running:

# mysql

• When MySQL service is started with --skip-grant-tables option, account-management is disabled and we must reload the grants in order to alter the password. In order to do so, reload users privileges:

# FLUSH PRIVILEGES;

• At this stage, we can use the following query to update the ROOT password. Replace “new_password” with the actual password you desire to use

# ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

• Stop MySQL service and start it back as in normal circumstances:

Need to kill mysql process it was not started as a service 

# cd /var/run/mysqld

# kill `cat  /var/run/mysqld/mysqld.pid`

 

# systemctl start mysqld.service   

 

Additional Information

Please, do not forget to update /root/.my.cnf file with the new password if you store it under the OS user profile. 

Also after this command make sure mysql stopped 

# systemctl stop mysqld.service   

If not kill is 

# ps -ef | grep mysql

mysql    21710 21649  0 11:02 pts/0    00:00:03 mysqld --skip-grant-tables --user=mysql

Then # systemctl start mysqld.service