Amazon RDS migration script

rds-import.sh

#!/bin/bash

TMP_DIR="/mnt/data/tmp"

LOCAL_HOST="localhost"
LOCAL_PASS=""

FOREIGN_HOST=".us-east-1.rds.amazonaws.com"
FOREIGN_PASS=""

IGNORE_USERS=""
IGNORE_DATABASES="mysql information_schema performance_schema"

MYSQLDUMP_OPTIONS="--max-allowed-packet=10M \
  --single-transaction \
  --compress \
  --order-by-primary"
MYSQLIMPORT_OPTIONS=""


function main {

  migrate_users
  migrate_dbs
}

function log {
  echo "$(date)  $1"
}

function migrate_users {

  log "Importing users"

  users_query="select distinct(grantee) from information_schema.user_privileges"
  users=$(mysql -h$LOCAL_HOST -uroot -p$LOCAL_PASS -s -e "$users_query" | tail -n +1)
  for user in $users; do

    if [[ $user =~ localhost ]] || [[ $user =~ 127.0.0.1 ]]; then
      log "Skipping local user $user"
      continue
    fi

    for skipuser in $IGNORE_USERS; do
      if [ "$user" == "$skipuser" ]; then
        log "Skipping user $user"
        continue 2
      fi
    done

    log "Importing grants for $user"
    user_grants_query="show grants for $user"
    user_grants=$(mysql -h $LOCAL_HOST -u root -p$LOCAL_PASS -s -e "$user_grants_query")
    while IFS= read -r grant; do
      mysql -h $FOREIGN_HOST -u root -p$FOREIGN_PASS -e "$grant"
    done <<< "$user_grants"
  done
}

function migrate_dbs {

  log "Importing databases"

  dbs=$(mysql -h$LOCAL_HOST -uroot -p$LOCAL_PASS -s -e "show databases" | tail -n +1)
  for db in $dbs; do

    for skipdb in $IGNORE_DATABASES; do
      if [ "$db" == "$skipdb" ]; then
        log "Skipping database $db"
        continue 2
      fi
    done

    log "Importing database $db"
    log "Size = $(dbsize $db) MBs"

    # Dump and import database into the foreign host
    # Notes:
    #  The --no-defaults option on the mysqldump cmd prevents a conflict with loose-local-infile.
    #
    #  The first two sed expressions remove any definer statements and forces those
    #  "procedures" (triggers and views) to run under the current security context.
    #
    #  The 3rd sed expression converts MyISAM databases to InnoDB.
    dumpfile=$TMP_DIR/db.sql
    mysqldump --no-defaults -h $LOCAL_HOST -u root -p$LOCAL_PASS --databases $db $MYSQLDUMP_OPTIONS > $dumpfile
    sed -i 's/\/\*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER \*\///g' $dumpfile
    sed -i 's/\/\*!50017 DEFINER=`root`@`localhost`\*\///g' $dumpfile
    sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' $dumpfile
    mysql $MYSQLIMPORT_OPTIONS -h $FOREIGN_HOST -u root -p$FOREIGN_PASS < $dumpfile
    rm $dumpfile

    if [ $? -eq 0 ]; then
      log "Import completed for $db without errors"
    else
      log "Import completed for $db with errors"
    fi

  done
}

function dbsize {

  db=$1

  query="
    SELECT sum(data_length+index_length)/1024/1024
    FROM information_schema.TABLES
    GROUP BY table_schema
    HAVING table_schema = '$db'
  "

  size=$(mysql -h$LOCAL_HOST -uroot -p$LOCAL_PASS -s -e "$query")
  ret=$?

  if [ -z "$size" ]; then
    size=0
  fi

  echo $size
  return $ret
}

main