MySQL Master/Slave - Auto Server Id

Question

I currently have a single dedicated machine hosting my MySQL instance and I'm looking at moving to a Master/Slave replication setup. I've configured a test master and slave as per the usual tutorials and it works fine. However, my application runs on AWS using a Load Balancer and Auto-Scaling etc... So new instances are added and removed. Each of these will have a local MySQL instance for reads. Can I configure the "server-id" in my.cnf to auto-assign? If not how would I do this with a dynamically sizing set of slave nodes?

Thanks

Answer 1

When you're using Auto Scaling, then you should move your database off the Auto Scaled instances. Each Auto Scaled instance should not have it's own copy of the database.

Instead:

1. Have a master database that is not part of an Auto Scaling group.
2. Have 0 or more read-replicas to help alleviate load on your database while reading
3. Have the EC2 instances that are part of your Auto Scaling group reference your not-auto-scaled database nodes as needed.

This way, as EC2 instances scale up/down/in/out, your database stays put separated from those instances.