Amazon Web Services has an amazing amount of capabilities, and some good ‘free’ stuff on it. But, and you knew the butt (sic) was coming, it can be painful to do simple things like setting up a MySQL database with remote access. Not setting up the database. Going through a hundred blind alleys, until you can figure out how to access the database remotely. Unless you’re going to drive out to their facility in Oregon, or wherever, obviously you’re going to have to access the database remotely, its a remote database.

I won’t go over the creation of the MySql database in the AWS RDS console, that part was no problem. Now for the hard part, we need to connect to the database. You need to make a security group. BUT WAIT, don’t use the security group menu in the RDS dashboard, apparently that’s just there as a distraction. Mine says something like, pound sand, your’re using VPC. So, you need to go to the VPC menu from the main AWS screen, then to Security Groups.

Create Security Group

Give it a name, and a description

Choose the dropdown for the default VPC connection

Then click the inbound rules tab that appears underneath. Choose MySQL/Aurora for the type, Protocol TCP for the protocol, and in the Source box put in 0.0.0.0/0 which means that you can access remotely from any IP. You can lock this down to one IP if you want, but my ISP changes the IP, and I travel.

Now we need to add the group to your MySQL instance. Go to AWS>RDS>Instances

Click the little arrow between the checkbox and MySQL to open up the details screen. Thanks Amazon for hiding this so well, I love to spend time in huge menus digging for stuff!

Choose the Instance Actions drop down

Pick modify, and change the security group to the one you just made and save it. Reboot your instance. Now you’re ready to connect. Go back to the details screen, on the left you’ll see three little tabs, one of them is a magnifying glass. Click this, and you’ll see your configuration details.

Now you should have everything you need to remotely connect. For your host URL, you use your endpoint (without the :3306 on the end, that’s your port).

Your login user will be the Username in the details screen, that’s the only one you can use until you connect, you can add other users after via SQL create users once you remotely connect.

Your password will be the password you entered when you created the instance.

And your protocol will be TCP. You should have everything you need for your database client.

Assuming all that worked, and you connected successfully, you can add other users with admin privileges on the database using this:

CREATE USER ‘user’@’%’ IDENTIFIED BY ‘password’;

GRANT ALL PRIVILEGES ON mydatabase.* TO ‘user’@’%’;

You need to get both of these lines correct, or else the connection will be refused.