How to setup Master-Master MySQL setup?

Master-to-Master MySQL replication and a python script for demo.
Setup

Clone this repository to your machine:

git clone https://github.com/jahantech/mm_mysql_setup.git
Go to mm_mysql_setup folder and run

docker build .
docker run -ti IMAGE_NAME
You will be able to log into mysql master instances instance via:

mysql –socket=/tmp/master1.sock
mysql –socket=/tmp/master2.sock
You should be able to create tables/databases and see them replicate on both other master instantly.

Demo

Once the docker instance is created, make sure haproxy is running and mysql instances are running:

mysqld_multi report
mysqld_multi start
Run python mysql script in the project and monitor the stats on http://localhost:8080/stats page.

Key thing to note here is that, master master replication will not work without you setting the offset values per master. Because if you don’t set offset for autoincrements (your primary keys) they will clash and replication will stop. The reason of clash is simple, your haproxy will try to send records to same table on two masters at the same time and hence both masters will try to commit transaction with same autoincrement id, which will definitely break your replication. To avoid that set offset value in the config. View my.cnf file for details.

References

MySQL Multi Instance Config: http://dev.mysql.com/doc/refman/5.7/en/mysqld-multi.html

Lock AWS Elastic IP Address (EIP) via RDNS

This post is regarding the AWS elastic ip addresses. If you have used AWS for instances, you must be aware of Elastic IP addresses. Elastic IP addresses are associated to your instances so that they are accessible via internet (inbound/outbound). Ofcourse you will need to setup strict security policies to restrict access to your server.

But say one day, you want to use AWS for production instance(s). So you create a big instance with enough resources and put some data on there. Everything looks good and then you ask one of your customers to add firewall rule for your Elastic IP address because your server will try to contact the customer to deliver files maybe.

Customer goes ahead and makes the firewall change. A day later, you forgot to have your breakfast and disassociated the production IP and released it back to AWS.

Panic! IP is lost because that goes back to AWS pool is used by the next request for creating instances. That is certainly a situation noone wants to be in specially when you have asked your customers to create firewall rules for your IP address.

Workaround: You won’t be able to get the exact IP address back, however you can prevent this from happening again. How?

Here is the trick, when you create an instance, create a DNS record for it. Once you have done that request AWS to create reverse DNS record for the instance. Once that is in place, even you yourself cant release the IP address back to the AWS IP pool. This is because AWS won’t release IP addresses that are associated to Reverse DNS records.

If you are hundred percent sure that you dont need that IP anymore , request AWS to remove the RDNS record and release the IP.

I hope you find this post useful. For now, there is no other way of securing/locking Elastic IP to your instance other than creating RDNS for it. Maybe Amazon will add lock IP functionality to AWS in future.