Access mysql database from an Amazon Windows EC2 instance to another EC2 windows instance

Question

I have 2 Amazon EC2 instances running WAMP stack. They are both in the same amazon account but different regions. Normally if I had this setup on Godaddy I would have used SOAP service to access data from one to another. My question is how do I access Mysql db from another instance?

Answer 1

A bit more detail in your question would advantageous. However, if you are trying to access data in one direction only (i.e. only one database server for each database), you could access MySQL directly:

• Setup an elastic IP address on your MySQL instance
• Open Port 3306 in your security group
• Configure (my.cnf) MySQL to bind to all interfaces (instead of just 127.0.0.1)
• Set MySQL privileges to allow access for user(s) at the remote address
• Use the MySQL instance's DNS address to access the database

Since you are in two different regions, you will be using the public address on your instances, and incur full data costs for any data transferred between the instances. The use of an elastic IP will save you having to setup another system by which to track changes in the instance's IP address. (It could be done by setting up your own DNS server, but is a lot more work, for no apparent gain.)

You may be able to setup your security group to only allow access from other instances in the same security group, in order to prevent outside access to MySQL.

The reason for using the DNS (instead of the IP), is that if you do move your instances to the same region, the DNS will map to the internal IP, and result in lower costs for data transfer.

If you are trying to setup two servers, both which serve the same databases, then you will need to setup replication between your instances, as well as implementing the above.