AWS Route Table config for public facing web server

Say my app backend on AWS consists of:

• a VPC containing a public subnet and an internet gateway
• In the subnet is a single EC2 instance running a web server
• an RDS MySQL DB (running on port 3306)

I understand that I will need to configure the subnet with a network ACL to only allow traffic on the web server's port (80). I also understand that I will need to configure the EC2 instance to be managed by a security group that will also only allow port 80 traffic.

Now then, I have the following needs:

• I need a way to point requests to my web server's public DNS (say, http://myapp.example.com) to the EC2 instance, and ensure there are traffic routes in place for that
• I need a way to allow my web server to communicate with RDS/MySQL over port 3306

I understand I will need a routing table to accomplish all of this (possibly multiple routing tables), and that I will need to add routes (traffic rules) to this table. But I am unsure of which resources to associate with the routing table to make this all happen, and why?

Do I create a routing table and associate it with the internet gateway, the VPC or the subnet (or something else)? Which routes do I add to this table? Thanks for any help here.

I understand that I will need to configure the subnet with a network ACL to only allow traffic on the web server's port (80)

You don't "need" do to that. The Security group on the web server will be enough. Don't mess with the default Network ACLs unless you understand how things like ephemeral ports work.

I need a way to point requests to my web server's public DNS (say, http://myapp.example.com) to the EC2 instance, and ensure there are traffic routes in place for that

Your server has a public IP address. You just need to setup a DNS record that points to the IP address. That's handled at your DNS provider, not in a network routing table. There is no extra routing involved at the VPC level for this.

I need a way to allow my web server to communicate with RDS/MySQL over port 3306

As long as they are in the same VPC, and you haven't messed with the default Network ACL rules, then by default everything in the VPC has a route to everything else in the VPC. You would just need to create an inbound rule in the RDS server's security group, for port 3306, that allows the web server's security group to access it.

I understand I will need a routing table to accomplish all of this (possibly multiple routing tables), and that I will need to add routes (traffic rules) to this table. But I am unsure of which resources to associate with the routing table to make this all happen, and why?

A VPC has a default routing table that will allow everything you have listed above. You don't need to change that, and absolutely shouldn't change it if you don't understand how it works.