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>