So for reasons Id rather not go into, my DB is on an EC2 instance in eu-west-1 and I have created a beanstalk app on us-east-1. Id like my app to talk to that EC2 instance on a MySQL port (3306).
Can anyone assist with how Id set this up, what ingress rules I need to setup on the EC2 security group? Given that I will have multiple versions of the app in beanstalk, the IP address may change regularly (after environment rebuilds etc).
The important concept regarding Security Group Rules you might be missing is, that you do not necessarily specify IP addresses as traffic sources alone, rather regularly will refer to other security groups as well:
The source can be an individual IP address (203.0.113.1), a range of addresses (e.g., 203.0.113.0/24), or an EC2 security group. The security group can be another group in your AWS account, a group in another AWS account, or the security group itself.
By specifying a security group as the source, you allow incoming traffic from all instances that belong to the source security group. [...] You might specify another security group in your account if you're creating a three-tier web service (see Creating a Three-Tier Web Service).
[emphasis mine]
Consequently you'll simply need to add the Beanstalk app instances security group as a traffic source for TCP port 3306 within the MySQL instance security group.
An additional concept to make oneself familiar with is, that you can have multiple security groups assigned to an instance, thus enabling (possibly dynamic) composition of the resulting firewall.
For example, a recommended practice for larger architectures suggests to specify a dedicated security group per 'role' your instances have (rather than accumulating several rules within one security group as usual), e.g. we have security groups like 'role-ssh' (TCP port 22) and 'role-mysql' (TCP port 3306), which are assigned to EC2 instances as needed in turn. You can read more about this concept in e.g. Security Groups - Most Underappreciated Feature of Amazon EC2.
