Meatwad 
geschrieben 14. Mai 2005 - 15:16
Mr_Maniac
geschrieben 14. Mai 2005 - 15:30
0 2 * * * /pfad/zu/mysqldump -h $NameOderIP -u $Username -p$Passwort --all-databases > /pfad/zur/backup.datei
/pfad/zu/mysql -h $NameOderIP -u $Username -p$Passwort < /pfad/zur/backup.datei
Floele
geschrieben 14. Mai 2005 - 15:32
#!/bin/bash # # MySQL Backup Script # VER. 2.2 - http://sourceforge.net/projects/automysqlbackup/ # Copyright (c) 2002-2003 [email protected] # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA # #================================================= ==================== #================================================= ==================== # Set the following variables to your system needs # (Detailed instructions below variables) #================================================= ==================== # Username to access the MySQL server e.g. dbuser USERNAME=user # Username to access the MySQL server e.g. password PASSWORD=pass # Host name (or IP address) of MySQL server e.g localhost DBHOST=localhost # List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3" DBNAMES="db1 db2 db3 usw." # Backup directory location e.g /backups BACKUPDIR="backups" # Mail setup # What would you like to be mailed to you? # - log : send only log file # - files : send log file and sql files as attachments (see docs) # - stdout : will simply output the log to the screen if run manually. MAILCONTENT="log" # Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs]) MAXATTSIZE="4000" # Email Address to send mail to? ([email protected]) MAILADDR="adresse" # ================================================== ========== # === ADVANCED OPTIONS ( Read the doc's below for details )=== #================================================= ============ # List of DBBNAMES for Monthly Backups. MDBNAMES="mysql $DBNAMES" # List of DBNAMES to EXLUCDE if DBNAMES are set to all (must be in " quotes) DBEXCLUDE="" # Include CREATE DATABASE in backup? CREATE_DATABASE=yes # Separate backup directory and file for each DB? (yes or no) SEPDIR=yes # Which day do you want weekly backups? (1 to 7 where 1 is Monday) DOWEEKLY=6 # Choose Compression type. (gzip or bzip2) COMP=gzip # Command to run before backups (uncomment to use) #PREBACKUP="/etc/mysql-backup-pre" # Command run after backups (uncomment to use) #POSTBACKUP="/etc/mysql-backup-post" #================================================= ==================== # Options documantation #================================================= ==================== # Set USERNAME and PASSWORD of a user that has at least SELECT permission # to ALL databases. # # Set the DBHOST option to the server you wish to backup, leave the # default to backup "this server".(to backup multiple servers make # copies of this file and set the options for that server) # # Put in the list of DBNAMES(Databases)to be backed up. If you would like # to backup ALL DBs on the server set DBNAMES="all".(if set to "all" then # any new DBs will automatically be backed up without needing to modify # this backup script when a new DB is created). # # If the DB you want to backup has a space in the name replace the space # with a % e.g. "data base" will become "data%base" # NOTE: Spaces in DB names may not work correctly when SEPDIR=no. # # You can change the backup storage location from /backups to anything # you like by using the BACKUPDIR setting.. # # The MAILCONTENT and MAILADDR options and pretty self explanitory, use # these to have the backup log mailed to you at any email address or multiple # email addresses in a space seperated list. # (If you set mail content to "log" you will require access to the "mail" program # on your server. If you set this to "files" you will have to have mutt installed # on your server. If you set it sto stdout it will log to the screen if run from # the console or to the cron job owner if run through cron) # # MAXATTSIZE sets the largest allowed email attachments total (all backup files) you # want the script to send. This is the size before it is encoded to be sent as an email # so if your mail server will allow a maximum mail size of 5MB I would suggest setting # MAXATTSIZE to be 25% smaller than that so a setting of 4000 would probably be fine. # # Finally copy automysqlbackup.sh to anywhere on your server and make sure # to set executable permission. You can also copy the script to # /etc/cron.daily to have it execute automatically every night or simply # place a symlink in /etc/cron.daily to the file if you wish to keep it # somwhere else. # NOTE:On Debian copy the file with no extention for it to be run # by cron e.g just name the file "automysqlbackup" # # Thats it.. # # # === Advanced options doc's === # # The list of MDBNAMES is the DB's to be backed up only monthly. You should # always include "mysql" in this list to backup your user/password # information along with any other DBs that you only feel need to # be backed up monthly. (if using a hosted server then you should # probably remove "mysql" as your provider will be backing this up) # NOTE: If DBNAMES="all" then MDBNAMES has no effect as all DBs will be backed # up anyway. # # If you set DBNAMES="all" you can configure the option DBEXCLUDE. Other # wise this option will not be used. # This option can be used if you want to backup all dbs, but you want # exclude some of them. (eg. a db is to big). # # Set CREATE_DATABASE to "yes" (the default) if you want your SQL-Dump to create # a database with the same name as the original database when restoring. # Saying "no" here will allow your to specify the database name you want to # restore your dump into, making a copy of the database by using the dump # created with automysqlbackup. # NOTE: Not used if SEPDIR=no # # The SEPDIR option allows you to choose to have all DBs backed up to # a single file (fast restore of entire server in case of crash) or to # seperate directories for each DB (each DB can be restored seperately # in case of single DB corruption or loss). # # To set the day of the week that you would like the weekly backup to happen # set the DOWEEKLY setting, this can be a value from 1 to 7 where 1 is Monday, # The default is 6 which means that weekly backups are done on a Saturday. # # COMP is used to choose the copmression used, options are gzip or bzip2. # bzip2 will produce slightly smaller files but is more processor intensive so # may take longer to complete. # # Use PREBACKUP and POSTBACKUP to specify Per and Post backup commands # or scripts to perform tasks either before or after the backup process. # # #================================================= ==================== # Backup Rotation.. #================================================= ==================== # # Daily Backups are rotated weekly.. # Weekly Backups are run by default on Saturday Morning when # cron.daily scripts are run...Can be changed with DOWEEKLY setting.. # Weekly Backups are rotated on a 5 week cycle.. # Monthly Backups are run on the 1st of the month.. # Monthly Backups are NOT rotated automatically... # It may be a good idea to copy Monthly backups offline or to another # server.. # #================================================= ==================== # Please Note!! #================================================= ==================== # # I take no resposibility for any data loss or corruption when using # this script.. # This script will not help in the event of a hard drive crash. If a # copy of the backup has not be stored offline or on another PC.. # You should copy your backups offline regularly for best protection. # # Happy backing up... # #================================================= ==================== # Restoring #================================================= ==================== # Firstly you will need to uncompress the backup file. # eg. # gunzip file.gz (or bunzip2 file.bz2) # # Next you will need to use the mysql client to restore the DB from the # sql file. # eg. # mysql --user=username --pass=password --host=dbserver database < /path/file.sql # or # mysql --user=username --pass=password --host=dbserver -e "source /path/file.sql" database # # NOTE: Make sure you use "<" and not ">" in the above command because # you are piping the file.sql to mysql and not the other way around. # # Lets hope you never have to use this.. :) # #================================================= ==================== # Change Log #================================================= ==================== # # VER 2.2 - (2004-12-05) # Changed from using depricated "-N" to "--skip-column-names". # Added ability to have compressed backup's emailed out. (code from Thomas Heiserowski) # Added maximum attachment size setting. # VER 2.1 - (2004-11-04) # Fixed a bug in daily rotation when not using gzip compression. (Fix by Rob Rosenfeld) # VER 2.0 - (2004-07-28) # Switched to using IO redirection instead of pipeing the output to the logfile. # Added choice of compression of backups being gzip of bzip2. # Switched to using functions to facilitate more functionality. # Added option of either gzip or bzip2 compression. # VER 1.10 - (2004-07-17) # Another fix for spaces in the paths (fix by Thomas von Eyben) # Fixed bug when using PREBACKUP and POSTBACKUP commands containing many arguments. # VER 1.9 - (2004-05-25) # Small bug fix to handle spaces in LOGFILE path which contains spaces (reported by Thomas von Eyben) # Updated docs to mention that Log email can be sent to multiple email addresses. # VER 1.8 - (2004-05-01) # Added option to make backups restorable to alternate database names # meaning that a copy of the database can be created (Based on patch by Rene Hoffmann) # Seperated options into standard and advanced. # Removed " from single file dump DBMANES because it caused an error but # this means that if DB's have spaces in the name they will not dump when SEPDIR=no. # Added -p option to mkdir commands to create multiple subdirs without error. # Added disk usage and location to the bottom of the backup report. # VER 1.7 - (2004-04-22) # Fixed an issue where weelky backups would only work correctly if server # locale was set to English (issue reported by Tom Ingberg) # used "eval" for "rm" commands to try and resolve rotation issues. # Changed name of status log so multiple scripts can be run at the same time. # VER 1.6 - (2004-03-14) # Added PREBACKUP and POSTBACKUP command functions. (patch by markpustjens) # Added support for backing up DB's with Spaces in the name. # (patch by markpustjens) # VER 1.5 - (2004-02-24) # Added the ability to exclude DB's when the "all" option is used. # (Patch by kampftitan) # VER 1.4 - (2004-02-02) # Project moved to Sourceforge.net # VER 1.3 - (2003-09-25) # Added support for backing up "all" databases on the server without # having to list each one seperately in the configuration. # Added DB restore instructions. # VER 1.2 - (2003-03-16) # Added server name to the backup log so logs from multiple servers # can be easily identified. # VER 1.1 - (2003-03-13) # Small Bug fix in monthly report. (Thanks Stoyanski) # Added option to email log to any email address. (Inspired by Stoyanski) # Changed Standard file name to .sh extention. # Option are set using yes and no rather than 1 or 0. # VER 1.0 - (2003-01-30) # Added the ability to have all databases backup to a single dump # file or seperate directory and file for each database. # Output is better for log keeping. # VER 0.6 - (2003-01-22) # Bug fix for daily directory (Added in VER 0.5) rotation. # VER 0.5 - (2003-01-20) # Added "daily" directory for daily backups for neatness (suggestion by Jason) # Added DBHOST option to allow backing up a remote server (Suggestion by Jason) # Added "--quote-names" option to mysqldump command. # Bug fix for handling the last and first of the year week rotation. # VER 0.4 - (2002-11-06) # Added the abaility for the script to create its own directory structure. # VER 0.3 - (2002-10-01) # Changed Naming of Weekly backups so they will show in order. # VER 0.2 - (2002-09-27) # Corrected weekly rotation logic to handle weeks 0 - 10 # VER 0.1 - (2002-09-21) # Initial Release # #================================================= ==================== #================================================= ==================== #================================================= ==================== # # Should not need to be modified from here down!! # #================================================= ==================== #================================================= ==================== #================================================= ==================== PATH=/usr/local/bin:/usr/bin:/bin DATE=`date +%Y-%m-%d` # Datestamp e.g 2002-09-21 DOW=`date +%A` # Day of the week e.g. Monday DNOW=`date +%u` # Day number of the week 1 to 7 where 1 represents Monday DOM=`date +%d` # Date of the Month e.g. 27 M=`date +%B` # Month e.g January W=`date +%V` # Week Number e.g 37 VER=2.2 # Version Number LOGFILE=$BACKUPDIR/$DBHOST-`date +%N`.log # Logfile Name OPT="--quote-names --opt" # OPT string for use with mysqldump ( see man mysqldump ) BACKUPFILES="" # thh: added for later mailing # Create required directories if [ ! -e "$BACKUPDIR" ] # Check Backup Directory exists. then mkdir -p "$BACKUPDIR" fi if [ ! -e "$BACKUPDIR/daily" ] # Check Daily Directory exists. then mkdir -p "$BACKUPDIR/daily" fi if [ ! -e "$BACKUPDIR/weekly" ] # Check Weekly Directory exists. then mkdir -p "$BACKUPDIR/weekly" fi if [ ! -e "$BACKUPDIR/monthly" ] # Check Monthly Directory exists. then mkdir -p "$BACKUPDIR/monthly" fi # IO redirection for logging. touch $LOGFILE exec 6>&1 # Link file descriptor #6 with stdout. # Saves stdout. exec > $LOGFILE # stdout replaced with file $LOGFILE. # Functions # Database dump function dbdump () { mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $1 > $2 return 0 } # Compression function SUFFIX="" compression () { if [ "$COMP" = "gzip" ]; then gzip -f "$1" echo echo Backup Information for "$1" gzip -l "$1.gz" SUFFIX=".gz" elif [ "$COMP" = "bzip2" ]; then echo Compression information for "$1.bz2" bzip2 -f -v $1 2>&1 SUFFIX=".bz2" else echo "No compression option set, check advanced settings" fi return 0 } # Run command before we begin if [ "$PREBACKUP" ] then echo ================================================== ==================== echo "Prebackup command output." echo eval $PREBACKUP echo echo ================================================== ==================== echo fi if [ "$SEPDIR" = "yes" ]; then # Check if CREATE DATABSE should be included in Dump if [ "$CREATE_DATABASE" = "no" ]; then OPT="$OPT --no-create-db" else OPT="$OPT --databases" fi else OPT="$OPT --databases" fi # Hostname for LOG information if [ "$DBHOST" = "localhost" ]; then HOST=`hostname` else HOST=$DBHOST fi # If backing up all DBs on the server if [ "$DBNAMES" = "all" ]; then DBNAMES="`mysql --user=$USERNAME --password=$PASSWORD --host=$DBHOST --batch --skip-column-names -e "show databases"| sed 's/ /%/g'`" # If DBs are excluded for exclude in $DBEXCLUDE do DBNAMES=`echo $DBNAMES | sed "s/\b$exclude\b//g"` done MDBNAMES=$DBNAMES fi echo ================================================== ==================== echo AutoMySQLBackup VER $VER echo http://sourceforge.net/projects/automysqlbackup/ echo echo Backup of Database Server - $HOST echo ================================================== ==================== # Test is seperate DB backups are required if [ "$SEPDIR" = "yes" ]; then echo Backup Start Time `date` echo ================================================== ==================== # Monthly Full Backup of all Databases if [ $DOM = "01" ]; then for MDB in $MDBNAMES do # Prepare $DB for using MDB="`echo $MDB | sed 's/%/ /g'`" if [ ! -e "$BACKUPDIR/monthly/$MDB" ] # Check Monthly DB Directory exists. then mkdir -p "$BACKUPDIR/monthly/$MDB" fi echo Monthly Backup of $MDB... dbdump "$MDB" "$BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql" compression "$BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql" BACKUPFILES="$BACKUPFILES $BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql$SUFFIX" echo ---------------------------------------------------------------------- done fi for DB in $DBNAMES do # Prepare $DB for using DB="`echo $DB | sed 's/%/ /g'`" # Create Seperate directory for each DB if [ ! -e "$BACKUPDIR/daily/$DB" ] # Check Daily DB Directory exists. then mkdir -p "$BACKUPDIR/daily/$DB" fi if [ ! -e "$BACKUPDIR/weekly/$DB" ] # Check Weekly DB Directory exists. then mkdir -p "$BACKUPDIR/weekly/$DB" fi # Weekly Backup if [ $DNOW = $DOWEEKLY ]; then echo Weekly Backup of Database \( $DB \) echo Rotating 5 weeks Backups... if [ "$W" -le 05 ];then REMW=`expr 48 + $W` elif [ "$W" -lt 15 ];then REMW=0`expr $W - 5` else REMW=`expr $W - 5` fi eval rm -fv "$BACKUPDIR/weekly/$DB/week.$REMW.*" echo dbdump "$DB" "$BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql" compression "$BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql" BACKUPFILES="$BACKUPFILES $BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql$SUFFIX" echo ---------------------------------------------------------------------- # Daily Backup else echo Daily Backup of Database \( $DB \) echo Rotating last weeks Backup... eval rm -fv "$BACKUPDIR/daily/$DB/*.$DOW.sql.*" echo dbdump "$DB" "$BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql" compression "$BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql" BACKUPFILES="$BACKUPFILES $BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql$SUFFIX" echo ---------------------------------------------------------------------- fi done echo Backup End `date` echo ================================================== ==================== else # One backup file for all DBs echo Backup Start `date` echo ================================================== ==================== # Monthly Full Backup of all Databases if [ $DOM = "01" ]; then echo Monthly full Backup of \( $MDBNAMES \)... dbdump "$MDBNAMES" "$BACKUPDIR/monthly/$DATE.$M.all-databases.sql" compression "$BACKUPDIR/monthly/$DATE.$M.all-databases.sql" BACKUPFILES="$BACKUPFILES $BACKUPDIR/monthly/$DATE.$M.all-databases.sql$SUFFIX" echo ---------------------------------------------------------------------- fi # Weekly Backup if [ $DNOW = $DOWEEKLY ]; then echo Weekly Backup of Databases \( $DBNAMES \) echo echo Rotating 5 weeks Backups... if [ "$W" -le 05 ];then REMW=`expr 48 + $W` elif [ "$W" -lt 15 ];then REMW=0`expr $W - 5` else REMW=`expr $W - 5` fi eval rm -fv "$BACKUPDIR/weekly/week.$REMW.*" echo dbdump "$DBNAMES" "$BACKUPDIR/weekly/week.$W.$DATE.sql" compression "$BACKUPDIR/weekly/week.$W.$DATE.sql" BACKUPFILES="$BACKUPFILES $BACKUPDIR/weekly/week.$W.$DATE.sql$SUFFIX" echo ---------------------------------------------------------------------- # Daily Backup else echo Daily Backup of Databases \( $DBNAMES \) echo echo Rotating last weeks Backup... eval rm -fv "$BACKUPDIR/daily/*.$DOW.sql.*" echo dbdump "$DBNAMES" "$BACKUPDIR/daily/$DATE.$DOW.sql" compression "$BACKUPDIR/daily/$DATE.$DOW.sql" BACKUPFILES="$BACKUPFILES $BACKUPDIR/daily/$DATE.$DOW.sql$SUFFIX" echo ---------------------------------------------------------------------- fi echo Backup End Time `date` echo ================================================== ==================== fi echo Total disk space used for backup storage.. echo Size - Location echo `du -hs "$BACKUPDIR"` echo echo ================================================== ==================== echo If you find AutoMySQLBackup valuable please make a donation at echo http://sourceforge.net/project/project_donations.php?group_id=101066 echo ================================================== ==================== # Run command when we're done if [ "$POSTBACKUP" ] then echo ================================================== ==================== echo "Postbackup command output." echo eval $POSTBACKUP echo echo ================================================== ==================== fi #Clean up IO redirection exec 1>&6 6>&- # Restore stdout and close file descriptor #6. if [ "$MAILCONTENT" = "files" ] then #Get backup size ATTSIZE=`du -c $BACKUPFILES | grep "[[:digit:][:space:]]total$" |sed s/\s*total//` if [ $MAXATTSIZE -ge $ATTSIZE ] then BACKUPFILES=`echo "$BACKUPFILES" | sed -e "s# # -a #g"` #enable multiple attachments mutt -s "MySQL Backup Log and SQL Files for $HOST - $DATE" $BACKUPFILES $MAILADDR < $LOGFILE #send via mutt else cat "$LOGFILE" | mail -s "WARNING! - MySQL Backup exceeds set maximum attachment size on $HOST - $DATE" $MAILADDR fi elif [ "$MAILCONTENT" = "log" ] then cat "$LOGFILE" | mail -s "MySQL Backup Log for $HOST - $DATE" $MAILADDR else cat "$LOGFILE" fi # Clean up Logfile eval rm -f "$LOGFILE" exit 0
Meatwad
geschrieben 14. Mai 2005 - 16:22
Zitat
Floele
geschrieben 14. Mai 2005 - 17:36
broklyn
geschrieben 14. Mai 2005 - 17:45
#!/bin/sh date=`date -I` mysqlcheck --all-databases --auto-repair --extended --optimize -uroot -ppasswort >/var/backup/logs/mysqlcheck-$date.log mysqldump --all-databases -uroot -ppasswort --opt | bzip2 -c > /var/backup/databasebackup-$date.sql.bz2 rm /var/backup/databasebackup-current.sql.bz2 rm /var/backup/logs/mysqlcheck-current.log ln -s /var/backup/databasebackup-$date.sql.bz2 /var/backup/databasebackup-current.sql.bz2 ln -s /var/backup/logs/mysqlcheck-$date.log /var/backup/logs/mysqlcheck-current.log
Meatwad
geschrieben 14. Mai 2005 - 18:10
broklyn
geschrieben 14. Mai 2005 - 18:33
Zitat
Zitat
Zitat
Zitat
Zitat
hostname:~# /root/scripts/mysqlbackup (snap) admin Table is already up to date allgemein OK anbieter Table is already up to date angebote Table is already up to date atdomains Table is already up to date autoresponder Table is already up to date backup OK bdefault Table is already up to date (/snap)
hostname:~# ls -ls /var/backup total 1864 648 -rw-r--r-- 1 root root 660695 May 12 03:54 databasebackup-2005-05-12.sql.bz2 644 -rw-r--r-- 1 root root 659444 May 14 19:36 databasebackup-2005-05-14.sql.bz2 0 lrwxrwxrwx 1 root root 45 May 14 19:36 databasebackup-current.sql.bz2 -> /var/backup/databasebackup-2005-05-14.sql.bz2 0 drwxr-xr-x 2 root root 131 May 14 19:36 logs
Dieser Beitrag wurde von broklyn bearbeitet: 14. Mai 2005 - 18:50
Meatwad
geschrieben 14. Mai 2005 - 18:50
Floele
geschrieben 14. Mai 2005 - 18:54
Zitat
Meatwad
geschrieben 14. Mai 2005 - 18:56
broklyn
geschrieben 14. Mai 2005 - 18:56
Zitat (Meatwad: 14.05.2005, 19:50)
Zitat
Dieser Beitrag wurde von broklyn bearbeitet: 14. Mai 2005 - 19:00
Meatwad
geschrieben 14. Mai 2005 - 19:00
Dieser Beitrag wurde von Meatwad bearbeitet: 14. Mai 2005 - 19:01
broklyn
geschrieben 14. Mai 2005 - 19:04
Dieser Beitrag wurde von broklyn bearbeitet: 14. Mai 2005 - 19:11
Meatwad
geschrieben 14. Mai 2005 - 19:05
Dieser Beitrag wurde von Meatwad bearbeitet: 14. Mai 2005 - 19:25
Hilfe
Neues Thema
Antworten
Nach oben
