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

bobby953 · 04-06-2010, 01:29 PM

So, I was happily using this script to backup my mysql databases:
http://sourceforge.net/projects/automysqlbackup/

It was working great until yesterday when I played around with /etc/hosts, /etc/sysconfig/network and /etc/resolv.conf file to fix up my hostname and stuff and now this script is not working. When i run the script, i get the following error:

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

Please note that I can login just fine using my root account from the shell:
mysql -u root -p

Here is the stanza of the backup script that is relevant:

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 I change the DBHOST=127.0.0.1, it still says
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) INSTEAD of 'root'@'127.0.0.1'

I changed the following files to these:

Code:

[root@proliant ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               serv1.domain.com serv1 localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
10.10.0.54      serv1.domain.com

[root@proliant ~]# cat /etc/resolv.conf
search domain.com
nameserver 8.8.8.8
nameserver 8.8.4.4

[root@proliant ~]# cat /etc/sysconfig/network
NETWORKING=yes
NETWORKING_IPV6=no
HOSTNAME=serv1
GATEWAY=10.10.0.51

Thanks a lot in advance.

paulsm4 · 04-06-2010, 01:34 PM

If I were you, I would consider simply:

* Add a new user "root@localhost" to mysql
* Make sure this "root@localhost" has the right password, and all the necessary grants
* You should be done

Alternatively, you could give your existing "root" user a wildcard hostname (if it doesn't already).

'Hope that helps .. PSM

PS:
Google for "ERROR 1045" - you'll find a zillion hits .. and probably a few good suggestions (if the above doesn't work for you).

rweaver · 04-06-2010, 01:36 PM

Just because I ran into this issue with another program recently... try making your localhost line in hosts look like this: '127.0.0.1 localhost localhost.localdomain serv1.domain.tld serv1' You can also try adding --verbose to your mysql command line to get more information. If you select out your user from mysql what does that look like?

You could also try setting DBHOST to 127.0.0.1

bobby953 · 04-06-2010, 01:44 PM

Thanks paulsm4 for your prompt response. However, I already have a user root@localhost in my mysql.user table. And I know the password I have in my script is correct, cuz i can login with that password using the following:
mysql -u root -h localhost -p

bobby953 · 04-06-2010, 01:52 PM

Quote:

Originally Posted by rweaver

Just because I ran into this issue with another program recently... try making your localhost line in hosts look like this: '127.0.0.1 localhost localhost.localdomain serv1.domain.tld serv1' You can also try adding --verbose to your mysql command line to get more information. If you select out your user from mysql what does that look like?

You could also try setting DBHOST to 127.0.0.1

Thanks rweaver... Changing the order in my hosts file does not work either. Plus, doing that renders my FQDN as localhost. Also, I tried using 127.0.0.1 as my DBHOST. Plz look at my original post.

I would show you the output from the cmd below
user mysql;
select * from user where user='root';
but I get too many column from the above cmd. How to show only 1st 3 columns or so?

frndrfoe · 04-06-2010, 02:15 PM

Does this work?

Code:

/etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
10.10.0.54      serv1.domain.com  serv1

rweaver · 04-06-2010, 02:45 PM

Quote:

Originally Posted by bobby953

user mysql;
select * from user where user='root';
but I get too many column from the above cmd. How to show only 1st 3 columns or so?

select Host,User from user where user='root';

(dont want password encrypted or otherwise

)

paulsm4 · 04-06-2010, 03:50 PM

Also, look here:

Quote:

http://ubuntuforums.org/showthread.php?t=1020139

Post the output of:

Code:

sudo grep -a -o -e '.*root' /var/lib/mysql/mysql/user.MYD
ls -l /var/lib/mysql
cat /etc/hosts
grep bind-address /etc/mysql/my.cnf

The first command should show stuff like

Code:

localhostroot

The second lists the contents of /var/lib/mysql. I'm looking for symlinks -- they don't behave as expected if you happen to use them here. (You have to edit /etc/apparmor.d/usr.sbin.mysqld in this case.)

The third command is to check that localhost resolves to 127.0.0.1.

The fourth command is to check the bind-address.

There seems to be something unusual about your setup, so the above are just sanity checks.

bobby953 · 04-06-2010, 04:43 PM

Quote:

Originally Posted by frndrfoe

Does this work?

Code:

/etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
10.10.0.54      serv1.domain.com  serv1

Nope, does not. plus with this config hostname -f shows unknown host

bobby953 · 04-06-2010, 04:55 PM

Quote:

Originally Posted by paulsm4

Also, look here:

Hey

Code:

sudo grep -a -o -e '.*root' /var/lib/mysql/mysql/user.MYD

  localhostroot
differenthostname.domain.comroot
        127.0.0.1root

Code:

ls -l /var/lib/mysql
shows no symlinks

Code:

cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               serv1.domain.com serv1 localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
10.10.0.54      serv1.domain.com

Code:

grep bind-address /etc/mysql/my.cnf
Nada - don't have such directive in my config file

The peculiar thing is from the first cmd, the hostname does not match my hostname. Any way to match this to my hostname??
Also, from the last cmd, i do not have the bind-address directive. But I didn't have that before either and everything was working fine.

Thanks for your efforts. Really appreciate it!

frndrfoe · 04-06-2010, 05:13 PM

Quote:

Any way to match this to my hostname??

If you issue the command `hostname` do you get the correct info?

If not issue `hostname serv1` to set it till the next boot. If you changed the hostname in /etc/sysconfig/network and did not either reboot or issue the hostname command with the new hostname then it is probably inconsistent.

perhaps restart mysql after setting the hostname too.

chrism01 · 04-06-2010, 07:05 PM

Maybe login to the DB manually and run

SHOW GRANTS

http://dev.mysql.com/doc/refman/5.0/en/show-grants.html to see what the DB thinks/expects.

paulsm4 · 04-06-2010, 11:18 PM

Hi, bobby953 -

I understand that things were probably working fine before you changed a couple of networking files. But your mysql "identity" is a combination of mysql user, Linux user/group and ... yes, Linux hostname. Further complicating things, there are subtle differences between IPV4 and IPV6 that might also be an issue here.

So let's start from the top, and pull together all the suggestions folks made, combined with what you've already tried. Shall we?

I'll give you examples from my Ubuntu 9.10 server, which is probably reasonably close to whatever you've got.

Quote:

1. Get OS version:

Quote:

uname -a

Code:

Linux ubuntu 2.6.31-17-generic-pae #54-Ubuntu SMP Thu Dec 10 17:23:29 UTC 2009 i686 GNU/Linux

2. Verify you can log on to mysql from command line (and verify MySQL version):

Quote:

mysql --version

Code:

mysql  Ver 14.14 Distrib 5.1.37, for debian-linux-gnu (i486) using  EditLine wrapper

mysql -uroot -pXXX mysql

Code:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.1.37-1ubuntu5 (Ubuntu)
  <= OK.  So I can log in as "root", and I'm running mySQL 5.1.37

3. Check which users you have defined, which user you logged in as, and your current grants:

Quote:

mysql> select host,user from user;

Code:

+-----------------------+------------------+
| host                  | user             |
+-----------------------+------------------+
| %                     | %.paulsm         |
| %                     | paulsm           |
| %                     | wikiuser         |
| 127.0.0.1             | root             |
| localhost             | debian-sys-maint |
| localhost             | root             |
| localhost             | wikiuser         |
| localhost.localdomain | wikiuser         |
| ubuntu                | root             |
+-----------------------+------------------+
9 rows in set (0.00 sec)
  <= You'll notice there's one "root" for 127.0.0.1, and another for "localhost".
     I'm *not* saying you "need" to do this.  But it happens to work ;-)

mysql> show grants;

Code:

+----------------------------------------------------------------------------+
| Grants for root@localhost                                                  |
+----------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD   | '*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' WITH GRANT OPTION                |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

4. Exit mysql and check your database:

Quote:

sudo grep -a -o -e '.*root' /var/lib/mysql/mysql/user.MYD

Code:

        localhostroot
ubunturoot
        127.0.0.1root

5. Check for "bind-address":

Quote:

grep bind-address /etc/mysql/my.cnf

Code:

bind-address            = 127.0.0.1

6. Check "localhost" and "127.0.0.1" (they should actually be the same: but we need to check):

Quote:

ping localhost

Code:

PING localhost (127.0.0.1) 56(84) bytes of data.
64 bytes from localhost (127.0.0.1): icmp_seq=1 ttl=64 time=1.76 ms
64 bytes from localhost (127.0.0.1): icmp_seq=2 ttl=64 time=0.156 ms
...

ping 127.0.0.1

Code:

PING 127.0.0.1 (127.0.0.1) 56(84) bytes of data.
64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.090 ms
64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.081 ms

OK: that should be plenty to get you in the right direction.

One final bit of food for thought:
Q: What *is* that funny "bind-address", anyway?
A: It allows remote access, or restricts you to local access only

bobby953 · 04-07-2010, 03:38 AM

paulsm4, awesome post... will follow your guideline exactly tomorrow.. it's 4 am and i'm so sleepy... lol... really appreciate your efforts!

bobby953 · 04-07-2010, 05:01 PM

Code:

uname -a
Linux serv1 2.6.18-164.el5PAE #1 SMP Thu Sep 3 04:10:44 EDT 2009 i686 i686 i386 GNU/Linux

Code:

mysql --version
mysql  Ver 14.12 Distrib 5.0.84, for redhat-linux-gnu (i686) using readline 5.1

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

Code:

mysql> select host,user from user;
+----------------------+--------------+
| host                 | user         |
+----------------------+--------------+
| %                    | someuser    |
| 127.0.0.1            | root         |
| localhost            | someotheruser|
| localhost            | someotheruser|
| localhost            | someotheruser  |
| localhost            | root         |
| localhost            | wpuser    |
| server1.domain.com | root         |
+----------------------+--------------+
8 rows in set (0.00 sec)

Code:

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

Code:

sudo grep -a -o -e '.*root' /var/lib/mysql/mysql/user.MYD
        localhostroot
server1.domain.comroot
        127.0.0.1root

Code:

grep bind-address /etc/my.cnf
Nothing... no such directive

Code:

 ping localhost
PING serv1.domain.com (127.0.0.1) 56(84) bytes of data.
64 bytes from serv1.domain.com (127.0.0.1): icmp_seq=1 ttl=64 time=0.029 ms
64 bytes from serv1.domain.com (127.0.0.1): icmp_seq=2 ttl=64 time=0.034 ms

Code:

ping 127.0.0.1
PING 127.0.0.1 (127.0.0.1) 56(84) bytes of data.
64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.037 ms
64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.016 ms

Code:

hostname
serv1

hostname -f
serv1.domain.com

Please note the red entries above. I don't know where server1 came from. I think it came from when I initially installed my system. My hostname is now serv1 and NOT server1. Sorry about the confusion, they are almost similar. When everything was working dandy, my FQDN was www.anotherdomain.com
Then i changed it to serv1.domain.com and the mysql backup script broke. Thanks again for all ur help