Manually Reset MySQL Password

MySQL password reset manually


   KB 24485        Last updated on 2024-01-10  




0.00
(0 votes)

Problem

MySQL, database connection failure due to invalid credentials. User Zimbra not able to authenticate database connection.

Error:
Log file:/opt/zimbra/log/mailbox.log
WARN  [main] [] misc - Could not establish a connection to the database.  Retrying in 5 seconds.
com.zimbra.common.service.ServiceException: system failure: getting database connection
.
.
Caused by: java.sql.SQLInvalidAuthorizationSpecException: Access denied for user 'zimbra'@'localhost' (using password: YES)

Solution

Reset MySQL password by manually updating MySQL configuration file.

Step 1:

  • Stop the mysql service. [Run as Zimbra] [su - zimbra]
$ mysql.server stop

Step 2:

  • Modify the /opt/zimbra/bin/mysql.server to start the MySQL with skipping the grant tables (Skipping authentication). [Run as ROOT]

From

--ledir=/opt/zimbra/common/sbin < /dev/null > /dev/null 2>&1 &

To

--ledir=/opt/zimbra/common/sbin < /dev/null > /dev/null 2>&1 --skip-grant-tables &
Save and Exit the file.

Step 3:

  • Start the mysql service. [Run as Zimbra] [su - zimbra]
$ mysql.server start

Step 4:

  • Connect to the database and change the password.
$ mysql
mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='zimbra';
mysql> FLUSH PRIVILEGES;
mysql> exit;
Note:
Make sure the 'password' you specify is should be same as in the localconfig. Run following command to check.
zmlocalconfig -s | grep zimbra_mysql_password

Step 5:

  • Go back to the file remove the --skip-grant-tables from /opt/zimbra/bin/mysql.server, and save and exit the file. [Run as root].

step 6:

  • Stop and start the MySQL service. [Run as zimbra] [su - zimbra]
$ mysql.server stop
$ mysql.server start

Step 7:

  • Now MySQL should be connect with user Zimbra. [Run as zimbra] [su - zimbra].
$ mysql

Step 8:

  • Restart mailbox services. [Run as zimbra] [su - zimbra]
zmmailboxdctl stop
zmmailboxdctl start
Submitted by: Raghu Noti
Verified Against: ZCS 8.8.15, ZCS 9.0 Date Created: 2022-11-13
Article ID: https://wiki.zimbra.com/index.php?title=Manually_Reset_MySQL_Password Date Modified: 2024-01-10



Try Zimbra

Try Zimbra Collaboration with a 60-day free trial.
Get it now »

Want to get involved?

You can contribute in the Community, Wiki, Code, or development of Zimlets.
Find out more. »

Looking for a Video?

Visit our YouTube channel to get the latest webinars, technology news, product overviews, and so much more.
Go to the YouTube channel »


Jump to: navigation, search