You have learned “How to use SSH to access and manage MySQL database” in the previous article. Take a step further and you are about to automate the database backup process.
I hope you still remember how to dump a mysql database?
# mysqldump -u MYSQL_USERNAME -pMYSQL_PASSWORD DATABASE_NAME > backup.sql
This command will dump your mysql database to backup.sql. Now, how to make it automatic backup the database every week? All you need is put the command into a shell script, and use a cron job to automate it.
Create a blank shell script
# cd /usr/local/bin # sudo touch doBackup.sh
The command first change your directory to /usr/local/bin and then created a blank shell script “doBackup.sh”
Edit the shell script
# sudo nano doBackup.sh
Paste the following code into the shell script
#!/bin/bash ssh_User="ssh_username" ssh_Host="ssh_host" sql_User="sql_username" sql_Pass="msql_password" # Backup destination directory DEST="/Users/GeekyDuck/db_backups" # Get data in dd-mm-yyyy format NOW="$(date +"%d-%m-%Y")" # Database name to backup DB ="mydatabase" # Generate the backup path FILE="$DEST/$DB.$NOW.sql" ssh [email protected]$ssh_Host mysqldump -u $sql_User -h $sql_Host -p$sql_Pass $DB > $FILE
Setup a Cron Job
# crontab -e
This command will open the cron tab editor. Add the line below to create a weekly cronjob that execute the dbBackup.sh
0 0 * * 0 /usr/local/bin/dbBackup.sh