Replicate External 5.6 Mysql Slave to AWS RDS (with Replicate Filters)

AWS-ELB-x_real_user_ip.md

1. Update nginx conf (/etc/nginx/nginx.conf)

  http {
    ...
    ...
    real_ip_header X-Forwarded-For; # Get Client IP From X-Forwarded_For
    set_real_ip_from 10.0.0.0/8;  # AWS ELB ip ranges
    ...
    ...
  }
  

2. restart nginx in order to apply

  service nginx restart

nginx-geoip.md

1. Download Country Data & unpack from MaxMind

    wget -N http://geolite.maxmind.com/download/geoip/database/GeoLiteCountry/GeoIP.dat.gz
    gunzip GeoIP.dat.gz
    sudo mv GeoIP.dat /etc/nginx/
   

2. Append geoip config to Nginx

  geoip_country /etc/nginx/GeoIP.dat;
  

ssh_port_forward.sh

#!/usr/bin/env bash

set -e

KEY_FILE_PATH={ssh_key_path}

chmod 400 "$KEY_FILE_PATH"

function cleanup {
    if [ "$sub_pid1" != "" ]; then
	    kill $sub_pid1 || true;
    fi
    exit;
}

trap cleanup SIGHUP SIGINT SIGTERM

ssh -v -N -o StrictHostKeyChecking=no {ssh_user}@xxxxx -p 22 -L {rds_host}:{rds_port}:{mysql_slave_host}:{mysql_slave_port} -i "$KEY_FILE_PATH" &
sub_pid1=$!

sleep 5

while true;
do
    echo
    read -n1 -p "Enter q to quit: " user_input;
    if [ "$user_input" == "q" ]; then
        cleanup;
        exit;
    fi
done;

step.md

1. Update Mysql conf on External Mysql 5.6 Slave

      #replicate filters eg: replicate-do-db = dbName
      #read-only                     = 1
      log-slave-updates              = 1
      binlog-format                  = {MIXED?}
      skip-slave-start
   

2. Restart Mysql on External Mysql 5.6 Slave

    sudo service mysql restart

3. Reset binlog on External Mysql 5.6 Slave

    RESET MASTER;

4. Dump db data on External Mysql 5.6 Slave

    mysqldump -u {user} -p -Qce -R --single-transaction --tz-utc --flush-privileges --master-data=2 --max-allowed-packet=900M  --flush-logs --databases {dbname}|gzip > {dump.sql}.gz

5. Create RDS Instance

6. Import DB dump file

7. Setup replication on RDS to External Mysql 5.6 Slave

  CALL mysql.rds_set_external_master (
host_name
, host_port
, replication_user_name
, replication_user_password
, mysql_binary_log_file_name
, mysql_binary_log_file_location
, ssl_encryption
);

8. Start Slave on RDS

  CALL mysql.rds_start_replication;

9. Start Slave on External Mysql 5.6 Slave

  START SLAVE;

10. Verify on both instance

  SHOW SLAVE STATUS\G;