bastion-mysql-extractor.sh

#!/usr/bin/env bash


#>>> EC2 user data
  yum install -y docker mariadb iptraf-ng htop tmux
  # Adding Oli's key (add Dom's and Matilde's)
  echo "ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQCxFWqGO98YYuZowuuFF5XPX2hvTMK8C/gz/BVt5GCy60spByJ7j3QVc0YEPdzx1D//fLJAa8T34d9jBJorZqkkHtG7mXCp9kWSC4IluyOosg3Sg1Wd0WBZatboRgRlwMpCXnHOPQxAHdyDgNrpEZIL9B6v5wT95MDc5tITywhirgwRY48jZIjNmzHn8U9DTUCj9qGlAHGU6pTOnvQmjLVy54rIMTgrTykGdHcG1+YTnQq0/TPpM5WzN8Ep7nM7oKeg6+887raZkGb6a2zX0lDNG6LUaQXblJ2vgdHfrrtoHV/jnXo58dXMRoIGt2MliGGGJ5pM8T0EIS36eGk3g6gr obax@BMDIG0012.local" | tee -a /home/ec2-user/.ssh/authorized_keys
  # execute as super user
  usermod -aG docker ec2-user

  chkconfig docker on
  service docker start
#<<< End of EC2 user data

docker run -it --rm -p 3306:3306 \
-e LOCAL_PORT=3306 \
-e REMOTE_PORT=3306 \
-e SSH_BASTION_HOST=3.84.198.148 \
-e REMOTE_SERVER_IP=rds.ez.s.aws.economist.com.internal \
-e SSH_USER=ec2-user \
-v ~/.ssh/id_rsa:/ssh_key/id_rsa:ro \
jujhars13/docker-ssh-tunnel

# Connect to destination database (remote network)
mysql \
  --protocol=tcp \
  -h localhost \
  --port 3306 \
  -u admin \
  --password=password1 \
  -e "SHOW DATABASES;"

# Connect to source database (local network) -- TODO get crendetials
mysql \
  --protocol=tcp \
  -h ds1uivv17qntsv0.cu1ehgyqxjcg.us-east-1.rds.amazonaws.com \
  --port 3306 \
  -u admin \
  --password=adminadmin \
  -e "SHOW DATABASES;"



# Start the dump to a file
nohup mysqldump   --protocol=tcp   -h ds1uivv17qntsv0.cu1ehgyqxjcg.us-east-1.rds.amazonaws.com   --port 3306   -u admin   --password=adminadmin   --databases ezpubmysql >  /tmp/2020-02-21-ezpublish-dump1.sql &

# Import the dump in the other instance

cat /tmp/2020-02-21-ezpublish-dump1.sql |   mysql \
    --protocol=tcp \
    -h localhost \
    --port 3306 \
    -u admin \
    --password=password1

watch -d -n 2 \
      "mysql \
     --protocol=tcp \
     -h localhost \
     --port 3306 \
     -u admin \
     --password=password1 \
       -e \"SELECT table_schema 'DB Name', ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) 'DB Size in MB'  FROM information_schema.tables  GROUP BY table_schema;\""


# How to get the table size in Mysql
SELECT table_schema 'DB Name', ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) 'DB Size in MB'  FROM information_schema.tables  GROUP BY table_schema;

mysql destdb < <(ssh -C sourceserver.example.com "mysqldump sourcedb")

mysqldump \
  --protocol=tcp \
  -h ds1uivv17qntsv0.cu1ehgyqxjcg.us-east-1.rds.amazonaws.com \
  --port 3306 \
  -u admin \
  --password=adminadmin \
  --databases ezpubmysql > /tmp/2020-02-21-ezpublish-dump.sql

# Bastion is in VPC vpc-d375eab6

# nc -vvv ds1uivv17qntsv0.cu1ehgyqxjcg.us-east-1.rds.amazonaws.com 3306

# RDS's route table: Route Table:
# RDS's subnet range: az c : subnet-36da6141 172.17.7.0/24

# subnet-e04068b9 | suse1-CIPublic1d
# 172.17.19.0/24
# -
# subnet-5f420974 | suse1-CIPublic1b
# 172.17.11.0/24
# -
# subnet-33da6144 | suse1-WebTier1c
# 172.17.1.0/24
# -
# subnet-25a4630e | suse1-WebTier1b
# 172.17.0.0/24
# -
# subnet-e2c3c195 | suse1-CIPublic1c
# 172.17.10.0/24
# -
# subnet-e34068ba | suse1-WebTier1d
# 172.17.14.0/24
# -



# ssh ec2-user@3.84.198.148