If you are looking for a script written to backup your mysql databases to individual files seperated by date then the code below will help you out. Keep in mind that the script has been designed to run on a daily basis. All files under the backup directory are stamped with day and time to keep from writing over your old backups. Each time the script is executed, it looks for any directory that is older than the retention period variable. This allows for keeping your partition slice from over flowing. I might incorporate some type of size detection, but that might be for a future release of this script.

the script

    # backup all mysql databases to /usr/sqlbackup/YYYYMMDD/databasename.sql files
    echo "=== starting on `$curDate` ==="
    todayDate=`/bin/date +%Y%m%d-%H%M%S`
    curDate="/bin/date +%Y%m%d-%H%M%S"
    backupDirectory="/usr/sqlbackup/`/bin/date +%Y%m%d`"
    dbList=`/usr/bin/mysql -u root -pSECRET -Bse 'show databases'`
    mysqlDump="/usr/bin/mysqldump --user="root" --password=SECRET"
    # mkdir for backup files
    echo "** testing existance of backup directory of ${backupDirectory}"
    if [ -d $backupDirectory ] ; then
      echo "**+ backup directory of ${backupDirectory} exists"
      echo "**+ skipping command to create backup directory"
      echo "**+ backup directory of ${backupDirectory} does not exists"
      echo "**+ creating backup directory"
      mkdir -p $backupDirectory
    # test if backup directory exists
    if [ -d $backupDirectory ] ; then
      echo ""
      echo "!!! backup directory does not exist !!! aborting backup process !!!"
      exit 1
    # start dump
    echo "**** starting mysqldump cmd of all databases on `$curDate`"
    for db in $dbList
      echo "***** starting dump of database $db"
      $mysqlDump $db > $backupDirectory/$db.sql
      echo "***** end dump of database $db"
    # look for files in archive directory and remove any that are older than 12*24hrs from current date of script starting
    echo "**** looking for files older than (12*24hrs) from `$curDate`"
    for file in "$( /usr/bin/find /usr/sqlbackup -type f -mtime +12 )"
      echo "****** removing files older than `$curDate`"
      /bin/rm -f $file
    echo "**** end of looking for files older than 12 days"
    echo "=== end mysqldump script on `$curDate` ==="
    exit 0
comments powered by Disqus