How To Take MySQL Database Backup using Shell Script
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
0 comments:
Post a Comment