LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices



Reply
 
Search this Thread
Old 04-06-2010, 02:29 PM   #1
bobby953
Member
 
Registered: Mar 2009
Posts: 41

Rep: Reputation: 15
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.
 
Old 04-06-2010, 02:34 PM   #2
paulsm4
Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
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).
 
Old 04-06-2010, 02:36 PM   #3
rweaver
Senior Member
 
Registered: Dec 2008
Location: Louisville, OH
Distribution: Debian, CentOS, Slackware, RHEL, Gentoo
Posts: 1,833

Rep: Reputation: 163Reputation: 163
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

Last edited by rweaver; 04-06-2010 at 02:44 PM.
 
Old 04-06-2010, 02:44 PM   #4
bobby953
Member
 
Registered: Mar 2009
Posts: 41

Original Poster
Rep: Reputation: 15
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
 
Old 04-06-2010, 02:52 PM   #5
bobby953
Member
 
Registered: Mar 2009
Posts: 41

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by rweaver View Post
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?
 
Old 04-06-2010, 03:15 PM   #6
frndrfoe
Member
 
Registered: Jan 2008
Distribution: RHEL, CentOS
Posts: 375

Rep: Reputation: 38
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
 
Old 04-06-2010, 03:45 PM   #7
rweaver
Senior Member
 
Registered: Dec 2008
Location: Louisville, OH
Distribution: Debian, CentOS, Slackware, RHEL, Gentoo
Posts: 1,833

Rep: Reputation: 163Reputation: 163
Quote:
Originally Posted by bobby953 View Post
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 )
 
Old 04-06-2010, 04:50 PM   #8
paulsm4
Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
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.
 
Old 04-06-2010, 05:43 PM   #9
bobby953
Member
 
Registered: Mar 2009
Posts: 41

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by frndrfoe View Post
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
 
Old 04-06-2010, 05:55 PM   #10
bobby953
Member
 
Registered: Mar 2009
Posts: 41

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by paulsm4 View Post
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!
 
Old 04-06-2010, 06:13 PM   #11
frndrfoe
Member
 
Registered: Jan 2008
Distribution: RHEL, CentOS
Posts: 375

Rep: Reputation: 38
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.

Last edited by frndrfoe; 04-06-2010 at 06:14 PM.
 
Old 04-06-2010, 08:05 PM   #12
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.6, Centos 5.10
Posts: 16,324

Rep: Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041
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.
 
Old 04-07-2010, 12:18 AM   #13
paulsm4
Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
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

Last edited by paulsm4; 04-07-2010 at 12:38 AM.
 
Old 04-07-2010, 04:38 AM   #14
bobby953
Member
 
Registered: Mar 2009
Posts: 41

Original Poster
Rep: Reputation: 15
paulsm4, awesome post... will follow your guideline exactly tomorrow.. it's 4 am and i'm so sleepy... lol... really appreciate your efforts!
 
Old 04-07-2010, 06:01 PM   #15
bobby953
Member
 
Registered: Mar 2009
Posts: 41

Original Poster
Rep: Reputation: 15
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

Last edited by bobby953; 04-07-2010 at 06:05 PM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) samnjugu Linux - Software 12 01-22-2013 07:22 AM
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) shogun1234 Linux - Server 1 06-15-2009 12:12 PM
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) ShaqDiesel Linux - Server 1 09-12-2008 12:49 AM
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) S canegames Debian 11 07-18-2007 01:57 PM
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) mohtasham1983 General 2 04-02-2007 07:36 PM


All times are GMT -5. The time now is 05:21 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration