Access mysql DB from different instance on EC2

Question

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

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?

Are you running in a VPC?

Steve
– Steve

2013-03-12 13:33:11 +00:00
Commented Mar 12, 2013 at 13:33 — Steve
– Steve, Commented Mar 12, 2013 at 13:33
No its not VPC but AWS.

Shrujan Shetty
– Shrujan Shetty

2013-03-12 13:35:27 +00:00
Commented Mar 12, 2013 at 13:35 — Shrujan Shetty
– Shrujan Shetty, Commented Mar 12, 2013 at 13:35

datasage · Accepted Answer · 2013-03-12 14:35:08Z

1

You should set your grant up for any host. But control access via granting permissions to the security group. Internal IPs can change if an instance is stopped/restarted.

answered Mar 12, 2013 at 14:35

datasage

19.7k2 gold badges51 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shrujan Shetty · Accepted Answer · 2013-03-14 12:25:45Z

1

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

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

answered Mar 14, 2013 at 12:25

Shrujan Shetty

2,4323 gold badges28 silver badges44 bronze badges

1 Comment

codersofthedark Over a year ago

'%' gives access to the said user from any IP which makes the complete system insecure.

codersofthedark · Accepted Answer · 2015-02-26 06:55:28Z

1

For connecting to the mysql instance from a different instance you should do the following:

GRANT SELECT ON DBname.* TO 'chowzter'@'xx.xx.xxx.xx' IDENTIFIED BY 'mypassword';
FLUSH PRIVILEGES;

example:

GRANT SELECT ON facebook.* TO 'zuckerberg'@'10.0.0.420' IDENTIFIED BY 'mysecuredpassword';
FLUSH PRIVILEGES;

You can go through MySql Documentation to under this even better: http://dev.mysql.com/doc/refman/5.1/en/grant.html

This should pretty much work assuming firewall allow access within VPC and you dont have bind address enabled.

answered Feb 26, 2015 at 6:55

codersofthedark

9,70510 gold badges50 silver badges72 bronze badges

Collectives™ on Stack Overflow

Access mysql DB from different instance on EC2

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related