Is AWS’s Elastic Load Balancer a single point of failure

You should think carefully why you need to have the same data in both US and EU. After all these are different users.

Running in a multi-region environment is much more complex and usually giving back performance due to the inherent latency between US and EU.

Even if you move out of RDS and try to replicate your data between regions, either asynch or synch, you will get latency issues that will give bad performance to your users.

The easiest way is to set up a dedicated RDS server in EU and share nothing between these instances.

A single ELB routes traffic to exactly one set of instances, and distributes the incoming traffic to all the instances "behind" it. It does not selectively route traffic based on any layer 7 analysis of the traffic, such as the Host: header.

You need one ELB for each set of instances. As you describe it, that's one ELB for each webapp.

If your primary purpose for running ELB is offloading the SSL using a wildcard certificate (I have one system designed like this, with dozens of apps living at many-different-domains.my-wildcard-cert-domain.com), then the instances "behind" the ELB could be running a reverse proxy such as HAProxy (or several other alternatives, like Varnish) that can make layer-7 routing decisions and then forward the traffic to the appropriate subset of machines behind them, which also allows more sophisticated load balancing and has the advantage of providing you with stats and traffic counters, aggregate and separate.

       /-- HAProxy \  /----- instances hosting app #1  
ELB ---|            >> ----- instances hosting app #2 
       \-- HAProxy /  \----- instances hosting app #n 

The intermediate ^^^^ instances can evaluate the Host: headers (among other things) and even capture the value of the session cookie in their logs for analysis.

This setup also allows me to run multiple apps on overlapping subsets of instances, where appropriate, and do a lot of other things that ELB by itself doesn't directly support. It also returns a custom "503" page in the case where an application gets overloaded or otherwise becomes unavailable, which ELB does not do on its own. I've depicted 2 proxy servers here, for no particular reason other than your mention of the number 2 in the question. My setup actually has 3, one for each availability zone in the region where this is deployed.