/Main_Page

::You must have ninja focus to complete your mission::NinjaFocus::

Mysql-backup-script

Views:


These scripts will backup all of the databases which happen to be present on your MySQL server, either on Windows or on Linux.

Contents

MySQL User Account and Permissions

I don't like leaving credentials for a super user lying around in a script. You can set the permissions you need for backups with this:

GRANT SELECT, LOCK TABLES ON `mysql`.* TO 'backup_user'@'localhost' IDENTIFIED BY 'backup_password';
GRANT USAGE ON *.* TO 'backup_user'@'localhost' IDENTIFIED BY 'backup_password';
USE mysql;
UPDATE User SET Select_priv ='Y', Lock_tables_priv ='Y', Show_db_priv ='Y', Show_view_priv = 'Y' where User ='backup_user' AND Host = 'localhost';
FLUSH PRIVILEGES;

Linux MySQL Backup Script

#!/bin/bash
#shell script to backup MySql database 
# To backup mysql databases file to a dir and later pick up by your 
# script. You can skip few databases from backup too.
# For more info please see (Installation info):
# http://www.cyberciti.biz/nixcraft/vivek/blogger/2005/01/mysql-backup-script.html 
# Last updated: Aug - 2005
# THIS IS NOT AN ORIGINAL COPY - IT HAS BEEN MODIFIED!
# GOTO NIXCRAFT TO GET AN ORIGINAL COPY
# Modified by Kieran Whitbread in 2007
# --------------------------------------------------------------------
# This is a free shell script under GNU GPL version 2.0 or above
# Copyright (C) 2004, 2005 nixCraft project
# Feedback/comment/suggestions : http://cyberciti.biz/fb/
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# -------------------------------------------------------------------------
#

MyUSER="backup_user"     # USERNAME
MyPASS="backup_password"       # PASSWORD 
MyHOST="localhost"          # Hostname

# Linux bin paths, change this if it can't be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"

# Backup Dest directory, change this if you have someother location
DEST="/var/backup"

# Main directory where backup will be stored
MBD="$DEST/mysql"

# Get hostname
HOST="$(hostname)"

# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"

# File to store current backup file
FILE=""
# Store list of databases 
DBS=""

# DO NOT BACKUP these databases
IGGY="test"

[ ! -d $MBD ] && mkdir -p $MBD || :

# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST

# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"


for db in $DBS
do
    skipdb=-1
    if [ "$IGGY" != "" ];
    then
        for i in $IGGY
        do
            [ "$db" == "$i" ] && skipdb=1 || :
        done
    fi

    if [ "$skipdb" == "-1" ] ; then
        #FILE="$MBD/$db.$HOST.$NOW.gz" #modified this so that only one db file is stored on disk, i.e we don't want to append the date to each file name. uses less disk this way
        FILE="$MBD/$db.$HOST.sql.gz"
        # do all inone job in pipe,
        # connect to mysql using mysqldump for select mysql database
        # and pipe it out to gz file in backup dir :)
        $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
    fi
done

Thanks to NixCraft

Windows MySQL Backup Script

Here's a batch files that will automatically backup all of the mysql databases on a windows server. The only thing it's missing at the moment is something to remove backups more than, for example, a week old.

I couldn't find a batch file that can both save multiple versions and also save all of the databases automatically, so this is a little something I cooked up.

@ECHO OFF

REM Copyright Kieran Whitbread 2009
REM Based on the work of numerous others
REM ============================
REM == SETTINGS ================

SET BACKUPS_DIR=C:\MySQL-Backups
SET MYSQL_DIR=%PROGRAMFILES%\MySQL\MySQL Server 5.1\bin
SET USER="backup_user" 
SET PASSWORD="backup_password"    

REM ============================

SET OLDDIR=%CD%
CD %TEMP%

REM The date in ISO format, to be appended to backup filenames.
REM (Working from a UK formatted date)
SET TODAY=%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2%

REM get a list of all databases hosted on the server
"%MYSQL_DIR%\mysql" -u %USER% -p%PASSWORD% -B -s -e"show databases" > mysqldblist.tmp

FOR /F %%D IN (mysqldblist.tmp) DO (
    ECHO Creating backup for database ''%%D''
    "%MYSQL_DIR%\mysqldump" -u %USER% -p%PASSWORD% --result-file="%BACKUPS_DIR%\%%D.%TODAY%.sql" "%%D" 
)

DEL mysqldblist.tmp

CD %OLDDIR%

Create a scheduled task to run the backup each night at 2a.m:

C:\ > AT 02:00 /every:M,T,W,Th,F,S,Su C:\<path>\mysql-backup.cmd

An Old Script

/bin/mysql-backup-script

This is run every night via a cron job. It is a simple bash script with an array of database names. Each database named in the array is backed-up with mysql-dump and gzipped. 7 days of backup are kept for each database.

Databases are not backed-up automatically you must edit the file and add databases to the list.

The backups are stored in /var/lib/mysql-backups

The script also flushes the tables in order to flush the query cache (note1: this doesn't clear the cache, so much as defragment it. note2: yes, this probably isn't the right place to add that command )

Enyo runs a script (via cron) to up backup the databases each night. Any databases which need to be backed up must be added to the "databases" array near the top of the script.

At the moment 7 days worth of backup are kept in /var/mysql-backups/<database name>/ .These are regularly backed up to tape, offering 5 weeks of database backups - See the next section.

#!/bin/bash
#
# DESCRIPTION:
# /bin/mysql-backup-script
# kjw
#
# Keeps 7 days of backups for local mysql databases. Also, the query cache is
# flushed to stop fragmentation.
#
# Needs a user account in mysql which has global SELECT, RELOAD and LOCK TABLE privilages.
# No other privilages should be given to the account!!
#

# SETTINGS:
backup_directory="/var/mysql-backups"
mysql_user="backup"
password="*************"
# Array of database names to backup
databases[0]="mysql"
databases[1]="varndeandyn"

####################################################
####################################################
####################################################

todays_date=`date +%G%m%d`
numberOfDBs=${#databases[@]}
count=0
while [ $count -lt $numberOfDBs ] # Loop through the array of database names
do
        # Is there a sub directory to store backups from this database?
        if [ -d ${backup_directory}/${databases[$count]} ]
        then
                # Directory exists, there might be some old backups we don't care about any more
                # delete any backups that are more than 7 days old
                find $backup_directory/${databases[$count]} -mtime +7 -iname ${databases[$count]}.????????.sql.gz -exec rm -f {}
        else
                # Directory doesn't exist, so create one
                mkdir ${backup_directory}/${databases[$count]}
                chgrp it-sup ${backup_directory}/${databases[$count]}
                chmod 0770 ${backup_directory}/${databases[$count]}
        fi
        # create a new backup for today
        /usr/bin/mysqldump -u $mysql_user -p${password} --database ${databases[$count]}|gzip -9 > ${backup_directory}/${databases[$count]}/${databases[$count]}.${todays_date}.sql.gz
        # set appropriate permissions
        chgrp  it-sup ${backup_directory}/${databases[$count]}/${databases[$count]}.${todays_date}.sql.gz
        chmod 0660 ${backup_directory}/${databases[$count]}/${databases[$count]}.${todays_date}.sql.gz
        # Increment the counter so we move on to the next database
        (( count++ ))
done

# Flush (defragment) the MySQL Query Cache
/usr/bin/mysql -u $mysql_user -p${password} -e "flush query cache;"

# Just incase something naughty is appended to the file
exit 0 # Bye! :)

see also Enyo Backup

Main Menu

Personal tools

Toolbox