02 Nov 2009 Howto backup mysql stored functions and stored procedures
This is how you can backup you MySQL database(s) and stored procedures
# mysqldump --routines <dbname>
Or you can backup only the stored procedures
# mysqldump --no-create-db --no-create-info --no-data --routines <dbname>
Tags: backup, howto, MySQL, stored functions
Posted by Hans-Henry Jakobsen
19 Mar 2009 Backup partition table
This post describes how you can backup your partition table for future recovery.
Backup
This example describes how you can backup the disk partition table on /dev/sda
dd if=/dev/sda of=sda.mbr bs=512 count=1
It’s also useful to keep a human readable copy of the disk layout for future reference
sudo fdisk -l > partitions.lst
Restore
This example shows how you can recover your file system using your partition table backup.
dd if=sda.mbr of=/dev/sda bs=512 count=1
This is a nice way to keep the information about your file system and will it help you in the future if you get disk corruption.
This does not only apply to linux partitions but all types of partitions.
Tags: backup, dd, fdisk, recovery
Posted by Hans-Henry Jakobsen
01 Feb 2008 Cold backup for the Open Source Edition of Zimbra
Today I’ve setup a cold backup routine to backup my Zimba installation running on my Debian (Etch) 4.0 server that is in full production now for my private domains. This is a slightly modified backup script for the Open Source Edition of Zimbra from the Zimbra Wiki. Please note that the script does a full backup every time it’s being run!
#!/bin/bash # Zimbra Backup Script # Requires sftp to run # This script is intended to run from the crontab as root # Free to use and free of any warranty! Daniel W. Martin, 9 Sept 2007 # Live sync before stopping Zimbra to minimize sync time with the services down # Comment out the following line if you want to try single cold-sync only rsync -avHK --delete /opt/zimbra/ /backup/zimbra # which is the same as: /opt/zimbra /backup # Including --delete option gets rid of files in the dest folder that don't exist at the src # this prevents logfile/extraneous bloat from building up overtime. # Stop Zimbra Services sudo -u zimbra /opt/zimbra/bin/zmcontrol stop sleep 40 # Sync to backup directory rsync -avHK --delete /opt/zimbra/ /backup/zimbra # Restart Zimbra Services sudo -u zimbra /opt/zimbra/bin/zmcontrol start # Create a txt file in the backup directory that'll contain the current Zimbra # server version. Handy for knowing what version of Zimbra a backup can be restored to. sudo -u zimbra zmcontrol -v > /backup/zimbra/conf/zimbra_version.txt # or examine your /opt/zimbra/.install_history # Create archive of backed-up directory for offsite transfer # cd /backup/zimbra ZimbraVersion=zimbraBackup-zcs-5.0.1_GA_1902.DEBIAN4.0.20080109200914 tar -zcf /backup/$ZimbraVersion-$(date +"%Y-%m-%d").tgz -C /backup/zimbra . # Transfer file to backup server using passwordless sftp scp zimbraBackup-zcs-5.0.1_GA_1902.DEBIAN4.0.20080109200914-$(date +"%Y-%m-%d").tgz username@example.com:/path/to/backupfolder/
I’ve described passwordless ssh/sftp session in a earlier post so I won’t describe the last line of the backup script.
Tags: backup, bash, Debian, sftp, tar., Zimbra
Posted by Hans-Henry Jakobsen
18 Nov 2007 Backup mysql databases into separate files
This bach script makes separate backup files of all the databases in mysql and saves the result in the mysql_backup folder.
#!/bin/bash -v
USERNAME='yourusername'
PASSWORD='yourpassword'
HOSTNAME='yourhostname'
BackupFolder='/backup'
for i in $(echo 'SHOW DATABASES;' | mysql --user $USERNAME -p$PASSWORD -h $HOSTNAME | grep -v '^Database$' ); do
mysqldump --user $USERNAME -p$PASSWORD -h $HOSTNAME --opt $i > $BackupFolder/$i.sql;
done;
Remember to change the -h, -p and -h switch according to your needs and avoid space between -p and the password variable.
Tags: backup, bash, Database, grep, MySQL, mysqldump
Posted by Hans-Henry Jakobsen
08 Sep 2007 Script to backup MySql database
#!/bin/bash
# Shell script to backup MySql database
# To backup Nysql databases file to /backup dir and later pick up by your
# script. You can skip few databases from backup too.
MyUSER="SET-MYSQL-USER-NAME" # USERNAME
MyPASS="SET-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="/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"
# 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
Tags: backup, bash, Database, MySQL, mysqldump
Posted by Hans-Henry Jakobsen
