Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have 2 instances in EC2 server, one instance has the mysql DB and the content stored in it and another instance to access the data stored in the first instance.

I need to create a user with read only access stored in the Database.

So

  1. CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

    grant select on DBname.* to 'chowzter'@'localhost';

ERROR

ERROR 1130 (HY000): Host 'ip-xx-xx-xxx-xxx.ec2.internal' is not allowed to connect to this MySQL server

2 . CREATE USER 'username'@'ec2-xx-xx-xx-xxx.compute-1.amazonaws.com' IDENTIFIED BY 'password'

grant select on DBname.* to 'username'@'ec2-xx-xx-xxx-xxx.compute-1.amazonaws.com';

This time the ip address used was the internal IP of the second instance got by the command.

ip a

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN 
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host 
valid_lft forever preferred_lft forever

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 12:31:41:02:58:47 brd ff:ff:ff:ff:ff:ff
inet **XX.XX.XX.XXX/23** brd YY.YYY.YY.YYY scope global eth0
inet6 fe80::1031:41ff:fe02:5847/64 scope link 
valid_lft forever preferred_lft forever

ip address: "XX.XXX.XX.XXX/23"

command: mysql -hxx.xx.xxx.xxx -uusername -ppassword

ERROR

ERROR 1045 (28000): Access denied for user 'username'@'ip-xx-xx-xxx-xxx.ec2.internal' (using password: YES)

Any Idea how to solve it?

share|improve this question
    
Did you run FLUSH PRIVILEGES; after modifying the user's grants? –  Alexander Janssen Mar 13 '13 at 9:13
    
Yes executed FLUSH PRIVILEGES; after creating the user and granting privileges. Still the same error. –  shrujan shetty Mar 13 '13 at 10:09
    
What it's also strange; the grant is for 'username'@'ec2-xx-xx-xxx-xxx.compute-1.amazonaws.com', but the error message is for the hostname 'ip-xx-xx-xxx-xxx.ec2.internal'. Did you really make sure using the same IP address as you used in the GRANT-statement? If not, add 'username'@'ip-xx-xx-xxx-xxx.ec2.internal' to your grants as well. –  Alexander Janssen Mar 13 '13 at 10:34
    
@AlexanderJanssen thanks for your reply. –  shrujan shetty Mar 14 '13 at 12:14
add comment

2 Answers

You need to check the security group of the first instance as well. Can you ssh into the first instance from the second instance?

Is the MySQL port open in your security group setting?

And why do you not use the packaged service RDS (MySQL) instead?

share|improve this answer
    
Thanks for your reply –  shrujan shetty Mar 14 '13 at 12:13
add comment
up vote 0 down vote accepted

Finally solved it .

First I created a user by using %.

 CREATE USER 'name'@'%' IDENTIFIED BY 'password';

Then you can give privileges to that user.

grant select on DBname.* to 'name'@'%';

mysql -h xx.xx.xxx.xxx -u user -p

Here the IP address is of the first instance

That gives me the privilege to access the DB in Read only mode from second instance..

share|improve this answer
1  
You do know that this user may now access the database with this password from every IP-address? –  Alexander Janssen Mar 18 '13 at 12:47
    
Then @AlexanderJanssen any idea as to restrict the user in such a way that he can access the DB from only one IP address? –  shrujan shetty Apr 17 '13 at 6:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.