How to create a cron job for database backup using mysqldump

Where necessary, you may need to have access to a VPS server so you can follow how to implement the steps in this article.  You can get a cheaper VPS Server from Contabo with 4vCPU cores, 8GM RAM, and 32TB Bandwidth for less than $5.50 per month. Get this deal here now

Table of Contents

Cloud VPS S

$5.50 Monthly
  • 4 vCPU Cores | 8GB RAM


$15.50 Monthly
  • 6 vCPU Cores | 16GB RAM


$17.50 Monthly
  • 8 vCPU Cores | 24GB RAM

mysqldump is a command-line utility and it’s used to create backups of MySQL databases. It allows you to generate a set of SQL statements that can be used to recreate the database structure and data at a later point in time.

If you want the manual way of backing up using the mysqldump command, here is the sample command you would run;

mysqldump -u username -p password dbname > backup.sql

A database file named backup.sql will be created as a backup file.

However, databases need to be constantly backed up, especially for large systems with changing or dynamic data. This is where you need to automate this process using a cron job and here is how it’s done;

Create a shell script for the backup

Using your preferred text editor on Linux, you need to create a file that will contain the instructions to automate the mysqldump backup process.

In this example, our file is named So, we need to add the following lines in the file we’ve created;


   # Set the date for backup file
   backup_date=$(date +"%Y%m%d_%H%M%S")

   # MySQL/MariaDB credentials

   # Backup destination directory

   # Create the backup using mysqldump
   mysqldump -u"$db_user" -p"$db_password" "$db_name" > "$backup_dir/backup_$backup_date.sql"

   # Compress the backup (optional)
   gzip "$backup_dir/backup_$backup_date.sql"

In this file, you will need to fill in the following details;

  • db_user: Make sure you put your database user
  • db_password: Put your database user password
  • db_name: This is the name of your database.
  • backup_dir: This is the directory path where you want your backup file to be saved

Add executable permission

On the script, we’ve created, we need to make it executable by adding the following permissions;

chmod +x

Test the script

The next step is to run the script manually to ensure that it performs the backup correctly. Here is the command and make sure you’re in the directory where the script is located


Confirm that the backup file is created in the specified directory.

Schedule the script with a cron job

We need to use the cron job to help us automate the schedule when the script is supposed to run the backup based on the defined interval

Open the crontab file

To add the cron task we need, we need to open the crontab file using the crontab -e command:

crontab -e

Add a line to schedule the backup script. For example, to run the backup every day at 2:00 AM:

0 2 * * * /path/to/

Save the changes and exit the editor.

This cron job will execute the at 2:00 AM every day, creating a new backup file with a timestamp in the specified directory. Adjust the paths and schedule according to your requirements. Also, make sure to keep the database credentials secure, and consider encrypting or securing the backup files if needed.

Automatically send the backup to a remote server

To automate exporting the backed-up file to a remote server, you can use a tool like scp (secure copy) in your backup script to transfer the file to the remote server. Here’s an updated version of the backup script that includes the export to a remote server:


# MySQL Database Information

# Backup Directory

# Remote Server Information

# Date format for backup file
DATE=$(date +"%Y%m%d_%H%M%S")

# mysqldump command
mysqldump -u $DB_USER -p$DB_PASSWORD $DB_NAME > $BACKUP_DIR/backup_$DATE.sql

# Transfer backup file to remote server using scp

# Remove local backup file (optional, if you want to save space)
rm $BACKUP_DIR/backup_$DATE.sql

Make sure to replace placeholders such as your_username, your_password, your_database_name, remote_username, remote_server_address, and file paths with your actual information.

This script will create a MySQL database backup locally and then transfer it to the specified remote server using scp. Adjust the paths, server information, and any other parameters according to your setup.

Considerations of the Remote Backup Setup

If you want your backup to be sent to a remote server, here are the important considerations to keep in mind;

  • Consider setting up SSH keys for passwordless authentication between the machines to avoid entering a password during the scp process.
  • Ensure that the user running the cron job has the necessary permissions to read from the local backup directory and write to the remote backup directory.

Hire us to handle what you want

Hire us through our Fiverr Profile and leave all the complicated & technical stuff to us. Here are some of the things we can do for you:

  • Website migration, troubleshooting, and maintenance.
  • Server & application deployment, scaling, troubleshooting, and maintenance
  • Deployment of Kubernetes, Docker, Cloudron, Ant Media, Apache, Nginx,  OpenVPN, cPanel, WHMCS, WordPress, and more
  • Everything you need on AWS, IBM Cloud, GCP, Azure, Oracle Cloud, Alibaba Cloud, Linode, Contabo, DigitalOcean, Ionos, Vultr, GoDaddy, HostGator, Namecheap, DreamHost, and more.

We will design, configure, deploy, or troubleshoot anything you want. Starting from $10, we will get your job done in the shortest time possible. Your payment is safe with Fiverr as we will only be paid once your project is completed.