Hi! This project contains 2 scripts which I'm using to synchronize my wordpress docker-based projects databases in two directions:
- sync local db (pull changes from production database)
- sync production db (pull changes from local db to production)
- Makefile file with 2 commands to run:
make sync_local_db
make sync_remote_db
- scripts/ folder which contains:
.env
sync-local-db-from-production.sh
sync-production-db-from-local.sh
SSH_LOGIN
: Your SSH login username for connecting to the remote server.SSH_SERVER
: The hostname or IP address of the remote server you want to connect to via SSH.LOCAL_DB_USER
: The username of the local MySQL/MariaDB database.LOCAL_DB_PASSWORD
: The password of the local MySQL/MariaDB database.LOCAL_DB_NAME
: The name of the local MySQL/MariaDB database.MARIADB_CONTAINER_NAME
: The name of the Docker container running MySQL/MariaDB locally.REMOTE_DB_HOST
: The hostname or IP address of the remote MySQL/MariaDB database server.REMOTE_DB_USER
: The username of the remote MySQL/MariaDB database.REMOTE_DB_PASSWORD
: The password of the remote MySQL/MariaDB database.REMOTE_DB_NAME
: The name of the remote MySQL/MariaDB database.REMOTE_ADMIN_USER
: The username used for administrative tasks on the remote server, if applicable.LOCAL_HOST
: The hostname or IP address of your local machine.LOCAL_PORT
: The port number where your local server is running (e.g., for a web server).REMOTE_URL
: The URL of the remote server, which may be used for updating URLs in WordPress or similar applications.
Run:
make sync_local_db
- Loads environment variables from a
.env
file containing database and server configurations.
- Prompts the user to enter a short description for the migration.
- Creates a directory structure for storing backups based on the current date and time.
- Creates a backup of the local MySQL database running in a Docker container.
- Saves the backup to a file with a timestamp in the backup directory.
- Dumps the remote MySQL database directly to a file on the remote server using SSH.
- Excludes the
wp_users
table and saves the dump to a file nameddatabase_dump.sql
on the remote server.
- Transfers the database dump file from the remote server to the local machine using SCP.
- Saves the file in the script's directory.
- Imports the database dump file into the local MySQL database running in a Docker container.
- Executes the SQL file containing the database dump in the local MySQL container.
- Updates URLs in the
wp_posts
andwp_options
tables of the local database to replace remote URLs with local URLs.
- Removes the database dump file from the remote server.
- Moves the remote dump file to the backup directory.
- Logs migration details, including the migration description, timestamp, and updates to a migration log file in the backup directory.
- Prints a message indicating that the migration is complete.
Run:
make sync_remote_db
- Retrieves the directory of the script and loads environment variables from a
.env
file containing database and server configurations.
- Prompts the user to enter a short description for the migration.
- Creates a directory structure for storing backups based on the current date and time.
- Dumps the local MySQL database running in a Docker container.
- Excludes the
wp_users
table and saves the dump to a file namedlocal_database_dump.sql
in the backup directory.
- Transfers the database dump file to the remote server using SCP.
- Dumps the remote MySQL database directly to a file on the remote server using SSH.
- Excludes the
wp_users
table and saves the dump to a file nameddatabase_dump.sql
on the remote server.
- Connects to the SSH server and imports the database dump into the remote MySQL database.
- Updates URLs in the
wp_posts
andwp_options
tables of the remote database. - Cleans up temporary files after the import process.
- Logs migration details, including the migration description, timestamp, and updates, to a migration log file in the backup directory.
- Prints a message indicating that the migration is complete.