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.


#!/bin/bash

# Basic configuration: datestamp e.g. YYYYMMDD

DATE=$(date +"%Y-%m-%d-%H:%M")

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

BACKUP_DIR="/backup/mysql"

# MySQL login details

MYSQL_USER="root"
MYSQL_PASSWORD="Test@7376"

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



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

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

cp backup.sh /usr/local/sbin/

create backup directory

mkdir -p /backup/mysql/

Step 2: change script permission

Give execution permission to script.

cd /usr/local/sbin/

chmod 755 backup.sh

Step 3: Create a cron job

create a cronjob  to schedule  backups  in every 6 hours

vi /etc/crontab

0 */6 * * * root /usr/local/sbin/backup.sh

save and restart cron

systemctl reload crond.service

Thats all, in every 6 hours our script will generate backups in specified folder.