0

I have read a lot of post around this topic but I didn't found the one to fit my needs. So, basically I want to make two backups: one at mid day (12 PM) and the other at midnight (12 AM) for each database on a MySQL server but I want to leave out system databases: mysql and information_schema (as far as I know is there is another one please let me know). After read a lot of topics I come with this bash script:

#!/bin/sh
now="$(date +'%d_%m_%Y_%H_%M_%S')"
filename="db_backup_$now".gz
backupfolder="/home/backups"
fullpathbackupfile="$backupfolder/$filename"
logfile="$backupfolder/"backup_log_"$(date +'%Y_%m')".txt
echo "mysqldump started at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
mysqldump --user=userbackup --password=***** --default-character-set=utf8 database | gzip > "$fullpathbackupfile"
echo "mysqldump finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
find "$backupfolder" -name db_backup_* -mtime +7 -exec rm {} \;
echo "old files deleted" >> "$logfile"
echo "operation finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
echo "*****************" >> "$logfile"
exit 0

This script made a backup for database database and keep 7 last .tar.gz files. Can any help me to improve this script so I can backup each database other than system ones and keep 7 last copies for each?

1

I'm currently using PostgreSQL, and I do something that looks really close to what you want to achieve, so this is my backup script:

#!/bin/bash
#
#------------------------------------------------------------------------------
# Editable parameters:
#
## Filesystem Location to place backups.
BACKUP_DIR="/path/to/backup/folder"
## The user used to connect to postgres instance
USER="postgres"
PWD="pwd_in_plaintext_is_not_a_"
## Just the date string that will be appended to the backup files name
BACKUP_DATE="$(date +'%d_%m_%Y_%H_%M_%S')"
## Numbers of days you want to keep copie of your databases
NUMBER_OF_DAYS=7
## Uncomment following line if you want to overwrite the whole folder each time
#rm -rf ${BACKUP_DIR}/backupFulldb-*
#
#------------------------------------------------------------------------------
# don't change anything below this line

# Vacumm al databases before begin to backup
vacuumdb --all -U ${USER}

DATABASES=`psql -U ${USER} -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d'`
for i in ${DATABASES}; do
  if [ "$i" != "template0" ] && [ "$i" != "template1" ]; then
    echo Dumping $i to ${BACKUP_DIR}
    pg_dump -U ${USER} --column-inserts $i | gzip -c >  ${BACKUP_DIR}/backupFulldb-$i-${BACKUP_DATE}.out.gz
  fi
done
find ${BACKUP_DIR} -type f -prune -mtime +${NUMBER_OF_DAYS} -exec rm -f {} \;

You just need a query that list all dbs in your mysql instance, and replace it for the DATABASES array. Reading this post and this one, I assume you could eg. do as follow:

while read line
do 
    DATABASES+=("$line")
done < <(mysql -u${USER} -p${PWD} INFORMATION_SCHEMA -e "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA")

And sure, fix the dbs names you want to exclude:

if [ "$i" != "mysql" ] && [ "$i" != "information_schema" ]; then

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.