1、备份ＤＢ中的数据到单表，以防止增加表后少备份了。 （Perl）

#!/usr/bin/perl
#　mysql meg
#　wubx
use strict;
use DBI;
my $host='172.16.100.1';
my $port='3306';
my $user='root;
my $passwd='';
my $dsn="dbi:mysql:hostname=$host:port=$port";
my $backup="/backup";
my $dbh= DBI->connect($dsn,$user,'');
unless ($dbh)
{
        print "Connect error!\n";

}
my ($sec,$min,$hour,$mday,$mon,$year,$wday)=localtime(time);
$mon = $mon+1;
$year = $year+1900;
my $terday = sprintf("%04d-%02d-%02d",$year,$mon,$mday);
print "cd $backup\n";
my $sql="show databases";
my $sth= $dbh->prepare("$sql");
$sth->execute;
while (my @row=$sth->fetchrow_array())
{
        print "mkdir $row[0]_$terday && cd $row[0]_$terday\n";
        $dbh->do("use $row[0]");
        my $show_table="show tables";
        my $sth_table= $dbh->prepare("$show_table");
        $sth_table->execute;
        while ( my @rr = $sth_table->fetchrow_array())
        {
                print "mysqldump --opt $row[0] $rr[0]|gzip>$row[0]_$rr[0].sql.gz\n";
        }
        print "cd ..\n";
}
$dbh->disconnect();

调用方法用crontab 结果输出到一个文件，然后在在执行那个文件。

2、用shell也挺简单

#/bin/bash
DATE=`date +%Y-%m-%d`
MYSQL="mysql -uroot -h xxx "
for db in `$MYSQL -e "show databases"|sed '1d'`
do
mkdir -p $db_$DATE
cd $db_$DATE
for table in `$MYSQL $db -e "show tables"|sed '1d'`
do
mysqldump --opt $db $table |gzip >$db_$table.sql.gz
done
done
-------------------------第二种方式，大同小异----------------

#!/bin/sh
#Backup database demo,2008-07-17
USERNAME='root'
#USERPWD=''
DBHOST='localhost'
DATABASE='game_cms'

BACKUP_PATH=/backup/cms_bak/sql
BACKUP_FILE=$BACKUP_PATH"/"$DATABASE"_"`date +%Y:%m:%d_%H_%M`".sql"

MYSQLDUMP=/usr/local/mysql/bin/mysqldump
MYSQLADMIN=/usr/local/mysql/bin/mysqladmin

if ! [ -x $BACKUP_PATH ] ; then
    mkdir $BACKUP_PATH
fi

#if [ -z "$1" ] ; then
#    echo 0 ERROR NO ARGS
#    exit 0
#fi

#$MYSQLADMIN -u$USERNAME -p$USERPWD -h$DBHOST flush-logs
#$MYSQLDUMP -u$USERNAME -p$USERPWD -h$DBHOST  $DATABASE> $BACKUP_FILE
$MYSQLADMIN -u$USERNAME -h$DBHOST flush-logs
$MYSQLDUMP -u$USERNAME -h$DBHOST --master-data --opt $DATABASE> $BACKUP_FILE

echo ok