Difference between revisions of "Issues with mysql and logmysql passwords"

(Adding Template:Article_Footer, and categories)
(For logger database)
 
(14 intermediate revisions by 4 users not shown)
Line 1: Line 1:
If the wiki link below does not work then please follow these steps:
+
{{Archive}}{{Article Infobox|{{admin}}||{{ZCS 5.0}}|}}__TOC__
  
 +
Please follow this wiki if following document does not helps you.
 
http://wiki.zimbra.com/index.php?title=Resetting_LDAP_%26_MySQL_Passwords
 
http://wiki.zimbra.com/index.php?title=Resetting_LDAP_%26_MySQL_Passwords
  
'''Issue:'''
+
====Problem====
 +
Due to wrong passwords in the database of mysql/logmysql not able to connect to the database with the existing password. Need to be update the password manually.
  
Due to wrong passwords in the database mysql/logmysql is not able to connect to the database with the existing password.
+
====Error====
 +
The error might looks like this.
 +
<pre>#logmysql&#10;ERROR 1044: Access denied for user 'zimbra'@'localhost' to database 'zimbra'</pre>
  
We need to change the passwords manually.
+
<pre>#mysql&#10;ERROR 1044: Access denied for user 'zimbra'@'localhost' to database 'zimbra'</pre>
  
'''Error''':
+
==For Mysql Database==
  
    #logmysql
+
1. Stop the mysql service. [Run as Zimbra]
    ERROR 1044: Access denied for user 'zimbra'@'localhost' to database 'zimbra_logger'
 
  
    #mysql
+
  $ mysql.server stop
    ERROR 1044: Access denied for user 'zimbra'@'localhost' to database 'zimbra'
 
  
'''Steps''':
+
2. Modify the /opt/zimbra/bin/mysql.server to start the mysql with skipping the grant tables. [Run as ROOT]
  
1) For Mysql:
+
  # vi /opt/zimbra/bin/mysql.server
 
+
a) Stop the mysql service. [Run as Zimbra: mysql.server stop]
+
Modify the following:
 
 
b) Run as ROOT vi /opt/zimbra/bin/mysql.server
 
 
 
c) Modify the following:
 
 
       --defaults-file=${mysql_mycnf} \
 
       --defaults-file=${mysql_mycnf} \
 
       --external-locking \
 
       --external-locking \
 
       --ledir=${mysql_directory}/libexec < /dev/null > /dev/null 2>&1 &
 
       --ledir=${mysql_directory}/libexec < /dev/null > /dev/null 2>&1 &
 +
 
   TO
 
   TO
 +
 
       --defaults-file=${mysql_mycnf} \
 
       --defaults-file=${mysql_mycnf} \
 
       --external-locking \
 
       --external-locking \
 
       --ledir=${mysql_directory}/libexec < /dev/null > /dev/null 2>&1 --skip-grant-tables &
 
       --ledir=${mysql_directory}/libexec < /dev/null > /dev/null 2>&1 --skip-grant-tables &
 +
 
Save and Exit the file.
 
Save and Exit the file.
d) Start the mysql service. [Run as Zimbra: mysql.server start]
 
  
e) Connect to the database.
+
3. Start the mysql service. [Run as Zimbra]
 +
  $ mysql.server start
 +
 
 +
4. Connect to the database and change the passwords.
 +
 
 
     # mysql
 
     # mysql
 
     Welcome to the MySQL monitor.  Commands end with ; or \g.
 
     Welcome to the MySQL monitor.  Commands end with ; or \g.
Line 44: Line 48:
 
     mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';      ----> Root user.
 
     mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';      ----> Root user.
 
     mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='zimbra';    ----> Zimbra user.
 
     mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='zimbra';    ----> Zimbra user.
Make sure the 'password' you specify is should be same as 
 
zmlocalconfig -s | grep mysql_root_password and zmlocalconfig -s | grep zimbra_mysql_password.
 
    mysql> FLUSH PRIVILEGES;
 
    mysql> exit
 
  
f) Go back to the file remove the --skip-grant-tables and save and exit the file.
+
Make sure the 'password' you specify is should be same as in the localconfig. Run following command to check those passwords.
 +
 
 +
  zmlocalconfig -s | grep mysql_root_password
 +
  zmlocalconfig -s | grep zimbra_mysql_password
 +
 
 +
Flush the privileges and exit.
 +
 
 +
  mysql> FLUSH PRIVILEGES;
 +
  mysql> exit
  
g) Stop and start the mysql service. [mysql.server stop and start]
+
5. Go back to the file remove the --skip-grant-tables from /opt/zimbra/bin/mysql.server and save and exit the file. You will need to do it as root.
  
h) Now try to connect to mysql.
+
6. Stop and start the mysql service. [Run as zimbra]
  
 +
  $ mysql.server stop
 +
  $ mysql.server start
  
 +
7. Now you should be able to connect to the mysql.
  
'''2) For Logmysql:'''
+
  $ mysql zimbra
  
a) Stop the logmysql service. [Run as Zimbra: logmysql.server stop]
+
==For logger database==
  
b) Run as ROOT vi /opt/zimbra/bin/logmysql.server
+
1. Stop the logger mysql service. [Run as Zimbra]
  
c) Modify the following:
+
  $ logmysql.server stop
 +
 
 +
2. Modify the /opt/zimbra/bin/logmysql.server to start the logmysql with skipping the grant tables. [Run as ROOT]
 +
 
 +
  # vi /opt/zimbra/bin/logmysql.server
 +
 
 +
Modify the following:
 
       --defaults-file=${logger_mysql_mycnf} \
 
       --defaults-file=${logger_mysql_mycnf} \
 
       --external-locking \
 
       --external-locking \
 
       --ledir=${logger_mysql_directory}/libexec &
 
       --ledir=${logger_mysql_directory}/libexec &
 +
 
     TO
 
     TO
 +
 
       --defaults-file=${logger_mysql_mycnf} \
 
       --defaults-file=${logger_mysql_mycnf} \
 
       --external-locking \
 
       --external-locking \
 
       --ledir=${logger_mysql_directory}/libexec --skip-grant-tables &
 
       --ledir=${logger_mysql_directory}/libexec --skip-grant-tables &
 +
 
Save and Exit the file.
 
Save and Exit the file.
  
d) Start the logmysql service. [Run as Zimbra: logmysql.server start]
+
3. Start the logmysql service. [Run as Zimbra]
 +
  $ logmysql.server start
 +
 
 +
4. Connect to the database and change the passwords.
  
e) Connect to the database.
 
 
     # logmysql
 
     # logmysql
 
     Welcome to the MySQL monitor.  Commands end with ; or \g.
 
     Welcome to the MySQL monitor.  Commands end with ; or \g.
Line 83: Line 105:
 
     mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';      ----> Root user.
 
     mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';      ----> Root user.
 
     mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='zimbra';    ----> Zimbra user.
 
     mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='zimbra';    ----> Zimbra user.
Make sure the 'password' you specify is should be same as 
 
zmlocalconfig -s | grep mysql_logger_root_password and zmlocalconfig -s | grep zimbra_logger_mysql_password
 
    mysql> FLUSH PRIVILEGES;
 
    mysql> exit
 
  
f) Go back to the file remove the --skip-grant-tables and save and exit the file.
+
Make sure the 'password' you specify is should be same as in the localconfig. Run following command to check those passwords.
 +
 
 +
  zmlocalconfig -s | grep mysql_logger_root_password
 +
  zmlocalconfig -s | grep zimbra_logger_mysql_password
 +
 
 +
Flush the privileges and exit.
 +
 
 +
  mysql> FLUSH PRIVILEGES;
 +
  mysql> exit
 +
 
 +
5. Go back to the file remove the --skip-grant-tables from /opt/zimbra/bin/logmysql.server and save and exit the file. You will need to do it as root.
 +
 
 +
6. Stop and start the mysql service. [Run as zimbra]
 +
 
 +
  $ logmysql.server stop
 +
  $ logmysql.server start
  
g) Stop and start the logmysql service. [logmysql.server stop and start]
+
7. Now you should be able to connect to the logmysql.
  
h) Now try to connect to logmysql.
+
  $ logmysql zimbra_logger
  
 +
==For ZCS 8.8.15/9==
 +
https://wiki.zimbra.com/wiki/Manually_Reset_MySQL_Password
  
 
{{Article_Footer|ZCS 5.0.x|2/13/2008}}
 
{{Article_Footer|ZCS 5.0.x|2/13/2008}}
  
 
[[Category:MySQL]]
 
[[Category:MySQL]]
[[Category:Troubleshooting]]
+
[[Category:Troubleshooting Authentication]]
 +
[[Category:ZCS 5.0]]

Latest revision as of 09:28, 13 November 2022

Admin Article

Article Information

This article applies to the following ZCS versions.

ZCS 5.0 Article ZCS 5.0

Please follow this wiki if following document does not helps you. http://wiki.zimbra.com/index.php?title=Resetting_LDAP_%26_MySQL_Passwords

Problem

Due to wrong passwords in the database of mysql/logmysql not able to connect to the database with the existing password. Need to be update the password manually.

Error

The error might looks like this.

#logmysql
ERROR 1044: Access denied for user 'zimbra'@'localhost' to database 'zimbra'
#mysql
ERROR 1044: Access denied for user 'zimbra'@'localhost' to database 'zimbra'

For Mysql Database

1. Stop the mysql service. [Run as Zimbra]

 $ mysql.server stop

2. Modify the /opt/zimbra/bin/mysql.server to start the mysql with skipping the grant tables. [Run as ROOT]

 # vi /opt/zimbra/bin/mysql.server

Modify the following:

     --defaults-file=${mysql_mycnf} \
     --external-locking \
     --ledir=${mysql_directory}/libexec < /dev/null > /dev/null 2>&1 &
  TO
     --defaults-file=${mysql_mycnf} \
     --external-locking \
     --ledir=${mysql_directory}/libexec < /dev/null > /dev/null 2>&1 --skip-grant-tables &

Save and Exit the file.

3. Start the mysql service. [Run as Zimbra]

 $ mysql.server start

4. Connect to the database and change the passwords.

   # mysql
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 6883
   Server version: 5.0.45-log Source distribution
   Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
   mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';      ----> Root user.
   mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='zimbra';    ----> Zimbra user.

Make sure the 'password' you specify is should be same as in the localconfig. Run following command to check those passwords.

 zmlocalconfig -s | grep mysql_root_password
 zmlocalconfig -s | grep zimbra_mysql_password

Flush the privileges and exit.

 mysql> FLUSH PRIVILEGES;
 mysql> exit

5. Go back to the file remove the --skip-grant-tables from /opt/zimbra/bin/mysql.server and save and exit the file. You will need to do it as root.

6. Stop and start the mysql service. [Run as zimbra]

 $ mysql.server stop
 $ mysql.server start

7. Now you should be able to connect to the mysql.

 $ mysql zimbra

For logger database

1. Stop the logger mysql service. [Run as Zimbra]

 $ logmysql.server stop

2. Modify the /opt/zimbra/bin/logmysql.server to start the logmysql with skipping the grant tables. [Run as ROOT]

 # vi /opt/zimbra/bin/logmysql.server

Modify the following:

     --defaults-file=${logger_mysql_mycnf} \
     --external-locking \
     --ledir=${logger_mysql_directory}/libexec &
   TO
     --defaults-file=${logger_mysql_mycnf} \
     --external-locking \
     --ledir=${logger_mysql_directory}/libexec --skip-grant-tables &

Save and Exit the file.

3. Start the logmysql service. [Run as Zimbra]

 $ logmysql.server start

4. Connect to the database and change the passwords.

   # logmysql
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 6883
   Server version: 5.0.45-log Source distribution
   Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
   mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';      ----> Root user.
   mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='zimbra';    ----> Zimbra user.

Make sure the 'password' you specify is should be same as in the localconfig. Run following command to check those passwords.

 zmlocalconfig -s | grep mysql_logger_root_password
 zmlocalconfig -s | grep zimbra_logger_mysql_password

Flush the privileges and exit.

  mysql> FLUSH PRIVILEGES;
  mysql> exit

5. Go back to the file remove the --skip-grant-tables from /opt/zimbra/bin/logmysql.server and save and exit the file. You will need to do it as root.

6. Stop and start the mysql service. [Run as zimbra]

 $ logmysql.server stop
 $ logmysql.server start

7. Now you should be able to connect to the logmysql.

 $ logmysql zimbra_logger

For ZCS 8.8.15/9

https://wiki.zimbra.com/wiki/Manually_Reset_MySQL_Password

Verified Against: ZCS 5.0.x Date Created: 2/13/2008
Article ID: https://wiki.zimbra.com/index.php?title=Issues_with_mysql_and_logmysql_passwords Date Modified: 2022-11-13



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