Backup local MySQL Databases to remote MySQL Server using Shell Script on Linux

July 14, 2016 | By | Reply More

On this tutorial, I am going to show you how to automate the MySQL databases backup from local computer to a remote server (a cloud computing) using a shell script. We can built our own script to automate this process and modify the code as we need. The idea here is to backup the local MySQL Databases using mysqldump command and then send the compressed databases to remote computer and then automate the database restore process on remote computer.

Configuration I use:

Source Computer

  • Local computer with MySQL/MariaDb Server
  • IP address: 10.34.0.10
  • Local root access
  • MySQL root privileges or any MySQL users that can perform dump or backup command

Remote Computer

  • Remote server which accessible via ssh
  • Remote Server root access
  • MySQL root privileges or any MySQL users that can perform database restore command
  • IP address: 188.166.227.100 (Digital Ocean Droplet)

The script workflow:

The script will perform a MySQL database backup using mysqldump command and then the backup file will be transferred/copied to the remote server. Then, after the upload completed, the remote server will decompress the backup file and then restore the database to the remote MySQL Server.

Now let’s get dirt

Step 1. Create a new shell script 

On the local computer, open Terminal and create a new directory for example /script/sync

Now cd to the new directory and create a new shell script

Add the following line to it

Explanation:

The first line will backup all the databases in the local MySQL Server and save the backup as backup.sql.gz under /script/sync directory.

The second line will copy the backup.sql.gz to the remote server via ssh connection and save to /sync folder in the remote server. And then the last line it will restore the backup.sql.gz file to the remote MySQL server.

Step 2. Make the script executable

Execute the following command to make the script executable

Step 3. Run the Script

At this point, the script should be ready. We can start using it by typing the following command

You can modify the script and tweak it to meet your current configuration.

Tags: , , ,

Category: Script

Leave a Reply