Wednesday, October 18, 2017

Mysql weekly backup script


[mysql@localhost]$ cat mysqlweekly.sh
# It takes  mysqldump  all DBS on the servrer on Sunday
# All other days it takes backup of binary log files
#It keeps backups on same server. Once script is verified, scripts can be configured to copy backups into a remote# server.
#!/bin/bash
# direcory initiations
DBUSER='backupuser'
DBPASS='somepassword'
MYSQLCMD="/usr/bin/MySQL"
MYSQLDMP="/usr/bin/mysqldump"
FULLBACK="/backup/FULL"
STATUSFILE="/var/logs/mysql/stat.txt"
# Create non existing directories
[ ! -d $FULLBACK ] && mkdir -p $FULLBACK
mkdir -p /var/logs/mysql
# Function to  take full backup of database
fullback(){
  # Remove two  weeks old backups.
  find $FULLBACK  -mtime +14 -type f -exec rm -f {} \;
  rm -f /opt/logs/log.txt
  # Store all  default DBs into  a variable
  IGNORDB="
   performance_schema
   information_schema
   lost+found
   test
   mysql
   "
# Store all  DBs on the server to a variable
   DBL=$(mysql -u$DBUSER -p$DBPASS -Bse 'show databases')
   for db in $DBL
   do
      CH="yes"
      # Check DB is default one
      if [ "$IGNORDB" != "" ]; then
         for i in $IGNORDB
            do
              if [ "$i" == "$db" ]; then
               CH="no"
              fi
         done
      fi
      if [ "$CH" == "yes" ]; then
                    $MYSQLDMP  --lock-all-tables -u$DBUSER -p$DBPASS  $db  | gzip > $FULLBACK/$db$(date +"%m-%d-%y").sql.gz
            if [ "$?" -eq "0" ]
            then
              echo "$(date) $db backup for is OK" >> $STATUSFILE
            else
               echo " $(date) ##### WARNING: #####  $db backup failed" >> "$$STATUSFILE"
            fi
      fi
   done


  }


main (){
            fullback
  }
main

No comments:

Post a Comment