LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Software (http://www.linuxquestions.org/questions/linux-software-2/)
-   -   ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) (http://www.linuxquestions.org/questions/linux-software-2/error-1045-28000-access-denied-for-user-root%40localhost-using-password-yes-800405/)

bobby953 04-06-2010 02:29 PM

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
 
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 02: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 02: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 02: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 02:52 PM

Quote:

Originally Posted by rweaver (Post 3926512)
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 03: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 03:45 PM

Quote:

Originally Posted by bobby953 (Post 3926529)
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 04: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 05:43 PM

Quote:

Originally Posted by frndrfoe (Post 3926555)
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 05:55 PM

Quote:

Originally Posted by paulsm4 (Post 3926650)
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 06: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 08: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-07-2010 12:18 AM

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 04: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 06: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 :)


All times are GMT -5. The time now is 05:32 PM.