|access denied for connections to MySQL on Redhat Linux|
is the /etc/hosts file to blame?
I hope I'm posting this in the right board - I posted it here since I think its probably a Redhat linux specific issue.
I'm currently transferring a jsp/ MySQL site from the win98 machine I built it on to the linux webserver which it will go live on.
The problem is that the connection URL jdbc:mysql://localhost/my_db?user=dbuser&password=dbpassword is getting me an access denied error back from MySQL.
I gave the linux machine a name when I installed, and I think that originally when the ip 127.0.0.1 was getting translated to the host name for the MySQL connection it was using the machine name for the host rather than 'localhost'. So I removed the machine name.
Still no joy, so I assumed that the IP must have been getting resolved to 'localhost.localdomain' (a peculiarity of Redhat Linux) and therefore was still not being found as the correct host in the User table.
I then went into my network config through KDE and changed localhost.localdomain to localhost. Sure enough, the line in /etc/hosts now reads:
127.0.0.1 localhost localhost
but still no joy. I've triple checked the username and password and they're definitely correct. Does anyone have any ideas what the problem might be? Also, how can I check what domain name 127.0.0.1 is getting resolved to for the connection?
There are a couple of things you need to do with MySQL before you could have a user dive in and start manipulating data bases.
Did you set mysql root password with mysqladmin?
mysqladmin -u root password xxxxxx
After that you could make a database like this:
mysqladmin -u root -p create [yourdb_name]
Enter password:[root password]
Then get inside mysql:
# mysql -u root -p
Enter password: [root password]
You should get something that looks like this:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 3.23.41
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
Then you need to grant permission:
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON yourdb_name.* TO dbuser@localhost IDENTIFIED BY 'dbpassword';
That should do it if you are resolving properly. You could also allow access from anywhere (a but if a security risk) by doing a wild card:
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON yourdb_name.* TO dbuser@'%' IDENTIFIED BY 'dbpassword';
Thanks for the reply Littleman,
Sorry, I should have said, I have no problem connecting to the MySQL monitor, and had already set up the password for the root user, created a seperate db user for the jsp pages and granted the necessary privileges....when connecting via the MySQL monitor the host name resolves to 'localhost' for the connection.
I tried granting the privileges as you recommend so that a connection can be accepted from any host and now the pages connect ok.
I'm fairly sure it must be something to do with the host value that the pages attempt to connect with.
I still need to figure out what the problem is though, since I'm not happy leaving my privileges so open on what is soon to be a production server.
*sigh* back to the drawing board :)
Ok, well if it is a problem with the host name try a wildcard (user@'%') setting for host and see if it works. It will at least let you connect until you figure out the host problem. And if you can't connect then you know it is something else.
> I'm fairly sure it must be something to do with the host value that the pages attempt to connect with
Yes, I've had a similar problem with Mandrake - I could never pin down what hostname/alias PHP was trying to connect with. In the end I 'cheated' and granted privileges to firstname.lastname@example.org - works fine.
well, I found the problem, and now I feel like a right idiot!
Basically, as well as changing my /etc/hosts file so that 127.0.0.1 mapped to localhost instead of localhost.localdomain I also changed the password for the dbuser. I was worried that starting the password with a punctuation mark might be screwing things up.
Unfortunately I made both these changes at the same time and in a blur of tiredness and frustration forgot to update the password in my connection URL string.
The original problem was the mapping to localhost.localdomain, and although I'd fixed it I then screwed up the connection URL at the same time.
When I granted the privileges to dbuser from any host a new record was added to users with the correct password (I reverted to my original password for that), so I could then connect.
Many apologies Littleman, I'll go and stand in the corner for an hour.
Hehe, it is always the little things that get you. I am glad you figured it out.
Ah, good fix sugarkane - for the record mandrake does the same mapping as Redhat: 127.0.0.1 to localhost.localdomain. Apparently these are the only two distributions of Linux that do - I wonder if they have a common heritage somewhere down the line.
Although the /etc/hosts file for both distributions will also include a 'localhost' alias, for some reason it seems to be ignored when connecting to MySQL from scripts.
So theres now three possible fixes for this problem that I know of - granting priviledges to the IP address, changing your /etc/hosts file, and updating the Host record in the user table to match localhost.localdomain (I've also come accross people doing this).
Anyone have any opinions as to which is the better fix?
I'm wondering if anything else on Redhat Linux requires the localhost.localdomain mapping to work....