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

Answer 1

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.

Answer 2

There could be one of the following reasons:

1. 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.
2. 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.
3. 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?

Answer 3

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)

Answer 4

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

Answer 5

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.

Answer 6

For some configurations of ubuntu, the bind-address needs be changed in this file:

/etc/mysql/mysql.conf.d/mysqld.cnf 

Answer 7

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:

1. EC2_DB launched with IP 10.55.142.100
2. EC2_web launched with IP 10.55.142.144
3. EC2_DB and EC2_WEBare in the same security group with access across your DB port (3306)
4. EC2_DB has a mysql DB that can be reached locally by the DB root user ('root'@'localhost')
5. EC2_DB mysql DB has a remote user 'my_user'@'%' IDENTIFIED BY PASSWORD 'password'
6. 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.

Answer 8

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! :)

Answer 9

Update on new MySQL 5.7 on AWS EC2

The my.cnf file in /etc/mysql will only be having

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

DO NOT EDIT THIS FILE....MYSQL WILL BLOCK THE CHANGE

Instead, just go to /etc/mysql/mysql.conf.d/

There, you should find a file named mysqld.cnf

Here, you will find

bind-address = 127.0.0.1

Change it to

bind-address = 0.0.0.0 <OR> <Your IP>

THAT'S IT!!!. SAVE THE FILE AND RESTART MYSQL.

DO NOT DO ANY OTHER CHANGES LIKE 'SKIP-NETWORKING'. THIS SHOULD WORK

Thanks

Answer 10

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.

Answer 11

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.

Answer 12

Solution to this is three steps:

1. Within MySQL my.ini/ my.cnf file change the bind-address to accept connection from all hosts (0.0.0.0).

2. Within aws console - ec2 - specific security group open your mysql port (default is 3306) to accept connections from all hosts (0.0.0.0).

3. Within windows firewall add the mysql port (default is 3306) to exceptions.

And this will start accepting remote connections.

Answer 13

• 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

Answer 14

Here are the steps I followed for remote connection with MySQL installed on Ec2:

1. Edit the following line in /etc/mysql/mysql.conf.d/mysqld.cnf

   bind-address            = 127.0.0.1
   
   to
   
   bind-address            = 0.0.0.0
   

2. Create New user to remotely connect to mysql from any machine. Mention the machine ip instead of ‘%’ if you only wanted to remotely acces mysql from specific IP.

CREATE USER 'master'@'%' IDENTIFIED BY 'write-password-here';

GRANT ALL PRIVILEGES ON db_name.* TO 'master'@'%' WITH GRANT OPTION;

3. In the inbound rules of the security group of your Ec2 instance add a row having values MYSQL/Aurora, TCP, 3306 and source=0.0.0.0/0

Finally connect with MySQL from the remote machine by

mysql -u master -h write-server-ip -p

Answer 15

Simple Hack

1. Our EC2 MYSQL server will be hosted at remotehost:3306 using

port forwarding we can forward this remote port to our localhost:3307 and

the use MYSQL workbench to connect to `localhost:3307'

2. Make sure to edit inbound rules of your ec2 instance to allow traffic on

port 3306

Answer 16

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:

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

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

3. Create a new user myuser with limited privileges to one particular database mydb (basically following the instructions in this Amazon tutorial):

   $EC2prompt> mysql -u root -p
   [...omitted output...]
   mysql>  CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'your_strong_password';
   mysql>  GRANT ALL PRIVILEGES ON 'mydb'.* TO 'myuser'@'localhost';`
   

4. 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> GRANT ALL PRIVILEGES ON 'mydb'.* TO 'myuser'@'*.*.*.23';
   mysql> FLUSH PRIVILEGES;`
   

   (replace '*.*.*.23' by your local IP address)

5. For good measure, I exited mysql to the shell and restarted the msyql server:

   $EC2prompt> sudo service mysql restart

6. After these steps, I was able to happily connect from my computer with:

   $localprompt> mysql -h myinstancename.amazonaws.com -P 3306 -u myuser -p

   (replace myinstancename.amazonaws.com by the public address of your EC2 instance)

Answer 17

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> select host,user from mysql.user;
+-----------+-----------+
| host      | user      |
+-----------+-----------+
| %         | myuser    |
| localhost | mysql.sys |
| localhost | root      |
+-----------+-----------+
3 rows in set (0.00 sec)

Answer 18

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.

Answer 19

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.

Answer 20

For AWS EC2 Ubuntu 18.x and 20.x

1. Head over to the EC2 console.
2. Then to Security Groups.
3. The Security Group that is associated to your instance
4. Edit Inbound Rules
5. Add New Rule. Select MySQL/Aurora then select Source to anywhere.
6. Finally Follow this tutorial from digital ocean on how to setup a remote database connection that is optimised.

Tutorial link: https://www.digitalocean.com/community/tutorials/how-to-set-up-a-remote-database-to-optimize-site-performance-with-mysql-on-ubuntu-18-04