Issues with mysql and logmysql passwords: Difference between revisions

Revision as of 16:39, 24 March 2015

- This is archive documentation, which means it is not supported or valid for recent versions of Zimbra Collaboration.

Article Information
This article applies to the following ZCS versions. 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

The Problem

Due to wrong passwords in the database of mysql/logmysql is not able to connect to the database with the existing password. We need to change the passwords manually.

The Error

The error might looks like this.

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

   #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

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: 2015-03-24

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

Other help Resources

User Help Page »
Official Forums »
Zimbra Documentation Page »

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 »

Issues with mysql and logmysql passwords: Difference between revisions

Revision as of 16:39, 24 March 2015

Contents

The Problem

The Error

For Mysql Database

For logger database

Products

Support

Learn

Community

@@ Line 1: / Line 1: @@
-'''Issue:'''
+{{Archive}}{{Article Infobox|{{admin}}||{{ZCS 5.0}}|}}__TOC__
-Due to wrong passwords in the database mysql/logmysql is not able to connect to the database with the existing password.
+Please follow this wiki if following document does not helps you.
+http://wiki.zimbra.com/index.php?title=Resetting_LDAP_%26_MySQL_Passwords
-We need to change the passwords manually.
+==The Problem==
-'''Error''':
+Due to wrong passwords in the database of mysql/logmysql is not able to connect to the database with the existing password. We need to change the passwords manually.
-#mysql
+==The Error==
-ERROR 1044: Access denied for user 'zimbra'@'localhost' to database 'zimba_logger'
+The error might looks like this.
+    #logmysql
+    ERROR 1044: Access denied for user 'zimbra'@'localhost' to database 'zimbra_logger'
-'''Steps''':
+    #mysql
+    ERROR 1044: Access denied for user 'zimbra'@'localhost' to database 'zimbra'
-) For Mysql:
+==For Mysql Database==
-a) Stop the mysql service. [Run as Zimbra: mysql.server stop]
+. Stop the mysql service. [Run as Zimbra]
-b) Run as ROOT vi /opt/zimbra/bin/mysql.server
+  $ mysql.server stop
-c) Modify the following:
+. 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
+   TO
        --defaults-file=${mysql_mycnf} \
@@ Line 33: / Line 41: @@
 Save and Exit the file.
-d) Start the mysql service. [Run as Zimbra: mysql.server start]
+. Start the mysql service. [Run as Zimbra]
+  $ mysql.server start
-e) Connect to the database.
-# 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.
+. Connect to the database and change the passwords.
-mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';      ----> Root user.
+    # 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.
-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.
-Make sure the password you specify is should be same as  zmlocalconfig -s | grep mysql_root_password and zmlocalconfig -s | grep zimbra_mysql_password.
+  zmlocalconfig -s | grep mysql_root_password
+  zmlocalconfig -s | grep zimbra_mysql_password
-mysql> FLUSH PRIVILEGES;
+Flush the privileges and exit.
+  mysql> FLUSH PRIVILEGES;
+  mysql> exit
-mysql> exit
+. 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.
-f) Go back to the file remove the --skip-grant-tables and save and exit the file.
+. Stop and start the mysql service. [Run as zimbra]
-g) Stop and start the mysql service. [mysql.server stop and start]
+  $ mysql.server stop
+  $ mysql.server start
-h) Now try to connect without the --skip-grant-tables option and check.
+. Now you should be able to connect to the mysql.
+  $ mysql zimbra
+==For logger database==
-----
+. Stop the logger mysql service. [Run as Zimbra]
-'''2) For Logmysql:'''
+  $ logmysql.server stop
-a) Stop the logmysql service. [Run as Zimbra: logmysql.server stop]
+. Modify the /opt/zimbra/bin/logmysql.server to start the logmysql with skipping the grant tables. [Run as ROOT]
-b) Run as ROOT vi /opt/zimbra/bin/logmysql.server
+  # vi /opt/zimbra/bin/logmysql.server
-c) Modify the following:
+Modify the following:
        --defaults-file=${logger_mysql_mycnf} \
        --external-locking \
        --ledir=${logger_mysql_directory}/libexec &
-TO
+    TO
        --defaults-file=${logger_mysql_mycnf} \
@@ Line 85: / Line 98: @@
 Save and Exit the file.
-d) Start the logmysql service. [Run as Zimbra: logmysql.server start]
+. Start the logmysql service. [Run as Zimbra]
+  $ logmysql.server start
+. Connect to the database and change the passwords.
-e) Connect to the database.
+    # 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.
-# logmysql
+Make sure the 'password' you specify is should be same as in the localconfig. Run following command to check those passwords.
-Welcome to the MySQL monitor.  Commands end with ; or \g.
+  zmlocalconfig -s | grep mysql_logger_root_password
-Your MySQL connection id is 6883
+  zmlocalconfig -s | grep zimbra_logger_mysql_password
-Server version: 5.0.45-log Source distribution
-Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
+Flush the privileges and exit.
-mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';      ----> Root user.
+   mysql> FLUSH PRIVILEGES;
+   mysql> exit
-mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='zimbra';    ----> Zimbra user.
+. 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.
-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
+. Stop and start the mysql service. [Run as zimbra]
-mysql> FLUSH PRIVILEGES;
+  $ logmysql.server stop
+  $ logmysql.server start
-mysql> exit
+. Now you should be able to connect to the logmysql.
-f) Go back to the file remove the --skip-grant-tables and save and exit the file.
+  $ logmysql zimbra_logger
-g) Stop and start the logmysql service. [logmysql.server stop and start]
+{{Article_Footer|ZCS 5.0.x|2/13/2008}}
-h) Now try to connect without the --skip-grant-tables option in the file and check.
+[[Category:MySQL]]
+[[Category:Troubleshooting Authentication]]
+[[Category:ZCS 5.0]]