2

I have Rstudio and RMySQL package installed on my local system and i want to connect to my test server database hosted on AWS EC2. I am using the followinf command:

mysqlconnection = dbConnect(MySQL(), user='root', password='*****', 
        dbname='sakila', host='ec2-55-231-255-3.compute-1.amazonaws.com',port=1234)

but it gives error that cant connect to database. It is working all right for localhost. Please suggest what needs to be done.

Ajit Kumar
  • 41
  • 1
  • 7
  • did you allow root remote login in your mysql instance? see http://stackoverflow.com/questions/11223235/mysql-root-access-from-all-hosts – Tom Jan 22 '16 at 12:56
  • 1
    btw, for security reasons, it s better to create a mysql user with restricted rights on the databases you need than to use root user – Tom Jan 22 '16 at 13:00
  • I am not sure about permission for remote login but we use SSH to connect to test server successfully. – Ajit Kumar Jan 22 '16 at 13:09
  • 1
    mysql usually disable root access when coming from another source than localhost. Also, it is possible that your mysql server only listens on localhost and not on public interface. So as suggested in the link in my first comment, go to mysql and type "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';", then edit /etc/my.cnf file and comment out "#bind-address = 127.0.0.1", then "service mysql restart". Then try again :) – Tom Jan 22 '16 at 13:23
  • Thanks for the reply. Actually the user mentioned above has all privileges except for 'file_priv' and 'super_priv'. The user mentioned above has a different username than root. I cant find "#bind-address = 127.0.0.1" in my.ini file. – Ajit Kumar Jan 22 '16 at 14:20
  • when I say "root", i mean mysql user not ssh user right? did you try the grant provilege command? secondly, to check how your server is running, can you paste the result of "sudo netstat -tlnp | grep -i sql"? – Tom Jan 22 '16 at 14:59
  • Yes user mentioned above is the mysql user. But his name is not 'root'. Can u tell me how to allow remote access on MySQL server?? – Ajit Kumar Jan 25 '16 at 07:30

2 Answers2

0

You likely need to go to your "Security Groups" and add your local IP address as an allowed "inbound" connection.

Amazon Webservices Dashboard > Networking > VPC > Networking and Security > Security Groups > Inbound (tab)
Brian FitzGerald
  • 3,041
  • 3
  • 28
  • 38
0

You need to add inbound IP of EC2 security group of EC2 instance/ machine. but How ??

  1. Go to EC2 service and select the instance you are using
  2. In description you can see "Security groups"
  3. Select the security group, it will open another page, which shows of the name and property of that same security group.
  4. Select inbound from bottom panel and add your local machine IP address into inbound

Hope it helps

DSBLR
  • 555
  • 5
  • 9