i need help for shifting db from server to server

[chems]

hello guys

i want to shift my big db ( more than 1.5 Gb )

from a vps that i have root access

to another with directadmin without root access any help to do that ?

 

[Mr.KiLLeR]

u can do that through big dump

 

[Sp32]

I'd advise mysqldumper as I've had problems with bigdump in the past.

 

[Dl4uall]

i can do it it

 

[chems]

i think mysqldumper will take much time

 

[faizulhaque]

if you have access with SSH than use Commands to Get DB backup in within 5 to 10 sec.

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

servername= localhost
dbusername=you already know that
databasename=you know that too

when you press enter it will ask database password.

Use Putty for SSH is good and lite weight.

 

[Sp32]

If you're good at linux, do it the linux way via ssh.

mysqldumper doesn't take any longer than bigdump when it comes to backing up or downloading, why would you assume it did?

 

[chems]

If you're good at linux, do it the linux way via ssh.

mysqldumper doesn't take any longer than bigdump when it comes to backing up or downloading, why would you assume it did?

if you have access with SSH than use Commands to Get DB backup in within 5 to 10 sec.



servername= localhost
dbusername=you already know that
databasename=you know that too

when you press enter it will ask database password.

Use Putty for SSH is good and lite weight.

the problem is that i havent the new server ssh access

 

[Tango]

You should ask for ssh access, your could do the transfer in 10-20 minutes then.

Compress your database in /var/lib/mysql & push it to the new server, then decompress.

 

[faizulhaque]

if you are facing any problem than post here...

 

[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 +"%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.