How to Create Individual zipped Backup routine for MySQL
What is the most important task you need to do in regards to your application ? I making sure you have a backup of it !
In this post we will show how to create script that will generate a zipped backup of each of our MySQL schema.1- Enable password-less log in from localhost
Create the file ".my.cnf" in you home folder and alter the permission on it as chmod 600 [mysqldump]
user = --user used to run backup
password = --password of the user
[MySQL]
user= --user used to run backup
password= --password of the user2- Create the script "zippedbkp" that will create individual zipped backups of your databases.
Make sure you alter the permission on it as "chmod 700 zippedbkp". You will need to create the directories where the backup will be held and make sure the used user to run the backup will have read & write rights on it. #!/bin/bash
#Script backup mysql individual sem senha
USER="root"
DIR="date | awk {'print $2"_"$3"_"$6'}"
PWDDIR="/home/backup_mysql/"
FINALDIR="$PWDDIR$DIR"
GZIP_ENABLED=1
GZIP="/bin/gzip"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
mkdir $FINALDIR
databases=$MYSQL --user=$USER -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"
for db in $databases; do
echo $db
if [ $GZIP_ENABLED == 1 ]; then
mysqldump --force --opt --user=$USER --databases $db | gzip "$FINALDIR/$db.gz"
else
mysqldump --force --opt --user=$USER --databases $db "$FINALDIR/$db.sql"
fi
done3- Create a crontab routine to automatize your backup.
0 1 * * * /mysql/scripts/zippedbkp