How to change the MySQL 'sa' user and 'root' user passwords.

Document ID : KB000018989
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

This knowledge document explains about the steps how to change the MySQL database 'sa' user and 'root' user passwords.

Solution:

Description:

This knowledge document explains about the steps how to change the MySQL database 'sa' user and 'root' user passwords.

Solution:

  1. On the CA Business Intelligence (CABI) server, stop the Tomcat service and the CABI SIA service via the Central Configuration Manager (CCM). After that, stop the MySQL Service via the Windows Control Panel (Control Panel -> Services -> BOE120MySQL service)

  2. Open the command prompt. Change to the <MySQL Installed folder>\bin directory. By default this is located at C:\Program Files (x86)\CA\SC\CommonReporting3\MySQL5\bin

  3. Run the following command

    mysqld-nt.exe --skip-grant-tables

    After executing the command, the prompt will not be returned. It is possible to check whether the mysqld-nt.exe is running or not via the Process tab within Windows Task Manager.

    Figure 1

  4. Open another command prompt and change to the <MySQL Installed folder>\bin directory. By default this is located at C:\Program Files (x86)\CA\SC\CommonReporting3\MySQL5\bin

  5. Run "mysql.exe".

  6. The command prompt "mysql" will be returned without demanding a password as follows

    Figure 2

  7. Run the following command

    UPDATE mysql.user SET Password=PASSWORD('newpasswordhere') WHERE User='root';
    FLUSH PRIVILEGES;

    In this example, the password of the MySQL root user is being set to "newpasswordhere1" and the password of the MySQL sa user password is being set to "newpasswordhere2".

    Figure 3

    After completing the command, type "quit" to logout of the MySQL.

  8. Kill any existing "mysqld-nt.exe" processes via the Process tab within Windows Task Manager.

  9. Restart the MySQL Service stopped in step #1 via the Windows Control Panel.

    NOTE: It is necessary to follow the below steps when the password of the MySQL sa user has changed. If the password of the MySQL root user changed but not the MySQL sa user, it is NOT necessary to follow the following steps.

Change the Settings of the CABI CMS System Database

  1. Via the Windows ODBC Control Panel, check the MySQL connection after changing the password of the MySQL sa user

    Figure 4

    Figure 5

  2. Open the properties of the "Server Intelligence Agent(SIA) service from the CCM.

  3. Click the Configuration tab.

  4. If any message boxes similar to "Failed to Retrieve cluster name from Database" appears, click OK.

  5. Click the "Specify..." button on the CMS System Database Configuration section.

    Figure 6

  6. Select 'Update Data Source Settings' and click OK.

    Figure 7

  7. Select 'MySQL driver' and click OK.

    Figure 8

  8. Input the information for the MySQL database and input the new password of the MySQL sa user.

    Figure 9

  9. Return to the properties dialog and click OK.

  10. Restart the MySQL Service via the Windows Control Panel. In addition, restart the CABI Tomcat service and the SIA service via the CCM.