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?
FLUSH PRIVILEGES;
after modifying the user's grants?'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 theGRANT
-statement? If not, add'username'@'ip-xx-xx-xxx-xxx.ec2.internal'
to your grants as well.