#!/bin/bash

# Replace these variables with your own database credentials
REMOTE_USER="remote_user"
REMOTE_PASSWORD="remote_password"
REMOTE_HOST="remote_host"
REMOTE_DATABASE="remote_database"
LOCAL_USER="local_user"
LOCAL_PASSWORD="local_password"
LOCAL_DATABASE="local_database"
FILENAME="dump_$(date +%Y%m%d%H%M%S).tar.gz"

# Use mysqldump to dump the remote database to a file
ssh $REMOTE_USER@$REMOTE_HOST "mysqldump -u$REMOTE_USER -p'$REMOTE_PASSWORD' $REMOTE_DATABASE" | gzip > $FILENAME

# Copy the compressed dump file to the local machine
scp $FILENAME $LOCAL_USER@$LOCAL_HOST:/path/to/save

# Import the dump file to the local database
gunzip < $FILENAME | mysql -u$LOCAL_USER -p$LOCAL_PASSWORD $LOCAL_DATABASE

# Remove the dump file from the local machine
rm $FILENAME

In this script, you need to replace the variables REMOTE_USER, REMOTE_PASSWORD, REMOTE_HOST, REMOTE_DATABASE, LOCAL_USER, LOCAL_PASSWORD, and LOCAL_DATABASE with your own database credentials. The FILENAME variable specifies the name of the compressed dump file, which includes the current date and time to avoid overwriting existing files.

To run the script, save it to a file, make it executable using chmod +x script_name.sh, and then run it using ./script_name.sh.

This script connects to the remote host using ssh, runs the mysqldump command to dump the remote database to a file, compresses the dump file using gzip, copies the compressed file to the local machine using scp, imports the dump file to the local database using the mysql command after uncompressing it using gunzip, and finally removes the dump file from the local machine.