I've been looking around for best practices when setting up your database on the cloud but it still isn't clear to me which of the following solutions should we be going for?
I see Amazon Aurora being marketed as the better alternative however after some research it doesn't seem like people are using it. Is there a problem with it?
You should benchmark Aurora carefully before you consider it. Launch an instance and set up a test instance of your application and your database. Generate as high of load as you can. I did at my last company, and I found that despite Amazon's claims of high performance, Aurora failed spectacularly. Two orders of magnitude slower than RDS. Our app had a high rate of write traffic.
Our conclusion: if you have secondary indexes and have high write traffic, Aurora is not suitable. I bet it's good for read-only traffic though.
(Edit: the testing I'm describing was done in Q1 of 2017. As with most AWS services, I expect Aurora to improve over time. Amazon has an explicit strategy of "Release ideas at 70% and then iterate." From this, we should conclude that a new product from AWS is worth testing, but probably not production-ready for at least a few years after it's introduced).
At that company, I recommended RDS. They had no dedicated DBA staff, and the automation that RDS gives you for DB operations like upgrades and backups was very helpful. You sacrifice a little bit of flexibility on tuning options, but that shouldn't be a problem.
The worst inconvenience of RDS is that you can't have a MySQL user with SUPER privilege, but RDS provides stored procs for most common tasks you would need SUPER privilege for.
I compared a multi-AZ RDS instance versus a replica set of EC2 instances, managed by Orchestrator. Because Orchestrator requires three nodes so you can have quorum, RDS was the clear winner on cost here, as well as ease of setup and operations.