YaBB SE Community

English User Help => FAQ => Topic started by: andrea on December 14, 2002, 01:01:39 AM

Title: FAQ: HowTo Save/Restore the database data
Post by: andrea on December 14, 2002, 01:01:39 AM
YaBB SE Database Tutorial: Save/Restore with phpMyAdmin (http://www.penthesilea.ch/yabbutils/save-instr/)
(the tutorial contains also a restore script of Dave Smulders which allows to restore big dump files)
Backup Script of Greg Robson (http://www.yabbse.org/community/index.php?board=1;action=display;threadid=7224)
Backup Database Mod by SK15 (http://www.yabbse.org/community/index.php?board=158;action=display;threadid=9635)
Title: Re:FAQ: HowTo Save/Restore the database data
Post by: andrea on December 14, 2002, 01:03:32 AM
Can this be moved into the FAQ board?

Questions:
Title: Re:FAQ: HowTo Save/Restore the database data
Post by: David on December 14, 2002, 01:08:19 AM
I also have a nice cronjob script that I use to do backups, willing to share it if there is interest.
Title: Re:FAQ: HowTo Save/Restore the database data
Post by: Omar Bazavilvazo on December 14, 2002, 01:19:39 AM
share it plz :)

I don't it won't hurt to share it to all ppl interested (included me) :P

ja ne!
Title: Re:FAQ: HowTo Save/Restore the database data
Post by: David on December 14, 2002, 01:28:09 AM
Very simple little script.  Replace the CAPS with the needed values.  For the paths you must use full paths, not relative.  In the end it creates the file timestamp-db.sql.gz.  You can rename the file if you wish but if you want the timestamp make sure you leave in $DATESTAMP.  You can run this from the command line if you make it executable or set it up to run via cron.

Code: [Select]
#! /bin/sh

DATESTAMP=`date +%m-%d-%Y-%H%M`

#backup and tar the database
mysqldump -uMySQLUSERNAME -pMySQLPASSWORD -B DBNAME > /PATHTOBACKUPSTORAGE/$DATESTAMP-db.sql
tar -czf /PATHTOBACKUPSTORAGE/$DATESTAMP-db.sql.tar.gz /PATHTOBACKUPSTORAGE/$DATESTAMP-db.sql
rm -f /PATHTOBACKUPSTORAGE/$DATESTAMP-db.sql
Title: Re:FAQ: HowTo Save/Restore the database data
Post by: Omar Bazavilvazo on December 14, 2002, 02:40:47 AM
Thanks!

I tried it :)

Manually, it worked (the script i created)

Now let's wait for Cpanel to execute the cron job :)

cool!

ja ne!
Title: Re:FAQ: HowTo Save/Restore the database data
Post by: Shindou Hikaru on December 14, 2002, 04:24:23 AM

Very simple little script.  Replace the CAPS with the needed values.  For the paths you must use full paths, not relative.  In the end it creates the file timestamp-db.sql.gz.  You can rename the file if you wish but if you want the timestamp make sure you leave in $DATESTAMP.  You can run this from the command line if you make it executable or set it up to run via cron.

Code: [Select]
#! /bin/sh

DATESTAMP=`date +%m-%d-%Y-%H%M`

#backup and tar bn_website database
mysqldump -uMySQLUSERNAME -pMySQLPASSWORD -B DBNAME > /PATHTOBACKUPSTORAGE/$DATESTAMP-db.sql
tar -czf /PATHTOBACKUPSTORAGE/$DATESTAMP-db.sql.tar.gz /PATHTOBACKUPSTORAGE/$DATESTAMP-db.sql
rm -f /PATHTOBACKUPSTORAGE/$DATESTAMP-db.sql


How cool! I set up the same thing for YaBB SE ;)
Title: Re:FAQ: HowTo Save/Restore the database data
Post by: dart on January 04, 2003, 12:31:33 AM
yep..
but how iam doing the installaion?
when iam gave the name of my database(i didn't created it first, couse i dont know how to creat it)
its giving me a problem:
" CONNOT USE THE GIVEN DATABASE
Title: Re:FAQ: HowTo Save/Restore the database data
Post by: mediman on January 08, 2003, 10:23:05 PM
our db dump/restore/optimizer (restore in development), the old standalone was http://www.yabbse.org/community/attachments/maindb2.zip, will be added in the admin.menu! we are working now on gzip and bzip the output!

mediman &  manju
Title: Whats the best way to back/move up your database?
Post by: Overseer on March 25, 2003, 10:03:35 AM
I'm moving hosts soon... current host is linux based so i have cpanel/phpmyadmin etc

cpanel provides a dbase backup function.. but is phpmyadmin's export better than this?

basically whats the best way to be sure i've downloaded a 'good' image of my database?

and also for ease of re-rupping it on the new host (which has the same features available)

of course I wont delete the 'old' one until i've got it running on the new host anyways...


(ohh yeah... my dbase is currently around 250 meg )
Title: Re:Whats the best way to back/move up your database?
Post by: [Unknown] on March 25, 2003, 11:20:12 AM
Do you have shell access?  (on both preferably...)

-[Unknown]
Title: Re:Whats the best way to back/move up your database?
Post by: Overseer on March 25, 2003, 11:27:41 AM
I definitely do where I am now... just checked and i will on the new host i'm looking at too (if i ask) ;D
Title: Re:Whats the best way to back/move up your database?
Post by: David on March 25, 2003, 04:00:06 PM
Then use the mysqldump tool.  Things in CAPS are for you to fill in.

On your current host, do this in a directory where it can be downloaded over the web.
Code: [Select]
mysqldump -uUSER -pPASSWORD DBNAME > backup.sql
gzip backup.sql

On your new host.
Code: [Select]
Make the new database
wget http://www.yoursite.com/backup.sql.gz
gunzip backup.sql
mysql -uUSER -pPASSWORD DBNAME < backup.sql
Title: Re:Whats the best way to back/move up your database?
Post by: Douglas on March 25, 2003, 08:03:26 PM
There should be a space between -u and USERNAME and -p and PASSWORD.  This is VERY important.  David, can you fix your post to reflect that?
Title: Re:Whats the best way to back/move up your database?
Post by: David on March 25, 2003, 08:04:56 PM
There should be a space between -u and USERNAME and -p and PASSWORD.  This is VERY important.  David, can you fix your post to reflect that?
I use it nightly with no space.
Title: Re:Whats the best way to back/move up your database?
Post by: [Unknown] on March 25, 2003, 08:47:14 PM
There indeed should be no space - it uses a more linux like syntax... imho, better convention.

mysqldump --opt -uUSER -pPASSWORD DBNAME > backup.sql

Might be good though....

-[Unknown]
Title: Re:Whats the best way to back/move up your database?
Post by: Overseer on March 26, 2003, 04:33:42 AM
ahh cool.. thanks for replies.. so I wont actually have to download the database and re-up it again. (altho i probably will just to have a backup)
kinda like FXPing :)
Title: Re:Whats the best way to back/move up your database?
Post by: Anguz on March 26, 2003, 02:15:08 PM
I don't have shell access, but I do have PHPMyAdmin and CPanel

how can I do what David explains with those?
Title: Re:Whats the best way to back/move up your database?
Post by: David on March 26, 2003, 04:21:38 PM
I don't have shell access, but I do have PHPMyAdmin and CPanel

how can I do what David explains with those?
Take a look at this guide.
http://www.penthesilea.ch/yabbutils/save-instr/index-en.php
Title: Re:Whats the best way to back/move up your database?
Post by: Anguz on March 26, 2003, 08:01:57 PM
Take a look at this guide.
http://www.penthesilea.ch/yabbutils/save-instr/index-en.php

thx Dave, that's a great tutorial, but after 10 tries, I wasn't able to complete the whole db transfer before it dropped  :(

so I asked my webhost and he said that I could have "shell access", so could you explain to me what "shell access" is and how exactly I should do what you mentioned earlier?

thx for your help, I'm really new to this
Title: Re:Whats the best way to back/move up your database?
Post by: David on March 26, 2003, 08:16:34 PM
Shell access is like Dos.  It is command line.  You need to use a program like Putty, http://www.chiark.greenend.org.uk/~sgtatham/putty/, to access it.  Your host will have all details as to how to connect.
Title: Re:Whats the best way to back/move up your database?
Post by: Anguz on March 27, 2003, 02:07:44 AM
thank you David for all your help :)
Title: Re:Whats the best way to back/move up your database?
Post by: Gastovski on March 27, 2003, 01:35:05 PM
I have backed up and migrated my forum onto the new server without any hiccups, apart from the fact that the topics do not show up. Anyone know why this would be?

In admin panel it shows the messages, but no topics... :-\
Title: Re:Whats the best way to back/move up your database?
Post by: Anguz on March 27, 2003, 01:42:40 PM
did you make sure the topics table was there when you migrated?
Title: Re:Whats the best way to back/move up your database?
Post by: Gastovski on March 27, 2003, 02:51:01 PM
Yup, weird. I messed around with setting up a new forum in a new database, and  then deleted that db, changing yabb to the falklands db, and it's worked. Looks like a case of buggy mySQL/YaBB. Anyway, thanks ;D
Title: Re:Faq: HowTo Save/Restore the database data
Post by: Overlord on June 03, 2003, 06:03:54 PM
err... I get an error with the cron one..
could someone help?

Code: [Select]
#!/bin/sh
mysqldump -uME -pMYPASS dbname > /home/me/dbname.sql

I get an email from cron saying

Quote
/home/me/backup: mysqldump: command not found

but when I run it from the command line, it is ok.
Title: Re:Faq: HowTo Save/Restore the database data
Post by: David on June 03, 2003, 07:15:29 PM
You shouldn't need the #!/bin/sh in the cron job.  Maybe try a full path to mysqldump.
Title: Re:Faq: HowTo Save/Restore the database data
Post by: Overlord on June 03, 2003, 07:16:56 PM
You shouldn't need the #!/bin/sh in the cron job.  Maybe try a full path to mysqldump.

Would you know what that is?

And the path #!/bin/sh isn't in my crontab file, it is in the backup.sh script I have crond run.
Title: Re:Faq: HowTo Save/Restore the database data
Post by: David on June 03, 2003, 07:18:36 PM
Can you run the script fine if you execute it?
Title: Re:Faq: HowTo Save/Restore the database data
Post by: Overlord on June 03, 2003, 07:20:21 PM
Yes.
Title: Re:Faq: HowTo Save/Restore the database data
Post by: Overlord on June 03, 2003, 07:29:49 PM
Would it help if I gave you shell access?
Title: Re:Faq: HowTo Save/Restore the database data
Post by: David on June 03, 2003, 08:33:50 PM
Would it help if I gave you shell access?
I doubt it.  Maybe talk to your host and see if they have any ideas.
Title: Re:Faq: HowTo Save/Restore the database data
Post by: andrea on June 03, 2003, 09:08:18 PM
try a full path to mysqldump.

Thats a good hint. Did you try this?

Furthermore make sure all environment you need to run the script in foreground is also set for the cron job.

The cron job runs in background and by a different user. Make sure it does not try to write any messages or errors to stdout. Redirect all messages and errors to a file or to /dev/null. Furthermore make sure that all needed environment variables are set in the backup script and the script has all permissions, try if it helps to set the "s" permission (chmod u+s yourbackupscript).
Title: Re:Faq: HowTo Save/Restore the database data
Post by: Overlord on June 04, 2003, 05:13:27 PM
What is the default path to mysqldump?
It is't in /usr/bin or /bin :(
Title: Re:Faq: HowTo Save/Restore the database data
Post by: Overlord on June 04, 2003, 05:27:38 PM
Ok, thanks for your help guys/girls. I had to add the full path to mysqldump (/usr/local/bin/mysqldump).

Thanks a million!