Syncing 2 or More Databases by Shell Script

Database sync (short for database synchronization) refers to the process of ensuring that two or more databases (or database systems) have consistent, up-to-date, and identical data. This is particularly important in environments where multiple copies of a database are stored across different systems, servers, or geographical locations, and updates need to be reflected across all instances.

Requirements:

• Remote MySQL Access: The MySQL server should allow connections from your IP address (or all IPs) and not just localhost.
• Firewall: The MySQL port (default 3306) should be open for remote connections.
• Database User Permissions: The user must have the correct privileges to connect from a remote host, dump, and restore databases.

#!/bin/bash

# Define remote database details
REMOTE_HOST="remote_server_ip_or_domain"
REMOTE_DB_NAME="remote_database_name"
REMOTE_DB_USER="remote_db_user"
REMOTE_DB_PASSWORD="remote_db_password"

# Define local database details
LOCAL_HOST="localhost"  # or IP of the destination MySQL server
LOCAL_DB_NAME="local_database_name"
LOCAL_DB_USER="local_db_user"
LOCAL_DB_PASSWORD="local_db_password"

# Temporary file to store the database dump
DUMP_FILE="db_backup_$(date +%F).sql"

echo "----------------------------"
echo "Starting Database Transfer Process"
echo "----------------------------"

# 1. Dump the remote database using the MySQL credentials
echo "Dumping the remote database..."
mysqldump -h $REMOTE_HOST -u $REMOTE_DB_USER -p$REMOTE_DB_PASSWORD $REMOTE_DB_NAME > $DUMP_FILE
if [ $? -ne 0 ]; then
    echo "Error: Failed to dump the remote database"
    exit 1
fi
echo "Remote database dumped successfully."

# 2. Restore the dump file to the local database (or another remote database)
echo "Restoring the database on the destination server..."
mysql -h $LOCAL_HOST -u $LOCAL_DB_USER -p$LOCAL_DB_PASSWORD $LOCAL_DB_NAME < $DUMP_FILE
if [ $? -ne 0 ]; then
    echo "Error: Failed to restore the database on the destination server"
    exit 1
fi
echo "Database restored successfully on the destination server."

# 3. Clean up by removing the dump file
echo "Cleaning up..."
rm $DUMP_FILE
if [ $? -ne 0 ]; then
    echo "Error: Failed to remove dump file."
else
    echo "Clean up completed successfully."
fi

echo "----------------------------"
echo "Database transfer and restoration completed."
echo "----------------------------"

Explanation of the Script:

1. Dump the remote database:
   • The script uses mysqldump with the MySQL user and password to directly connect to the remote MySQL database and dump the data into a file.
   • mysqldump -h $REMOTE_HOST -u $REMOTE_DB_USER -p$REMOTE_DB_PASSWORD $REMOTE_DB_NAME > $DUMP_FILE
     This command connects to the MySQL server at $REMOTE_HOST, authenticates using the provided credentials, and dumps the specified database into a file.
2. Restore the dump to the local or another remote database:
   • The script uses the mysql command to restore the dump to the local or destination server:
   • mysql -h $LOCAL_HOST -u $LOCAL_DB_USER -p$LOCAL_DB_PASSWORD $LOCAL_DB_NAME < $DUMP_FILE
     This command connects to the MySQL server at $LOCAL_HOST and restores the database using the dump file.
3. Clean up:
   • The script removes the dump file once the process is complete.