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 backup_script.sh. So, we need to add the following lines in the file we’ve created;

   #!/bin/bash

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

   # MySQL/MariaDB credentials
   db_user="your_username"
   db_password="your_password"
   db_name="your_database_name"

   # Backup destination directory
   backup_dir="/path/to/backup/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, backup_script.sh we’ve created, we need to make it executable by adding the following permissions;

chmod +x backup_script.sh

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

./backup_script.sh

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/backup_script.sh

Save the changes and exit the editor.

This cron job will execute the backup_script.sh 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:

#!/bin/bash

# MySQL Database Information
DB_USER="your_username"
DB_PASSWORD="your_password"
DB_NAME="your_database_name"

# Backup Directory
BACKUP_DIR="/path/to/backup/directory"

# Remote Server Information
REMOTE_USER="remote_username"
REMOTE_SERVER="remote_server_address"
REMOTE_DIR="/path/to/remote/backup/directory"

# 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
scp $BACKUP_DIR/backup_$DATE.sql $REMOTE_USER@$REMOTE_SERVER:$REMOTE_DIR

# 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.