Access MySQL Hosted on Amazon EC2 Server Remotely

If you have a working MySQL server hosted on Amazon EC2 and you want to access it remotely with a MySQL client, then follow these steps.Amazon_MySQL_logos

    1. Access you EC2 server via SSH. You can do it with PuTTY on windows and by using Terminal application on Linux or Mac. For a step by step tutorial go here.
  1. Once, you are logged in, you need to create a new MySQL user. First, issue the following command to login with the root user
    mysql -u root -p

    If a password is set provide the password and login. Now create a new user with this command

    create user 'bill'@'%' identified by 'some_password';

    Replace ‘bill’ with your username and ‘some_password’ with your desired password

  2.  Grant all privileges to the user ‘bill’
    Grant all privileges on *.* to 'bill'@'%' with grant option;
  3. Confirm if the new user ‘bill’ has been created
    select user, host from mysql.user;
  4. You can also update an existing user and login with that user credentials from remote machine.
    UPDATE mysql.user SET host = '%' WHERE user='some_remote_user';
  5. Now open up MySQL client like MySQL Workbench, on your local machine, and provide  Username as bill, Password that you provided for user bill and the Host IP or Hostname. In case you don’t know how to get the EC2 host, follow the below steps.
  • Go to http://www.aws.amazon.com and login with your Amazon cloud credentials.
  • Click on EC2 and select your EC2 instance.
  • The details will be shown at the bottom of the page, scroll down and copy the value against Public DNS. This is your Host for MySQL.
I hope this was helpful. Please provide your feedback.

Published by

Ali

Some day I'll fill this up. Till then Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Nam cursus. Morbi ut mi. Nullam enim leo ....

Leave a Reply

Your email address will not be published. Required fields are marked *