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 END_SCRIPT 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 END_SCRIPT 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]
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: 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
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
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]
