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/shnow=$(date +'%d_%m_%Y_%H_%M_%S')filename=db_backup_$now.gzbackupfolder=/home/backupsfullpathbackupfile=$backupfolder/$filenamelogfile=$backupfolder/backup_log_$(date +'%Y_%m').txtecho mysqldump started at $(date +'%d-%m-%Y %H:%M:%S') >> $logfilemysqldump --user=userbackup --password=***** --default-character-set=utf8 database | gzip > $fullpathbackupfileecho mysqldump finished at $(date +'%d-%m-%Y %H:%M:%S') >> $logfilefind $backupfolder -name db_backup_* -mtime +7 -exec rm {} \;echo old files deleted >> $logfileecho operation finished at $(date +'%d-%m-%Y %H:%M:%S') >> $logfileecho ***************** >> $logfileexit 0This 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?
Script for backup each DB on server but omit system databases
bash;shell script;scripting;cron;backup
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 instanceUSER=postgresPWD=pwd_in_plaintext_is_not_a_## Just the date string that will be appended to the backup files nameBACKUP_DATE=$(date +'%d_%m_%Y_%H_%M_%S')## Numbers of days you want to keep copie of your databasesNUMBER_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 backupvacuumdb --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  fidonefind ${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 linedo     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 ]; thenHope this helps