Re: MYSQL DB BACKUP
There are some of backup scripts written by me.You can find it at: http://blog.chinaunix.net/u/29134/article_71953.html On Wed, Oct 22, 2008 at 4:52 PM, Mad Unix <[EMAIL PROTECTED]> wrote: > Any one tried the script from HowToForge > > > http://www.howtoforge.com/shell-script-to-back-up-all-mysql-databases-each-table-in-an-individual-file-and-upload-to-remote-ftp > > #!/bin/sh > # System + MySQL backup script > # Copyright (c) 2008 Marchost > # This script is licensed under GNU GPL version 2.0 or above > # - > > # > ##TO BE MODIFIED# > > ### System Setup ### > BACKUP=YOUR_LOCAL_BACKUP_DIR > > ### MySQL Setup ### > MUSER="MYSQL_USER" > MPASS="MYSQL_USER_PASSWORD" > MHOST="localhost" > > ### FTP server Setup ### > FTPD="YOUR_FTP_BACKUP_DIR" > FTPU="YOUR_FTP_USER" > FTPP="YOUR_FTP_USER_PASSWORD" > FTPS="YOUR_FTP_SERVER_ADDRESS" > > ##DO NOT MAKE MODIFICATION BELOW# > # > > ### Binaries ### > TAR="$(which tar)" > GZIP="$(which gzip)" > FTP="$(which ftp)" > MYSQL="$(which mysql)" > MYSQLDUMP="$(which mysqldump)" > > ### Today + hour in 24h format ### > NOW=$(date +"%d%H") > > ### Create hourly dir ### > > mkdir $BACKUP/$NOW > > ### Get all databases name ### > DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')" > for db in $DBS > do > > ### Create dir for each databases, backup tables in individual files ### > mkdir $BACKUP/$NOW/$db > > for i in `echo "show tables" | $MYSQL -u $MUSER -h $MHOST -p$MPASS > $db|grep -v Tables_in_`; > do >FILE=$BACKUP/$NOW/$db/$i.sql.gz >echo $i; $MYSQLDUMP --add-drop-table --allow-keywords -q -c -u > $MUSER -h $MHOST -p$MPASS $db $i | $GZIP -9 > $FILE > done > done > > ### Compress all tables in one nice file to upload ### > > ARCHIVE=$BACKUP/$NOW.tar.gz > ARCHIVED=$BACKUP/$NOW > > $TAR -cvf $ARCHIVE $ARCHIVED > > ### Dump backup using FTP ### > cd $BACKUP > DUMPFILE=$NOW.tar.gz > $FTP -n $FTPS < quote USER $FTPU > quote PASS $FTPP > cd $FTPD > mput $DUMPFILE > quit > END_SCRIPT > > ### Delete the backup dir and keep archive ### > > rm -rf $ARCHIVED > > > On Wed, Sep 17, 2008 at 10:18 AM, Krishna Chandra Prajapati > <[EMAIL PROTECTED]> wrote: > > Thanks a lot. > > > > I am writing script which will take backup and copy it to another box. > > > > On Wed, Sep 17, 2008 at 11:14 AM, Ananda Kumar <[EMAIL PROTECTED]> > wrote: > > > >> Can u mount that file system on the slave db and take the backup so that > u > >> can avoid Network latency. > >> > >> regards > >> anandkl > >> > >> > >> On 9/16/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > >>> > >>> Yes > >>> > >>> On Tue, Sep 16, 2008 at 6:39 PM, Ananda Kumar <[EMAIL PROTECTED]> > wrote: > >>> > Hi Krishna, > When u say remote server, do u mean the file system storing the backup > is > on a different machine. > > regards > anandkl > > > On 9/16/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> > wrote: > > > > Hi, > > > > Currently, i am taking production server backup on hourly basis on > the > > slave > > server. Is it feasible to take 15G backup on remote server on hourly > > basis. > > It takes 10 minutes on slave server. How much time it will take on > > remote > > server. > > > > Thanks, > > -- > > Krishna Chandra Prajapati > > > > > > >>> > >>> > >>> > >>> -- > >>> Krishna Chandra Prajapati > >>> > >>> > >>> > >> > >> > > > > > > -- > > Krishna Chandra Prajapati > > MySQL DBA, > > Ed Ventures e-Learning Pvt.Ltd. > > 1-8-303/48/15, Sindhi Colony > > P.G.Road, Secunderabad. > > Pin Code: 53 > > Office Number: 040-66489771 > > Mob: 9912924044 > > URL: ed-ventures-online.com > > Email-id: [EMAIL PROTECTED] > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: MYSQL DB BACKUP
Any one tried the script from HowToForge http://www.howtoforge.com/shell-script-to-back-up-all-mysql-databases-each-table-in-an-individual-file-and-upload-to-remote-ftp #!/bin/sh # System + MySQL backup script # Copyright (c) 2008 Marchost # This script is licensed under GNU GPL version 2.0 or above # - # ##TO BE MODIFIED# ### System Setup ### BACKUP=YOUR_LOCAL_BACKUP_DIR ### MySQL Setup ### MUSER="MYSQL_USER" MPASS="MYSQL_USER_PASSWORD" MHOST="localhost" ### FTP server Setup ### FTPD="YOUR_FTP_BACKUP_DIR" FTPU="YOUR_FTP_USER" FTPP="YOUR_FTP_USER_PASSWORD" FTPS="YOUR_FTP_SERVER_ADDRESS" ##DO NOT MAKE MODIFICATION BELOW# # ### Binaries ### TAR="$(which tar)" GZIP="$(which gzip)" FTP="$(which ftp)" MYSQL="$(which mysql)" MYSQLDUMP="$(which mysqldump)" ### Today + hour in 24h format ### NOW=$(date +"%d%H") ### Create hourly dir ### mkdir $BACKUP/$NOW ### Get all databases name ### DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')" for db in $DBS do ### Create dir for each databases, backup tables in individual files ### mkdir $BACKUP/$NOW/$db for i in `echo "show tables" | $MYSQL -u $MUSER -h $MHOST -p$MPASS $db|grep -v Tables_in_`; do FILE=$BACKUP/$NOW/$db/$i.sql.gz echo $i; $MYSQLDUMP --add-drop-table --allow-keywords -q -c -u $MUSER -h $MHOST -p$MPASS $db $i | $GZIP -9 > $FILE done done ### Compress all tables in one nice file to upload ### ARCHIVE=$BACKUP/$NOW.tar.gz ARCHIVED=$BACKUP/$NOW $TAR -cvf $ARCHIVE $ARCHIVED ### Dump backup using FTP ### cd $BACKUP DUMPFILE=$NOW.tar.gz $FTP -n $FTPS < wrote: > Thanks a lot. > > I am writing script which will take backup and copy it to another box. > > On Wed, Sep 17, 2008 at 11:14 AM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > >> Can u mount that file system on the slave db and take the backup so that u >> can avoid Network latency. >> >> regards >> anandkl >> >> >> On 9/16/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: >>> >>> Yes >>> >>> On Tue, Sep 16, 2008 at 6:39 PM, Ananda Kumar <[EMAIL PROTECTED]> wrote: >>> Hi Krishna, When u say remote server, do u mean the file system storing the backup is on a different machine. regards anandkl On 9/16/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > > Hi, > > Currently, i am taking production server backup on hourly basis on the > slave > server. Is it feasible to take 15G backup on remote server on hourly > basis. > It takes 10 minutes on slave server. How much time it will take on > remote > server. > > Thanks, > -- > Krishna Chandra Prajapati > >>> >>> >>> >>> -- >>> Krishna Chandra Prajapati >>> >>> >>> >> >> > > > -- > Krishna Chandra Prajapati > MySQL DBA, > Ed Ventures e-Learning Pvt.Ltd. > 1-8-303/48/15, Sindhi Colony > P.G.Road, Secunderabad. > Pin Code: 53 > Office Number: 040-66489771 > Mob: 9912924044 > URL: ed-ventures-online.com > Email-id: [EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL DB BACKUP
Thanks a lot. I am writing script which will take backup and copy it to another box. On Wed, Sep 17, 2008 at 11:14 AM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > Can u mount that file system on the slave db and take the backup so that u > can avoid Network latency. > > regards > anandkl > > > On 9/16/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: >> >> Yes >> >> On Tue, Sep 16, 2008 at 6:39 PM, Ananda Kumar <[EMAIL PROTECTED]> wrote: >> >>> Hi Krishna, >>> When u say remote server, do u mean the file system storing the backup is >>> on a different machine. >>> >>> regards >>> anandkl >>> >>> >>> On 9/16/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: Hi, Currently, i am taking production server backup on hourly basis on the slave server. Is it feasible to take 15G backup on remote server on hourly basis. It takes 10 minutes on slave server. How much time it will take on remote server. Thanks, -- Krishna Chandra Prajapati >>> >>> >>> >> >> >> >> -- >> Krishna Chandra Prajapati >> >> >> > > -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED]
Re: MYSQL DB BACKUP
Can u mount that file system on the slave db and take the backup so that u can avoid Network latency. regards anandkl On 9/16/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > > Yes > > On Tue, Sep 16, 2008 at 6:39 PM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > >> Hi Krishna, >> When u say remote server, do u mean the file system storing the backup is >> on a different machine. >> >> regards >> anandkl >> >> >> On 9/16/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: >>> >>> Hi, >>> >>> Currently, i am taking production server backup on hourly basis on the >>> slave >>> server. Is it feasible to take 15G backup on remote server on hourly >>> basis. >>> It takes 10 minutes on slave server. How much time it will take on remote >>> server. >>> >>> Thanks, >>> -- >>> Krishna Chandra Prajapati >>> >> >> >> > > > > -- > Krishna Chandra Prajapati > > >
Re: MYSQL DB BACKUP
Yes On Tue, Sep 16, 2008 at 6:39 PM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > Hi Krishna, > When u say remote server, do u mean the file system storing the backup is > on a different machine. > > regards > anandkl > > > On 9/16/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: >> >> Hi, >> >> Currently, i am taking production server backup on hourly basis on the >> slave >> server. Is it feasible to take 15G backup on remote server on hourly >> basis. >> It takes 10 minutes on slave server. How much time it will take on remote >> server. >> >> Thanks, >> -- >> Krishna Chandra Prajapati >> > > -- Krishna Chandra Prajapati
Re: MYSQL DB BACKUP
Hi Krishna, When u say remote server, do u mean the file system storing the backup is on a different machine. regards anandkl On 9/16/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > > Hi, > > Currently, i am taking production server backup on hourly basis on the > slave > server. Is it feasible to take 15G backup on remote server on hourly basis. > It takes 10 minutes on slave server. How much time it will take on remote > server. > > Thanks, > -- > Krishna Chandra Prajapati >
Re: restoring mysql db backup..
Helza wrote: > > Hi, > > I've made an backup of my MySQL db using: > > mysqldump -uoperatio_operati -p* operatio_forum >> database.sql > > and with: > mysqldump -uoperatio_operati -p* --quick --add-drop-table --add-locks >--extended-insert --lock-tables operatio_forum > database.sql > > both produce a mysql back of around 2-3 mb... > > however when i try to import it with: > > mysqldump -uoperatio_operati -p* operatio_forum < database.sql > > nothing happens.. i just see: > > # MySQL dump 8.12 > # > # Host: localhostDatabase: operatio_forum > # > # Server version 3.23.32 > > nothing else.. > and nothing is imported into the MySQL db :(( > > PLEASE help me as i need to have a working backup :( > > Greetings Helza Hi, Don't use mysqldump to restore, use mysql instead: mysql -uoperatio_operati -p* operatio_forum < database.sql Regards -- Joseph Bueno NetClub/Trader.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
restoring mysql db backup..
Hi, I've made an backup of my MySQL db using: mysqldump -uoperatio_operati -p* operatio_forum >> database.sql and with: mysqldump -uoperatio_operati -p* --quick --add-drop-table --add-locks --extended-insert --lock-tables operatio_forum > database.sql both produce a mysql back of around 2-3 mb... however when i try to import it with: mysqldump -uoperatio_operati -p* operatio_forum < database.sql nothing happens.. i just see: # MySQL dump 8.12 # # Host: localhostDatabase: operatio_forum # # Server version 3.23.32 nothing else.. and nothing is imported into the MySQL db :(( PLEASE help me as i need to have a working backup :( Greetings Helza
