Quickly Dump + Backup + Sync MySQL tables between servers via command line

sync_mysql.sh

#!/bin/bash

# sync mysql tables from dev server to production server quickly via command line
# specify the list of tables to sync below

dev_host="127.0.0.1"
dev_user="root"
dev_pass="K#oDAk6AF@GumR7"
dev_db_name="local_dev"

prod_host="123.456.789.012"
prod_user="root"
prod_pass="RSk4t&nz9HLzH!9"
prod_db_name="prod_xyz_live"

suffix=$(date +%F-%H%M%S)  # The "+%s" option to 'date' is GNU-specific.
sync_label="test"

# uncomment for verbose output
#verbose="--verbose"

# specify the mysql tables to sync that correspond to the sync_label variable
sync_tables="airports countries states regions lat_long options"

echo

        echo "------ WARNING ------"
        echo "You are about to sync dev data to prod"
        echo "This will overwrite production data!"
        read -p "Are you sure you want to continue? " -n 1 -r
        if [[ $REPLY =~ ^[Nn]$ ]]
        then
                echo
                echo "aborting"
                echo
                exit 1
        fi

mkdir -p backups
cd backups

echo
echo

echo "dumping $dev_host:$dev_db_name $sync_label tables"
mysqldump --host=$dev_host --user=$dev_user --password=$dev_pass $verbose --add-drop-table --quick $dev_db_name $sync_tables > ${dev_db_name}-${sync_label}-${suffix}.sql

rm ${dev_db_name}-${sync_label}.sql
ln -s ${dev_db_name}-${sync_label}-${suffix}.sql ${dev_db_name}-${sync_label}.sql

echo "sync file written:"
ls -alh ${dev_db_name}-${sync_label}-${suffix}.sql

echo
echo "backing up $prod_host:$prod_db_name $sync_label tables"
mysqldump --host=$prod_host --user=$prod_user --password=$prod_pass $verbose --add-drop-table --quick $prod_db_name $sync_tables > ${prod_db_name}-${sync_label}-backup-${suffix}.sql

rm ${prod_db_name}-${sync_label}.sql
ln -s ${prod_db_name}-${sync_label}-backup-${suffix}.sql ${prod_db_name}-${sync_label}.sql

echo "backup file written:"
ls -alh ${prod_db_name}-${sync_label}-backup-${suffix}.sql


echo
echo "importing $dev_db_name-$sync_label.sql into $prod_host:$prod_db_name"
mysql --host=$prod_host --user=$prod_user --password=$prod_pass $verbose $prod_db_name < ${dev_db_name}-${sync_label}.sql

echo
echo "done"