Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Quickly Dump + Backup + Sync MySQL tables between servers via command line
#!/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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.