i need help for shifting db from server to server

[chems]

what are linux command for compressing db and upload it to any filehost if possible

[faizulhaque]

i written command bro to get the backup from database server.

just put the path

mysql -h servername -u dbusername -p databasename < backupname.sql

here like your path is

/home/username/public_html/backup_folder/backupname.sql

than you command will modify like

mysql -h servername -u dbusername -p databasename < /home/username/public_html/backup_folder/backupname.sql

[Lock Down]

here is nice script that will do it for you just change to your site information. This also can be done with a cron job.

!/bin/sh
# System + MySQL backup script
# Full backup day - Sun (rest of the day do incremental backup)
# Copyright (c) 2005-2006 nixCraft <http://www.cyberciti.biz/fb/>
# This script is licensed under GNU GPL version 2.0 or above
# Automatically generated by http://bash.cyberciti.biz/backup/wizard-ftp-script.php
# ---------------------------------------------------------------------
### System Setup ###
DIRS="/home /etc /var/www"
BACKUP=/tmp/backup.$$
NOW=$(date +"&#37;d-%m-%Y")
INCFILE="/root/tar-inc-backup.dat"
DAY=$(date +"%a")
FULLBACKUP="Sun"
### MySQL Setup ###
MUSER="admin"
MPASS="mysqladminpassword"
MHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
### FTP server Setup ###
FTPD="/home/vivek/incremental"
FTPU="vivek"
FTPP="ftppassword"
FTPS="208.111.11.2"
NCFTP="$(which ncftpput)"
### Other stuff ###
EMAILID="admin@theos.in"
### Start Backup for file system ###
[ ! -d $BACKUP ] && mkdir -p $BACKUP || :
### See if we want to make a full backup ###
if [ "$DAY" == "$FULLBACKUP" ]; then
FTPD="/home/vivek/full"
FILE="fs-full-$NOW.tar.gz"
tar -zcvf $BACKUP/$FILE $DIRS
else
i=$(date +"%Hh%Mm%Ss")
FILE="fs-i-$NOW-$i.tar.gz"
tar -g $INCFILE -zcvf $BACKUP/$FILE $DIRS
fi
### Start MySQL Backup ###
# Get all databases name
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
FILE=$BACKUP/mysql-$db.$NOW-$(date +"%T").gz
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done
### Dump backup using FTP ###
#Start FTP backup using ncftp
ncftp -u"$FTPU" -p"$FTPP" $FTPS<<EOF
mkdir $FTPD
mkdir $FTPD/$NOW
cd $FTPD/$NOW
lcd $BACKUP
mput *
quit
EOF
### Find out if ftp backup failed or not ###
if [ "$?" == "0" ]; then
rm -f $BACKUP/*
else
T=/tmp/backup.fail
echo "Date: $(date)">$T
echo "Hostname: $(hostname)" >>$T
echo "Backup failed" >>$T
mail -s "BACKUP FAILED" "$EMAILID" <$T
rm -f $T
fi

[faizulhaque]

@Lock Down

cool bro nice Bash Script.... might i will try that too. on my vps..thanks

[Lock Down]

Enjoy ..

[chems]

here is nice script that will do it for you just change to your site information. This also can be done with a cron job.

can you explain how it works

[Lock Down]

If you do not understand linux commands nothing would help.

The variables are well defined so you would know what to replace if you use ssh and know your site folder and database names, passwords and users.

If you have tried it and get a specific error that you need help with let me know.


SImple backup and zip is :

mysqldump -u MYUSER -pMYPASS --opt --flush-logs MYDB | gzip > MYDB.sql.gz

simple scp transfer is :

scp MYDB.sql.gz MYLogin@MYSite.com:MYDB.sql.gz

It will ask for a password for that login.