Ok, so this isn't the sexiest topic, but if you're completely stuck the way I was several times today, maybe you're happy you found this post.
Today I needed to spin up a database I want available to students at the Newmark Graduate School of Journalism and also colleagues at Quartz. I also want to connect to the database from my home and the school using Python.
Since we use Amazon's web services, and I wanted to show the students SQL, I decided to give the AWS Aurora system a whirl — specifically the MySQL-compatible version.
As with many things AWS, it was a bit of a slog to get set up ... and I've decided to jot it all down while it's fresh so I can remember how the heck I did it (and show my students).
After a few tries, here's how I finally got set up:
Step by step
I started at console.aws.amazon.com and used my AWS account.
Then I picked "RDS" as the service I wanted. (Along the way, click the images to make them bigger.)
I got a big orange button inviting me to "Create Database" for an Aurora instance. You may need to hunt around if that's not the current promotion being offered.
Next, I picked "Standard Create." Here's my gotcha No. 1: Don't pick "Easy Create." Later I'll want to assign a database name, and that's not an option in Easy Create.
For Engine Type I piked "Amazon Aurora." Scroll down a little further and I picked "Amazon Aurora with MySQL compatibility," because that's the flavor I want.
And here's gotcha No. 2: While the "serverless" option seems attractive, and would be very cool in some cases, you can't give public access to a serverless Aurora database. The serverless version is available only inside the VPN in which it's created.
I'm not doing heavy production here, so "Dev/Test" is fine for me. I gave the instance a name (note that this is not the database name) and picked the option to generate a password.
Then I piked a size. I had to click the "Burstable classes" button to get the option for "db.t3.small," which is the cheapest size. (Here's the full pricing chart.)
Only thing I changed under "Connectivity" was to choose "Yes" for "Publicly accessible."
Then I opened the "Additional configuration" section with the little arrow, and here's where I entered the the database name. I'll need this later when I connect.
Here are more options, and how I set them:
Finally, I get to "Create database."
While it's being created, you get the option to "View credential details," which you need to do. That's where I got the username and password I'll need to connect later — and Amazon warns you that you won't be able to see the password again.
Making it available to you
There's one last, important step that prevented me from connecting to the database. I needed to clear my IP address — and the IP addresses of anyone who might use this — to get access.
IP control is set in the "security groups." I first clicked the "writer" instance of the database group up top, and then clicked on the default security group below:
Then you're taken to a spot where you can "Edit inbound rules" (note that I had to raise the horizontal screen divider to see that area):
Then I picked "MYSQL/Aurora" and "My IP" in the drop-downs:
If you're good at AWS, you could also make a new security group. To make it active, you have to click on the database instance, hit the "Modify" button, and add or change the security group.
Using the database in Python
The whole goal here is to get access to the database from a Python program. I'm using the "pymysql" library, and the connection code looks like this:
Note that I need my database name (check), my username (check), my password (check), and my endpoint (careful!) It turns out that I need the endpoint for the writer instance. You'll find that here:
With that, I was good to go. And now I won't forget how I got there!