ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

paulsm4 · 04-07-2010, 11:24 PM

Try this:

Quote:

mysql -uWHATEVER_USER_HAS_ACCESS -pTHAT_USERS_PASSWD mysql

update user set host = 'serv1' where host = 'server1.domain.com';
select host,user from user;
flush privileges;

quit

Restart mysql and try to access mysql as "root".

If that works, I imagine (based on my understanding of what you've said), that your script should work, too.

Plan B, for whatever it's worth, might be to use a DIFFERENT user in the script. I'm pretty sure your main goal is to get the script working - it doesn't necessarily have to be "root", correct?

Good luck .. PSM

bobby953 · 04-08-2010, 01:49 PM

Hi.. thanks for your reply... I did that but it did not help. However there is a silver lining.
One other change that I made, and which i overlooked; sorry about that, was the line below in the backup script:

Code:

# Username to access the MySQL server e.g. dbuser
USERNAME=root

# Username to access the MySQL server e.g. password
PASSWORD=myrootpassword

# Host name (or IP address) of MySQL server e.g localhost
DBHOST=localhost

# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
DBNAMES="all"

# Backup directory location e.g /backups

# The meat of the script is here:
mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $1 > $2

BACKUPDIR="/root/backup/mysqlbackup"

When the script was working fine, the line said:
DBNAMES="db1 db2 db3"

When I changed to
DBNAMES="all", it included another database 'db4'. Here is how I setup db4:
When I created this db, i created the following user to access the db:

mysql> create user 'userT'@'localhost' identified by '*****';
mysql> grant all on db4.* to 'userT'@'localhost';

Now, I did not give root the grant for db4. But isn't it that root by default has grant on every db created?

So I made a duplicate of my backup script and made the change like this:

Code:

# Username to access the MySQL server e.g. dbuser
USERNAME=userT

# Username to access the MySQL server e.g. password
PASSWORD=******

# Host name (or IP address) of MySQL server e.g localhost
DBHOST=localhost

# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
DBNAMES="db4"

# Backup directory location e.g /backups
BACKUPDIR="/root/backup/mysqlbackup"

This time, the script runs fine. So the only thing I can think of is that root had full grant on db1, db2, db3 but not db4 and hence the error?

But when i do this:

Code:

mysql> show grants;
+---------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                     |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*******' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `db4`.* TO 'root'@'localhost'                                                   |
+---------------------------------------------------------------------------------------------------------------+

Doesn't the first line means that root has all privileges (*.*) on all db?

Thanks again for your insights

paulsm4 · 04-08-2010, 03:16 PM

Hi -

The short answer is "No, not necessarily

". In several different aspects, for several different potential reasons.

This link might help:
http://dev.mysql.com/doc/refman/5.1/...ing-users.html

Anyway, it sounds like you got it working.

If possible, I encourage you to experiment with different combinations of MySQL username, MySQL host name and grants (using commands like we did above). Always run "flush privileges" (and/or restart mysql) after making any changes.

Your .. PSM

PS:
Please do click the "thanks" button if any of our posts helped

bobby953 · 04-08-2010, 04:59 PM

Thanks a lot for all your time and effort. While I have u here, how come my mysql configuration is so tiny. This is the content of this file:

Code:

[root@proliant etc]# cat my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# To allow mysqld to connect to a MySQL Cluster management daemon, uncomment
# these lines and adjust the connectstring as needed.
#ndbcluster
#ndb-connectstring="nodeid=4;host=localhost:1186"

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[ndbd]
# If you are running a MySQL Cluster storage daemon (ndbd) on this machine,
# adjust its connection to the management daemon here.
# Note: ndbd init script requires this to include nodeid!
connect-string="nodeid=2;host=localhost:1186"

[ndb_mgm]
# connection string for MySQL Cluster management tool
connect-string="host=localhost:1186"

I am running centos. I ran
rpm -ql mysql
and it doesn't show me any other config files. Are these all the config parameters needed to run mysql daemon?

bobby953 · 04-08-2010, 10:19 PM

Hi... So yes, using userT I am able to use the script to backup db4.. However, turns out I can't backup db1 or db2 or db3 using the script using root@localhost. I made this simple test script:

Code:

#!/bin/sh

datum=`/bin/date +%Y%m%d-%H-%M`

/usr/bin/mysqldump -u root -p***** --lock-all-tables --databases db2 > /root/backups/db2-${datum}.sql
exit 0

When I run this script, I get:

Code:

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

However, if i change the script like this:

Code:

#!/bin/sh

datum=`/bin/date +%Y%m%d-%H-%M`

/usr/bin/mysqldump -u root -p --lock-all-tables --databases db2 > /root/backups/db2-${datum}.sql
exit 0

... it asks for password...I put in the same password and it works.

I'm scratching my head... I spent all day troubleshooting but still can't figure out... any ideas?

Once again, for reference:

Code:

mysql> show grants;
+---------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                     |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '0d050c8e5b46372d' WITH GRANT OPTION |


[root@serv1 ~]# mysqltest
mysqltest: Could not open connection 'default': 1045 Access denied for user 'root'@'localhost' (using password: NO)
not ok

I can login just fine using:
mysql -u root -p
And entering the root password

I honestly tried googling and found so many references to this Error, and not even one solution worked for me. How is this possible

deumatasha · 03-24-2011, 06:53 PM

Hi have you found a solution to this problem, as i am faced with the same issue. I need to have cron run my backup so i need the password to be in the command, but for some reason it giving me the 1024 error and I am sure my password is correct.

deumatasha · 03-24-2011, 08:35 PM

I have found a solution to this problem.

1. Create a db user of any user, set the password setting of that user to no password.
2. Give this user all db privileges

then run the following command:
mysqldump -u [username with no passwd] -h localhost --all-databases > mysqlFullBackup.sql

NB:
a. There is no "-p" as your user has no have a passwd, this will allow the commend to run without requesting a passwd or giving a 1045 error.
b. mysqlFullBackup.sql , is the file name of your data pass back up.

Please note that due to this user having full database privilege and no password, for security reasons this user name must be kept private and secure at all times. Note also that no one can see your usernames for your database unless they have access to the database or otherwise given this information.