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
mkdir -p /script/sync
Now cd to the new directory and create a new shell script
cd /script/sync nano mysql_uploader.sh
Add the following line to it
mysqldump -u root -p12345 --all-database | gzip -9 > backup.sql.gz scp /script/sync_mysql/backup.sql.gz root@188.166.227.100:/sync ssh root@188.166.227.100 "gunzip < /sync/backup.sql.gz | mysql -u root -p12345"
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
chmod +x mysql_uploader.sh
Step 3. Run the Script
At this point, the script should be ready. We can start using it by typing the following command
sh mysql_uploader.sh
You can modify the script and tweak it to meet your current configuration.
