MySQL failover - Master to Master Replication
I'm thinking of doing exactly as you describe using Amazon's Route 53 DNS Failover.  So the idea is to setup 2 databases as master-master

db1.domain.com
db2.domain.com

Then do a Route53 "failover" set on

db.domain.com
-> primary is db1.domain.com => TCP-based health check on port 3306
-> secondary is db2.domain.com  => TCP-based health check on port 3306

I have not gotten to this point yet but that is my plan