While this was tested using a TurnKey LAMP Server build (Debian Linux), it applies to any Debian/Ubuntu server with MySQL.
Top-Level View of Tutorial
- Enable MySQL access through server’s firewall (Amazon AWS-EC2 Security Groups)
- Create non-root MySQL user with % (any) Host permissions
- Alter the bind-address in the MySQL configuration file (my.cnf) file
- Restart MySQL
Amazon AWS Security Groups for your EC2 Server
Hop into Amazon AWS and select Security Groups on the left side. Select your server in the top box, then you can click on the INBOUND, then you can EDIT the list.
On the new page, you can select MySQL from the left drop-down box, which automatically sets it to TCP and port 3306. The final choice is the SOURCE needs to be set to Anywhere to allow you access it from any IP address. Click SAVE and you’re all done with Amazon AWS.
Create MySQL User with Remote Access Permissions
If you want to give remote access to only a single database, it’s best to first select that database on the left-side of PHPMyAdmin. You can then select the PRIVILEGES tab and click Add New User near the bottom. You can use whatever username you wish, but the important detail is to set the Host to Use Text Field and then set the text field to %. This allows access from any IP address. The remaining fields should be straight-forward (you can also use the image as a reference).
Hit Go on the bottom-right to save your new user and wrap up your work in this section.
Alter MySQL Configuration (my.cnf)
The following step requires either the ROOT user or use of the sudo command.
You’ll need to access the command line for your server, most likely via SSH. The commands below assume you are the root user.
The file you are looking for is located in /etc/mysql/ and is called my.cnf. You can edit it with your favorite editor, such as Vi or NANO. NANO is the most user-friendly on first-use, so you can edit the file with:
Find the line that looks like this:
bind-address = 127.0.0.1
Alter it as such:
bind-address = 0.0.0.0
Save your changes (in NANO press CTRL+X and follow the prompts to finish saving the file and exit).
The final step is to restart MySQL. The following command gets it done:
Takes a few seconds, but then you’re all set! You can now access the MySQL database via the new user you created from any external IP address!