Hi,

fedora schrieb:

Is there any proper mysql backup that can backup database without stopping
it??
If so, I don't have to worry about it? Of course it wasn't failed but I just
worried if I recover it and the database or tables might be corrupt just
because Amanda detected strange. Please advice.

I use a script for exporting the databases to an extra partition some time
before amanda collect the data for backup. The script backup every database
into a single file, using mysqldump. Also it write me a small logfile and send
me an eMail if an error occured. All this is done while the MySQL server is
running. Only for backup the BinaryLogs I have to lock the tables for a second.

For me this is the best solution.

See my script below.

Regards
Marc


--
Marc Muehlfeld (Leitung Systemadministration)
Zentrum fuer Humangenetik und Laboratoriumsmedizin Dr. Klein und Dr. Rost
Lochhamer Str. 29 - D-82152 Martinsried
Telefon: +49(0)89/895578-0 - Fax: +49(0)89/895578-78
http://www.medizinische-genetik.de

#!/bin/bash

##############################################################################
#
# Backup mysql tables, binary logs (if enabled) and configuration
#
# Created:     2002-11-21, Marc Muehlfeld
# Last edited: 2007-01-23, Marc Muehlfeld
#
##############################################################################





##############################################################################
# Configuration area
##############################################################################


# MySQL password for root user
MYSQLPASSWD={password}


# Keep old backup $KEEP days
KEEP=2


# Path where you want to put the backup
DESTINATION="/BACKUP/mysql"


# Logfile
LOG="/var/log/`basename "$0"`.log"


# Set path for required tools
PATH=$PATH:/bin:/usr/bin


# Whitespace separated list of all configuration files you want to save
CONFIGS="/etc/my.cnf"





##############################################################################
# Here begins the program
##############################################################################


# Logging function to stdout and logfile
function logging () {

        echo -e "`date +%d.%m.%Y" "%H:%M:%S` $1" | tee -a "$LOG"

}



logging "Starting backup"



# Create destination directory if it doesn't exist and secure it.
umask 0027
test ! -d "$DESTINATION" && mkdir -p "$DESTINATION"
chown root:root "$DESTINATION"



# Create temporary directory
TMPDIR="$DESTINATION/MySQL_`date +%d-%m-%Y_%H-%M-%S`"
mkdir "$TMPDIR"



# Create list with database names
logging "Create list with database names"
DATABASES=`mysql -u root --password=$MYSQLPASSWD <<EOF
show databases; 
quit 
EOF`
DATABASES=`echo $DATABASES | sed -e 's/^Database //'`



# Dump databases
mkdir -p "$TMPDIR/db_exports"
for DB in $DATABASES ; do

        logging "Dumping $DB"
        mysqldump -u root -p$MYSQLPASSWD $DB -e -a -c --add-drop-table 
--add-locks -F -l > "$TMPDIR/db_exports/$DB.sql"

done



# Copy configs
logging "Backup configuration"
mkdir -p "$TMPDIR/configs"
cp -p "$CONFIGS" "$TMPDIR/configs"



# Check if there are binary logs enabled
egrep ^log-bin /etc/my.cnf > /dev/null 2>&1

if [ $? -eq 0 ] ; then

        logging "Copy binary logs"


        # Preparing
        mysql -u root -p$MYSQLPASSWD << EOF
        FLUSH TABLES WITH READ LOCK;
        FLUSH LOGS;
EOF

        # Copy binlog
        mkdir -p "$TMPDIR/bin_log"
        BINLOGDIR="`dirname $(egrep ^log-bin /etc/my.cnf | sed -e 's/^.*=//g')`"
        cp -p -r "$BINLOGDIR" "$TMPDIR/bin_log"


        # Remove old binlogs
        mysql -u root -p$MYSQLPASSWD <<EOF
        RESET MASTER;
        UNLOCK TABLES;
EOF

fi



# Build tar package
logging "Building tar package"
cd "$DESTINATION"
DIRONLY=`echo $TMPDIR | sed -e 's/^.*\///g'`
tar -zcf "$DIRONLY.tar.gz" "$DIRONLY"



# Remove all backups older than $KEEP days
logging "Removing backups older than $KEEP days"
find "$DESTINATION"/* -type f ! -mtime -$KEEP -exec rm -rf '{}' ';'



# Cleanup
logging "Removing temporary directory"
rm -rf "$TMPDIR"



# Finish
logging "Backup finished\n"



exit 0