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
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