1

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?

  • 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
1

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?

0

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..

  • 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
0

Using the following dummy names:

source MySQL = ec2-source.compute-1.amazonaws.com, ip-source.ec2.internal
external MySQL wanting access = ec2-external.compute-1.amazonaws.com, ip-external.ec2.internal

To create a user that can access the source from external with read-only permissions, while limiting access to the IP of your external machine, run this on your source MySQL:

CREATE USER 'username'@'ip-external.ec2.internal' IDENTIFIED BY 'password';
GRANT SELECT ON DBname.* TO 'username'@'ip-external.ec2.internal';
FLUSH PRIVILEGES;

You can now use the following on the external machine to access the read-only source DB:

mysql -u username -p -h ec2-source.compute-1.amazonaws.com

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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