Connect to mysql on Amazon EC2 from a remote server

Question

I want to connect to db on EC2 from my local machine, I can’t do it and tried everything - I use this command to connect to EC2:

mysql -uUSERNAME -hEC2_IP -pPASSWORD 

This error is generated.

ERROR 2003 (HY000): unable to connect to MySQL server on "IP" (110)

I changed my.cnf with

 skip networking bind-address = 0.0.0.0 

Unable to connect to database

Answer 1

There may be one of the following reasons:

• You need to make an entry in the Amazon security group in order to allow remote access from your machine to an instance of Amazon EC2 .: - I believe that this is done by you, since from your question it seems that you have already recorded with 0.0.0.0, which allows everyone access the car.
• MySQL does not allow a user to connect from a remote machine: - By default, MySql creates a root user ID with administrator access. But root access to the identifier is limited only by the local host. This means that the root id user with the 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 another database user to access MySQL from a remote machine. I would not recommend allowing root user database access from a remote machine. You can use the% wildcard to indicate any remote machine.
• Check if the local firewall of the machine is turned on. And if you enable it then make sure port 3306 is open.

Please follow the link below: How to enable remote access to the MySQL database server?

Answer 2

as stated in the answers above, this could be due to AWS security groups and other things. but if you created the user and gave him remote access "%" and still get this error, check the mysql configuration 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 3

Update: February 2017

Here are the FULL STEPS for remote access to MySQL (deployed on Amazon EC2):

1. Add MySQL to the inbound rules.

Go to the security group of your ec2 instance -> edit incoming rules -> add a new rule -> select MySQL/Aurora and the source in Anywhere .

2. Add bind-address = 0.0.0.0 to my.cnf

In the console console:

 sudo vi /etc/mysql/my.cnf 

this will open the vi editor.
in the my.cnf file after [mysqld] add a new line and write:

 bind-address = 0.0.0.0 

Save the file by typing :wq (enter)

Now restart MySQL:

 sudo /etc/init.d/mysqld restart 

3. Create a remote user and grant rights.

enter MySQL:

mysql -u root -p mysql (enter the password after that)

Now write the 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 that, you can gain remote access to MySQL dB by entering the public dns / ip of your instance as the MySQL host address, username as jerry and password as jerrypassword. (The default port is set to 3306)

Answer 4

You may not have configured the Amazon Security Group assigned to your EC2 instance to accept incoming requests on port 3306 (the default port for databases).

If so, you can easily open the port for the security group with a few clicks of buttons:

1) Log in to the AWS Console and go to "EC2"

2) In the menu on the left in the "Network and Security" section, go to the "Security Groups" section

3) Check the security group of interest

4) Click "Inbox"

5) Select "MYSQL" from the drop-down list and click "Add Rule"

There can be no reason, but worth it ...

Answer 5

A useful step in tracking this issue is to determine which MySQL bit address is actually set. You can do this with netstat:

 netstat -nat |grep :3306 

This helped me sort out my problem because there are several mysql configuration files, and I edited the wrong one. Netstat showed that mysql is still using the wrong configuration:

 ubuntu@myhost:~$ netstat -nat |grep :3306 tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 

So, I grepped config directories for any other files that could override my parameter 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'ah! This showed that the setup I configured was the wrong configuration file, so this time I edited the RIGHT file, confirmed it with netstat and was in business.

Answer 6

Although this question seems to be answering, another common problem you may get is that the database user was not configured correctly. 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 through your DB port (3306)
• EC2_DB has a mysql database accessible locally by root ( 'root'@'localhost' )
• EC2_DB mysql DB has remote user 'my_user'@'%' IDENTIFIED BY PASSWORD 'password'
• A bash mysql call from EC2_WEB fails with the error: mysql -umy_user -p -h ip-10-55-142-100.ec2.internal , as well as links to host links to an explicit IP address, public DNS, etc. .

---

Step 6 fails because mysql DB has invalid user permissions. He 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 % will work on any remote server, but I have not found that it is.

Please let me know if this helps you.

Answer 7

For some ubuntu configurations in this file, you need to change the binding address:

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

Answer 8

Change /etc/mysql/my.cnf :

 bind-address = 127.0.0.1 to 0.0.0.0 

Create a user with '%' host and restart:

 /etc/init.d/mysql restart 

Works for me! :)

Answer 9

Log in to AWS Management. Go to RDS, then select the db instance and go to the "Security Groups" section. Adding CIDR / IP to the security group fixed the problem.

Answer 10

The solution to this consists of three steps:

• In MySQL, the my.ini / my.cnf file changes the binding address to accept a connection from all hosts (0.0.0.0).

• In the aws console, the ec2 special security group opens your mysql port (3306 by default) to accept connections from all hosts (0.0.0.0).

• In the Windows firewall, add the mysql port (default 3306) to the exceptions.

And it will start accepting remote connections.

Answer 11

• START MYSQL with admin
  • 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' @ '%';
  • PRIVILEGES OF FLUSH;

If you are using an EC2 instance, be sure to add the inbound rules to the security group using MYSQL / Aurura.

If these steps do not work, check the /etc/mysql/my.cnf file and edit the bind_address parameter as

bind_address = 0.0.0.0

Answer 12

I know this is an old post, but ...

I am having this problem and I have determined that my problem is not really an instance of EC2. It looks like this might be a bug in the MySQL client driver software. I have not done thorough research yet, but I went so far as to install MySQL Workbench on an EC2 instance, and IT is also unstable - it fails with an error ("connection canceled" error). This link suggests a possible error below the stack, not EC2.

Of course, I did not do exhaustive research, and my post might not really be familiar, but it is worth noting and / or exploring in any case IMHO.

Answer 13

I went through all the previous answers (and answers to similar questions) without success, and so here is what finally helped me. The main step was to explicitly grant privileges on the mysql server to the local user (for the server), but with my local IP address attached to it ( myuser@*.*.*.* ). A complete step-by-step solution is as follows:

• Comment out the line bind_address in /etc/mysql/my.cnf on the server (i.e. an instance of EC2). I suppose bind_address=0.0.0.0 will also work, but it is not needed as others have mentioned.

• Add the rule (as already mentioned in other sources) for MYSQL to the EC2 instance security group with port 3306 and either My IP or Anywhere as the source. Both work fine after completing all the steps.

• Create a new myuser user with limited rights to one specific mydb database (mainly 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';` 

• Here is the key step without which my local address was rejected when trying to connect remotely (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' with your local IP address)

• For a good measure, I left mysql in a shell and restarted the msyql server:

  $EC2prompt> sudo service mysql restart

• After these steps, I was able to happily connect from my computer using:

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

  (replace myinstancename.amazonaws.com with the shared address of your EC2 instance)

Answer 14

When creating a user such as "myuser" @ "localhost", the user is limited to connecting only from localhost. Create a user only for remote access and use the IP address of the remote client from which you will connect to the MySQL server. If you are at risk of allowing connections from all remote hosts (usually using a 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 15

The default Mysql ip in the Ubuntu EC2 instance is 127.0.0.1 if you want to change it, just follow the answers that have already been given here.