Replicating a Remote MySQL Database to Local Environment / Server

If you deal with multiple server environments for your project or company, it can be difficult to make sure the environments are equally matched. Take, for example, the situation where you want to run some tests on a production database, but without affecting the production data itself. This is where replication can help.

The Process

The below bash script can dump a remote database (e.g. your production database), and replicate it to a local environment or server (or even a different remote server). It does this in the following order:

1. Connect to the remote database and dump the structure and data (using mysqldump utility)
2. Connect to the local database and drop the tables in a given database
3. Run the SQL dump from Step 1 onto local database, and thus making a copy of the remote database

The Script

Configuration

The script can easily be configured using the variables at the top of the script. These should be self-explanatory, and comments haven been included in the script to give you some more information. However, if you need assistants, feel free to leave a comment below.

Running the Script

Assuming you’ve saved the script to file replicate_db.sh and added the appropriate permissions to execute the file (e.g. chmod 755 replicate_db.sh), you can run the script using:

./replicate_db.sh

The script doesn’t output anything if it runs successfully, but it will output errors if something goes wrong. Errors you may see include MySQL connection errors, user / password errors etc. If you can’t connect to the remote database:

• make sure you have remote access to the database – the MySQL setup on remote host may only allow local connections
• make sure the host / IP / port / username / password / database name are correct for the remote and local connections
• make sure the firewall allows remote connections to the database / port

Limitations

If your particular use-case if more specific, the script may not be suitable for your needs. For example, this script assumes you are using the default MySQL port 3306. If this is not the case in your setup, you’ll need to modify the relevant parts of the script to alter the ports.