This script will take the backup of mysql databases.Additionally the script will compress the backup using gzip and also capture the backup time. You need to create the following table for capturing the backup time.

CREATE TABLE `mysql`.`backupinfo` ( `idback` int(10) unsigned NOT NULL AUTO_INCREMENT, `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `end_time` datetime DEFAULT NULL, `size` bigint(20) unsigned DEFAULT NULL, `type` enum('TEXT','RAW') DEFAULT NULL, PRIMARY KEY (`idback`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Use comments are welcome.

#!/bin/bash
 
bkDate=`date +%d%m%Y` #Backup Date
 
bkFolder="/home/soma/$bkDate/" #Backup destination and its your choice.
 
mkdir $bkFolder #Create a directory for that day's backup
 
cp /etc/my.cnf $bkFolder #Copy the my.cnf file
 
#Flushing the logs and capturing the backup start time.
#Capturing backup time is just for information.
#Since this is a textual backup i am storing the backup type as text.
 
#ToDO: Need to copy the relavent binary logs.
 
mysql -pBackUp@123 -u backup -e "FLUSH LOGS;INSERT INTO mysql.backupinfo ( type ) VALUES ( 'TEXT' );"
 
#Taking backup of all databases other that mysql and information_schema.
 
for db in `mysql --skip-column-names -u backup -pBackUp@123 -s -e "SELECT schema_name FROM information_schema.SCHEMATA WHERE schema_name NOT IN ( 'mysql','information_schema')"`
 
do
        mkdir $bkFolder$db; #Creating a new directory for each database.
        mysqldump -d --triggers -u backup -R -E -pBackUp@123 -q --databases $db | gzip > $bkFolder$db/$db.sql.gz #I am using gzip to compress the backup.
 
done
 
#Finally I am storing the end time and size of the backup.
 
mysql -u backup -e "UPDATE mysql.backupinfo SET end_time = NOW(), size = TRIM(REPLACE(REPLACE('`du -s`','       ',''),'.','')) WHERE type = 'TEXT' AND DATE(start_time) = DATE(NOW())" -pBackUp@123
 

Current Tags

You must be logged in to tag this tool

No Comments yet