MySQL Backup using SSH

[avx]

how can I make a database backup via SSH by using which command I don't use any remote mysql just a normal local host mySQL.

[justahost]

I have put together a small perl script to do this via cron, not sure if its of use to you:

#!/usr/bin/perl

$BACKUP_DIR = "/home/backup/";
$MYDATE = `date +%Y-%m-%d`; chomp($MYDATE);
$END = "sql.gz";


`rm -f $BACKUP_DIR/*.gz`;

@databases = `mysql -uda_admin -pPASSWORD -Bse 'show databases'`;
foreach $db (@databases)
{
chomp($db);
$HERE = "$BACKUP_DIR".$db."-$MYDATE.$END";
`mysqldump -uda_admin -pPASSWORD $db | gzip -9 > $HERE`;
}

So in the backup directory you specify you will get a file called dbname-datestamp.sql.gz

Its probably not the most tidy code in the worl, but by I am still thrashing me way through Perl!!

Ry

[Webcart]

I would suggest adding at least the following options to mysqldump command:

--add-drop-table -Q

Just my 2 cents...