mysql per-batch export to file migrate script

export.sh

# @author: Christian Noel Reyes <[email protected]>
# @date: 2015-05-13
# @description: data / table-per table migration script.
# @caveats:
#   source and target databases must have the same table structure for this script to work with no issues
#   tested with mysql 5.1 (client)

# usage:
# bash export.sh --shost <source host> --sdb <source database> --suser <source username> --spass <source password>\
#   --limit 10000;
#
# example:
# bash export.sh --shost localhost --sdb test_db1_s --suser root --spass mypassword\
#   --limit 10000;

# default arguments {
migratION_shost="localhost";
migratION_sdb="db_loop";
migratION_suser="dev";
migratION_spass="dev";
migratION_sport="3306";
migratION_limit="500000";
#  } default arguments

# parse arguments {
for ((i=1;i<=$#;i++));
do

    # master/host details {
    if [ ${!i} = "--shost" ]
    then ((i++))
        migratION_shost=${!i};

    elif [ ${!i} = "--sdb" ];
    then ((i++))
        migratION_sdb=${!i};

    elif [ ${!i} = "--suser" ];
    then ((i++))
        migratION_suser=${!i};

    elif [ ${!i} = "--spass" ];
    then ((i++))
        migratION_spass=${!i};

    elif [ ${!i} = "--sport" ];
    then ((i++))
        migratION_sport=${!i};
    # } master details

    elif [ ${!i} = "--limit" ];
    then ((i++))
        migratION_limit=${!i};
    fi

done;
# } parse arguments
iteration=0;

echo "process initiating...";

mysql --skip-column-names --silent -P$migratION_sport -h$migratION_shost -u$migratION_suser -p$migratION_spass $migratION_sdb --execute="SHOW TABLES;" | while read -r a_table ; do
  iteration=$((iteration+1))

  echo "[$iteration] > exporting from $migratION_shost.$migratION_sdb.$a_table";
  mysqldump --replace --single-transaction --no-create-db --no-create-info -P$migratION_sport -h$migratION_shost -u$migratION_suser -p$migratION_spass --where="1=1 ORDER BY id DESC LIMIT $migratION_limit" $migratION_sdb $a_table > $migratION_sdb.$a_table.sql

  tar cvzf $migratION_sdb.$a_table.sql.tar.gz $migratION_sdb.$a_table.sql;
  rm $migratION_sdb.$a_table.sql;

  # sleep necessary for freeing up memory and/or cpu proc (uncomment this if and only if necessary)
  sleep .25
done;

echo "process ended!";