Daily mysql databse backup with datetime stamp with cronjob

Creating MySQL Backup Script

First we need to create a script with below content called “daily-mysql-backup.sh” and save it on your system. Please change the below value as per your requirements. for root user "--no-tablespaces" is not required, for remote host we need to add extra parameter -h192.168.5.10 after -p parameter.

#!/bin/bash
USER="dbuser"
PASS="dbpass"
mysqldump -u$USER -p$PASS database_name --no-tablespaces | gzip -c > /home/jafar/db_backups/database_name_$(date "+%b_%d_%Y_%H_%M_%S").sql.gz

Now we need to set the cronjob for the daily backup, we will run the backup at 2AM every night.  Use the below command and add the corn job to the file and save the crontab reload or restart the cron service with last command

crontab -e

00 02 * * * /bash_scripts/daily-mysql-backup.sh

systemctl reload cron ##for ubuntu based system

systemctl reload crond ##for redhat based system