Connect to mysql on Amazon EC2 from a remote server

Question

I want to connect to db on EC2 from my local machine  I am not able to do and have tried everything-  I am using this command to connect to EC2   mysql -uUSERNAME -hEC2 IP -pPASSWORD   This error is generated      ERROR 2003  HY000   Can t connect to MySQL server on  IP   110    I have modified my cnf with   skip networking bind-address              0 0 0 0   Still not able to connect to the database

User · Answer

START MYSQL using admin user
- mysql -u admin-user -p (ENTER PASSWORD ON PROMPT)
Create a new user:
- CREATE USER 'newuser'@'%' IDENTIFIED BY 'password'; (% -> anyhost)
Grant Privileges:
- GRANT SELECT,DELETE,INSERT,UPDATE ON db_name.* TO 'newuser'@'%';
- FLUSH PRIVILEGES;

If you are running EC2 instance don't forget to add the inbound rules in security group with MYSQL/Aurura.

If these steps didn't work check /etc/mysql/my.cnf file and edit the bind_address param as

bind_address=0.0.0.0

User · Answer

I went through all the previous answers  and answers to similar questions  without success  so here is what finally worked for me  The key step was to explicitly grant privileges on the mysql server to a local user  for the server   but with my local IP appended to it  myuser           The complete step by step solution is as follows    Comment out the bind address line in  etc mysql my cnf at the server  i e  the EC2 Instance   I suppose bind address 0 0 0 0 would also work  but it s not needed as others have mentioned  Add a rule  as others have mentioned too  for MYSQL to the EC2 instance s security group with port 3306 and either My IP or Anywhere as Source  Both work fine after following all the steps  Create a new user myuser with limited privileges to one particular database mydb  basically following the instructions in this Amazon tutorial     EC2prompt gt  mysql -u root -p     omitted output     mysql gt   CREATE USER  myuser   localhost  IDENTIFIED BY  your strong password   mysql gt   GRANT ALL PRIVILEGES ON  mydb    TO  myuser   localhost     Here s the key step  without which my local address was refused when attempting a remote connection       ERROR 1130  HY000   Host        23  is not allowed to connect to this MySQL server    mysql gt  GRANT ALL PRIVILEGES ON  mydb    TO  myuser         23   mysql gt  FLUSH PRIVILEGES      replace        23  by your local IP address  For good measure  I exited mysql to the shell and restarted the msyql server    EC2prompt gt  sudo service mysql restart After these steps  I was able to happily connect from my computer with    localprompt gt  mysql -h myinstancename amazonaws com -P 3306 -u myuser -p   replace myinstancename amazonaws com by the public address of your EC2 instance

User · Answer

I know this is an old post but     I m experiencing this issue and I ve established that my problem is in fact not the EC2 instance  It seems like it might be a bug in the MySQL client driver software  I haven t done thorough research yet but I went as far as to install MySQL Workbench on the EC2 instance and IT also is erratic - it intermittently fails to connect  error is  connection cancelled    This link suggests a possible bug lower down the stack  not EC2   Of course  I have not done exhaustive research and my post might actually be off the mark  but worth noting and or exploring anyway  IMHO

User · Answer

Update  Feb 2017     Here are the COMPLETE STEPS for remote access of MySQL  deployed   on Amazon EC2  -     1  Add MySQL to inbound rules   Go to security group of your ec2 instance -  edit inbound rules -  add new rule -  choose MySQL Aurora and source to Anywhere     2  Add bind-address   0 0 0 0 to my cnf  In instance console     sudo vi  etc mysql my cnf   this will open vi editor  in my cnf file  after  mysqld  add new line and write this   bind-address   0 0 0 0   Save file by entering  wq enter   now restart MySQL   sudo  etc init d mysqld restart   3  Create a remote user and grant privileges   login to MySQL   mysql -u root -p mysql  enter password after this   Now write following commands   CREATE USER  jerry   localhost  IDENTIFIED BY  jerrypassword    CREATE USER  jerry      IDENTIFIED BY  jerrypassword    GRANT ALL PRIVILEGES ON     to jerry localhost IDENTIFIED BY  jerrypassword  WITH GRANT OPTION   GRANT ALL PRIVILEGES ON     to jerry     IDENTIFIED BY  jerrypassword  WITH GRANT OPTION   FLUSH PRIVILEGES   EXIT    After this  MySQL dB can be remotely accessed by entering public dns ip of your instance as MySQL Host Address  username as jerry and password as jerrypassword   Port is set to default at 3306

User · Answer

The error I was experiencing was that my database network settings allowed outbound traffic to the web     but inbound only from select IP addresses  I added an inbound rule to allow traffic from my ec2 s private IP and it worked

User · Answer

Solution to this is three steps    Within MySQL my ini  my cnf file change the bind-address to accept connection from all hosts  0 0 0 0   Within aws console - ec2 - specific security group open your mysql port  default is 3306  to accept connections from all hosts  0 0 0 0   Within windows firewall add the mysql port  default is 3306  to exceptions     And this will start accepting remote connections

User · Answer

While creating the user like  myuser   localhost   the user gets limited to be connected only from localhost  Create a user only for remote access and use your remote client IP address from where you will be connecting to the MySQL server  If you can bear the risk of allowing connections from all remote hosts  usually when using dynamic IP address   you can use  myuser       I did this  and also removed bind address from  etc mysql mysql cnf  Ubuntu  and now it connects flawlessly   mysql gt  select host user from mysql user   ----------- -----------    host        user         ----------- -----------                myuser        localhost   mysql sys     localhost   root         ----------- -----------  3 rows in set  0 00 sec

User · Answer

Though this question seems to be answered  another common issue that you can get is the DB user has been mis-configured   This is a mysql administration and permissions issue    EC2 DB launched with IP 10 55 142 100 EC2 web launched with IP 10 55 142 144 EC2 DB and EC2 WEBare in the same security group with access across your DB port  3306  EC2 DB has a mysql DB that can be reached locally by the DB root user   root   localhost   EC2 DB mysql DB has a remote user  my user      IDENTIFIED BY PASSWORD  password    A bash call to mysql from EC2 WEB fails  mysql -umy user -p -h ip-10-55-142-100 ec2 internal as does host references to the explicit IP  public DNS  etc      Step 6 fails because the mysql DB has the wrong user permisions   It needs this   GRANT ALL PRIVILEGES ON     TO  my user   ip-10-55-142-144 ec2 internal  IDENTIFIED BY PASSWORD  password    I would like to think that   would work for any remote server  but I did not find this to be the case   Please let me know if this helps you

User · Answer

A helpful step in tracking down this problem is to identify which bind-address MySQL is actually set to   You can do this with netstat   netstat -nat  grep  3306   This helped me zero in on my problem  because there are multiple mysql config files  and I had edited the wrong one   Netstat showed mysql was still using the wrong config   ubuntu myhost     netstat -nat  grep  3306 tcp        0      0 127 0 0 1 3306          0 0 0 0                 LISTEN   So I grepped the config directories for any other files which might be overriding my setting and found   ubuntu myhost     sudo grep -R bind  etc mysql  etc mysql mysql conf d mysqld cnf bind-address           127 0 0 1  etc mysql mysql cnf bind-address   0 0 0 0  etc mysql my cnf bind-address   0 0 0 0   D oh   This showed me the setting I had adjusted was the wrong config file  so I edited the RIGHT file this time  confirmed it with netstat  and was in business

User · Answer

There could be one of the following reasons    You need make an entry in the Amazon Security Group to allow remote access from your machine to Amazon EC2 instance   - I believe this is done by you as from your question it seems like you already made an entry with 0 0 0 0  which allows everybody to access the machine  MySQL not allowing user to connect from remote machine - By default MySql creates root user id with admin access  But root id s access is limited to localhost only  This means that root user id with correct password will not work if you try to access MySql from a remote machine  To solve this problem  you need to allow either the root user or some other DB user to access MySQL from remote machine  I would not recommend allowing root user id accessing DB from remote machine  You can use wildcard character   to specify any remote machine  Check if machine s local firewall is not enabled  And if its enabled then make sure that port 3306 is open    Please go through following link  How Do I Enable Remote Access To MySQL Database Server

User · Answer

Change  etc mysql my cnf   bind-address              127 0 0 1 to 0 0 0 0   Create user with     host and restart    etc init d mysql restart   Works for me

User · Answer

It could be that you have not configured the Amazon Security Group assigned to your EC2 Instance to accept incoming requests on port 3306  default port for MySQL    If this is the case then you can easily open up the port for the security group in a few button clicks   1  Log into you AWS Console and go to  EC2   2  On the left hand menu under  Network  amp  Security  go to  Security Groups   3  Check the Security Group in question  4  Click on  Inbound tab   5  Choose  MYSQL  from drop down list and click  Add Rule   Might not be the reason but worth a go

User · Answer

Log into AWS Management Console  Navigate to RDS then select the db instance and go to  security Groups   Adding CIDR IP under the security group  fixed the problem

User · Answer

as mentioned in the responses above  it could be related to AWS security groups  and other things  but if you created a user and gave it remote access     and still getting this error  check your mysql config file  on debian  you can find it here   etc mysql my cnf and find the line   bind-address              127 0 0 1   and change it to   bind-address              0 0 0 0   and restart mysql   on debian ubuntu    etc init d mysql restart   I hope this works for you

User · Answer

For some configurations of ubuntu  the bind-address needs be changed in this file    etc mysql mysql conf d mysqld cnf

User · Answer

The default ip of Mysql in instance EC2  Ubuntu is 127 0 0 1 if you want to change it is just to follow the answers that have already been given here

[mysql] Connect to mysql on Amazon EC2 from a remote server

Examples related to mysql

Examples related to amazon-ec2