Manually Reset MySQL Password

MySQL password reset manually

---

---

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