FAQ: HowTo Save/Restore the database data

[andrea]

• with phpMyAdmin one can save or restore the db date or move them to a different server:
  YaBB SE Database Tutorial: Save/Restore with phpMyAdmin
  (the tutorial contains also a restore script of Dave Smulders which allows to restore big dump files)
  
• save/restore or move to a different server by script:
  Backup Script of Greg Robson
  
• by this mod a backup/restore script can be activated in the admin menu:
  Backup Database Mod by SK15
  
• The current version of YaBB SE contain a save option in the admin menu
  
• for big databases you will find shell commands for saving and restoring in the replies below
  

[andrea]

Can this be moved into the FAQ board?

Questions:

• is there a mod to put Gregs backup script into the admin menu?
• is Dave Smulders backup manager  in development?
• did I omit in the above list some different backup/restore method?

[David]

I also have a nice cronjob script that I use to do backups, willing to share it if there is interest.

[Omar Bazavilvazo]

share it plz

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

ja ne!

[David]

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 Expand

#! /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

[Omar Bazavilvazo]

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!

[Shindou Hikaru]

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 Expand

#! /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

[dart]

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

[mediman]

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

[Overseer]

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 )

[[Unknown]]

Do you have shell access?  (on both preferably...)

-[Unknown]

[Overseer]

I definitely do where I am now... just checked and i will on the new host i'm looking at too (if i ask)

[David]

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 Expand

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

On your new host.

Code Select Expand

Make the new database
wget http://www.yoursite.com/backup.sql.gz
gunzip backup.sql
mysql -uUSER -pPASSWORD DBNAME < backup.sql

[Douglas]

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?

[David]

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.