MySQL is a freely available open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). We may host website in our server without using a panel like cwp,cpanel etc. For such situation it is much more important to take backup of databases and website.

Here we are taking backup of mysql database with the  help of a simple script.

Step 1: create script and put it in /usr/local/sbin/

create  a file called open in nano editor


copy the below script and paste in and save. Don't forget to change mysql username and password.

Copy and put it in the folder /usr/local/sbin/.


# Basic configuration: datestamp e.g. YYYYMMDD

DATE=$(date +"%Y%m%d")

# Location of your backups (create the directory first!)


# MySQL login details


# MySQL executable locations (no need to change this)


# MySQL databases you wish to skip


# Number of days to keep the directories (older than X days will be removed)


#---- DO NOT CHANGE BELOW THIS LINE ------------------------------------------

# Create a new directory into backup directory location for this date

mkdir -p $BACKUP_DIR/$DATE

# Retrieve a list of all databases


# Dumb the databases in seperate names and gzip the .sql file

for db in $databases; do
echo $db
$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --skip-lock-tables --events --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"

# Remove files older than X days

find $BACKUP_DIR/* -mtime +$RETENTION -delete
cp /usr/local/sbin/

create backup directory

mkdir /backup/mysql/

Step 2: change script permission

Give execution permission to script.

chmod 755

Step 3: Create a cron job

create a cronjob  to schedule a backup every night at 1 am

vi /etc/crontab

0 1 * * * root /usr/local/sbin/

save and restart cron

service cron restart

Every night, the script will add a backup of all your databases in the folder /backup/mysql/YYYYMMDD/<yourdatabase dump>