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  backup.sh. open backup.sh in nano editor

nano backup.sh

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

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

#!/bin/bash

# Basic configuration: datestamp e.g. YYYYMMDD

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

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

BACKUP_DIR="/backup/mysql"

# MySQL login details

MYSQL_USER="root"
MYSQL_PASSWORD="YOURSECUREPASSWORD"

# MySQL executable locations (no need to change this)

MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump

# MySQL databases you wish to skip

SKIPDATABASES="Database|information_schema|performance_schema|mysql"

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

RETENTION=14

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

databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "($SKIPDATABASES)"`

# 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"
done

# Remove files older than X days

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

create backup directory

mkdir /backup/mysql/

Step 2: change script permission

Give execution permission to script.

chmod 755 backup.sh

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

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>