THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
Code Block | ||||
---|---|---|---|---|
| ||||
#!/bin/bash # MySQL Backup Script # PreReqs: Mutt mail client # Add to cron to make it a scheduled backup # Copyright 2012 Nik Martin # Licensed under the Apache License, Version 2.0 (the "License"); # you may not use this file except in compliance with the License. # You may obtain a copy of the License at # # http://www.apache.org/licenses/LICENSE-2.0 # # Unless required by applicable law or agreed to in writing, software # distributed under the License is distributed on an "AS IS" BASIS, # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. # See the License for the specific language governing permissions and # limitations under the License. # MySQL Backup User Info DBHOST=localhost DBUSER=backup DBPASS=mybakupuserpass #a backup user needs select, lock tables, and reload privileges in MySQL: #GRANT SHOW DATABASES, SELECT, LOCK TABLES, RELOAD ON *.* to backup@localhost IDENTIFIED BY 'mybackupsuserpass'; #FLUSH PRIVILEGES; #where to send backed up file TO=you@yourdomain.com #mutt has to read body of email from file, not stdin MUTTXT=/tmp/myback.txt # format is YYYYMMDD DATE=`date +%Y%m%d` # This command runs the `show databases' query against the mysql server # it then pipes that result to awk which prints out the dbnames # finally use grep -v to strip out any db's that shouldn't be backed up # to skip multiple databases use grep -E -v "dbname1|dbname2|dname3" #get list of databases on server DBS=`mysql --host=$DBHOST -p$DBPASS -u $DBUSER --skip-column-names -e "show databases;" | awk '{ print $1 }' | grep -v "information_schema"` #now loop through, dumping each database to a gzip for i in $DBS do # format is dbname-YYYYMMDD.gz DBOUT=$i-$DATE.sql.gz echo Backing up $i to $DBOUT #set umask to protect file umask 006 mysqldump -u $DBUSER -h $DBHOST -p$DBPASS --add-drop-table $i | gzip -9 - > $DBOUT echo "Backup successfully done. Please see attached file." > $MUTTXT echo "" >> $MUTTXT echo "Backup file: $DBOUT" >> $MUTTXT echo "" >> $MUTTXT echo Sending $DBOUT to $TO which mutt > /dev/null if [ $? -eq 0 ]; then # now mail backup file with this attachment mutt -s "$DBOUT Backup" -a "$DBOUT" $TO < $MUTTXT else echo "Command mutt not found, cannot send an email with attachment" fi if [ $? -eq 0 ]; then echo Removing $DBOUT rm $DBOUT else echo Error sending $DBOUT fi done |