Kousik Chatterjee's Blog

Step: 1. First We need to Create the Following Directory Structure :

# mkdir -p /Backups/DB_backups
# mkdir /Backups/scripts

Step: 2. Create a Read-Only MySQL User Called "backupoperator" :

Note: Don't use "root" user.

-- Make Sure You have given the Right Privileges to "backupoperator" to take Backup.

# mysql -u root -p
Enter the Password:

mysql> grant select on *.* to backupoperator@localhost identified by 'backupoperator_password';
mysql> flush privileges;
mysql> quit

Step: 3. Creating MySQL Database Backup Shell  Script under "scripts" Directory :

Note: Backup Retention is 3 days. 3 days older file will deleted automatically.

# vi /Backups/scripts/mysql_backup_script.sh

-- Paste the Below Codes :

#!/bin/bash
export path1=/Backups/DB_backups
date1=`date +%y%m%d_%H%M%S`
# Set Backup Retention. Here Backup Retention is 3 days.
/usr/bin/find $path1/* -type d -mtime +3 -exec rm -r {} \; 2> /dev/null
cd $path1/
mkdir $date1
USER="backupoperator"
PASSWORD="backupoperator_password"
OUTPUTDIR="$path1/$date1"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
databases=`$MYSQL --user=$USER --password=$PASSWORD \
 -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
echo "` for db in $databases; do
    echo $db
if [ "$db" = "performance_schema" ] ; then
       $MYSQLDUMP --force --opt --single-transaction --lock-tables=false --skip-events  --user=$USER --password=$PASSWORD \
    --databases --routines $db > "$OUTPUTDIR/$db.sql"
         else
$MYSQLDUMP --force --opt --single-transaction --lock-tables=false --events  --user=$USER --password=$PASSWORD \
    --databases --routines $db > "$OUTPUTDIR/$db.sql"
fi
done `" 2> /Backups/Logs/error_$date1.log

-- Save & Quit (:wq)

Step: 4. Now, Give the Executable Permission :

# chmod 755 /Backups/scripts/mysql_backup_script.sh

Step: 5. To Execute the Script :

# cd /Backups/scripts/
# ./mysql_backup_script.sh

Step: 6. Schedule in Crontab :

# crontab -e

30 2 * * * /Backups/scripts/mysql_backup_script.sh > /dev/null

-- Save & Quit (:wq)

Note: It will take backup automatically every day at 2:30 AM.

Thanks For Visiting on My Blog, For More Tutorials Keep Visiting My Blog