• Registered: 2004-11-19
  • Posts: 186

Topic: Backup Database Excluding Search Tables Restore To New Test DB

Hey everyone!

I cobbled together a process which allows me to do the following through ssh / shell (I use Putty)

1)  Backup my punbb DB EXCLUDING all the search tables (my DB is 90mb with search tables and only 35 without)
2)  Add drop tables if the table exists
3)  Restore the DB to a copy of my forum to a location where I can run tests on an exact copy of my production site without worrying about impacting the live forum

Here's what I do:

1)  Copy all files from my main forum to another location: 

cp -f -p -r /home/site/www/folder-production /home/site/www/folder-test

2)  Backup my main punbbDB and only the non-search tables:

 mysqldump --add-drop-table -u username -p db_name bans categories censoring config forums forum_perms groups online posts ranks reports subscriptions topics users  > /home/site/forumdb.sql

3)  Restore my main punbbDB and only the non-search tables to another DB I've already setup:

 mysql -u username -p NEWdb_name < /home/site/forumdb.sql

So, how does that look?  Is this a good way to setup a test environment and also have a perfect backup of my forum in case I need to redirect to it?

If you have any comments / suggestions I'd love to hear them.   Also, any way to completely automate this whole process into a single batch file?

Rob Ludlow 
www.Nifty-Stuff.com - Repository of all Stuff Nifty!
www.reviewum.com - Professor Ratings + Teacher Reviews
  • Registered: 2004-11-19
  • Posts: 186

Re: Backup Database Excluding Search Tables Restore To New Test DB

No ideas / suggestions?

I've got a question then:  If I copy all the files and the DB exactly, wouldn't my new forum location (www/folder-test) be pointing to the original DB?   I ran through all these steps and it seems like the new test install is working perfectly and independent of the production install...  now I'm just not sure why.  roll

Rob Ludlow 
www.Nifty-Stuff.com - Repository of all Stuff Nifty!
www.reviewum.com - Professor Ratings + Teacher Reviews
  • Registered: 2004-11-19
  • Posts: 186

Re: Backup Database Excluding Search Tables Restore To New Test DB

Okay, where are Connorhd or Smartys to help share their overflowing fountains of knowledge on this subject?  Here are my two questions:

1)  Do the three steps above look like a good way to copy my forum to a new folder / DB for testing?

2)  When I did the above it worked fine... but now I'm wondering why it worked?  yikes   If I copy the DB and the punbb folder exactly then how are config settings (based on the original folder and DB) not causing collision / config issues with the original install?  I guess I just don't know where the config settings for punbb are kept and why they weren't copied over?

Thanks in advance for any help you can provide!

Rob Ludlow 
www.Nifty-Stuff.com - Repository of all Stuff Nifty!
www.reviewum.com - Professor Ratings + Teacher Reviews
  • Registered: 2006-04-30
  • Posts: 382

Re: Backup Database Excluding Search Tables Restore To New Test DB

You forgot to change the base path.

  • Registered: 2004-11-19
  • Posts: 186

Re: Backup Database Excluding Search Tables Restore To New Test DB

I'm surprised there aren't more threads on this subject (or comments on this one).

One of my DB's for punBB is about 300 mb and most of that is the search tables.  Backing up and especially restoring such a monster DB is a major hassle.   

Any way I can clean up the above processes to create a macro / script to automatically do everything I've listed above?

Any other thougths / comments on backing up punBB and/or cloning it to a separate location to run tests and install mods in a non-production environment?

Rob Ludlow 
www.Nifty-Stuff.com - Repository of all Stuff Nifty!
www.reviewum.com - Professor Ratings + Teacher Reviews
  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Backup Database Excluding Search Tables Restore To New Test DB

I have fountains of knowledge on the subject of backups? tongue
What you're doing I would say is the best way, although I would personally generate two backups. One of schema, which contains all tables, and one of data, which contains non-search data. mysqldump offers those options.

As for automating it, for PunBB-Hosting I've modified the DB Management plugin to backup using mysqldump, that would be the most automation you could do I think (unless you make, say, a shell script to import the database from the command line that you would run by itself).

  • Registered: 2004-11-19
  • Posts: 186

Re: Backup Database Excluding Search Tables Restore To New Test DB

Thanks Smartys.  I've tasted the everlasting Kool-Aid from your fountain of knowledge on many occasions and I'm hooked.  You definitely know your stuff.

BTW, for anyone looking for a good DB backup script, I use this one here: http://www.ameir.net/blog/index.php?/ar … -v2.1.html

Here's what I do to try to help ensure that my DB won't get lost too easily:

1)  Create a daily copy of the existing forum database and compress it to a zip file
2)  Leave a copy of that backup on the local server and send a copy to another server thousands of miles away.
3)  Download all the daily backup files from that second server onto my local computer
4)  Every once in a while burn all the backups to a DVD and store them at another physical location.

Rob Ludlow 
www.Nifty-Stuff.com - Repository of all Stuff Nifty!
www.reviewum.com - Professor Ratings + Teacher Reviews

8 Reply by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Backup Database Excluding Search Tables Restore To New Test DB

reviewum.com wrote:

Any way I can clean up the above processes to create a macro / script to automatically do everything I've listed above?

It's obviously running on a *nix based system, so just write a shell script. and then pop an entry in cron for it. It's a doddle.

MattF's Website