backing up large boards

The 2.0.x discussion forum has been locked; this will remain read-only. The 3.0.x discussion forum has been renamed phpBB Discussion.
LucidParody
Registered User
Posts: 208
Joined: Thu Oct 24, 2002 8:15 am
Contact:

backing up large boards

Post by LucidParody »

how do you go about backing up the database for a large board? like 5000+ users or whatnot? My board is still small now, and i've been downloading the bbs database gziped, but as it grows bigger, what will be a more efficiant way to back it up?
User avatar
A_Jelly_Doughnut
Former Team Member
Posts: 34457
Joined: Sat Jan 18, 2003 1:26 am
Location: Where the Rivers Run
Contact:

Post by A_Jelly_Doughnut »

SSH is the most efficient way to backup or restore a DB, but some hosts don't support it.
A Donut's Blog
"Bach's Prelude (Cello Suite No. 1) is driving Indiana country roads in Autumn" - Ann Kish
Wert
Former Team Member
Posts: 3678
Joined: Tue Jul 03, 2001 8:33 pm
Location: Sacramento, CA
Name: Chris Aguilar

Post by Wert »

If your host gives you telnet access, that is the way to go. Fast and efficient.
Chris Aguilar - AKA "Wert"
LucidParody
Registered User
Posts: 208
Joined: Thu Oct 24, 2002 8:15 am
Contact:

Post by LucidParody »

what are the commands to back up and restore via ssh?
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by drathbun »

I use mysqldump to extract the data from my database. I have a shell script scheduled in cron. I have 7 generations (in theory, I have 8 when I look right now) of backups, which are done on Saturday just after midnight.

I have 3000+ users, 61K+ posts, and my gzipped backup file is 25,073,125 bytes. When I remember to do so, I download the files to my own workstation. I'm in the process of automating that part as well.

The entire backup process takes less than 1 minute to run. Here's part of my backup script... since the "keep 7 only" doesn't seem to be working at the moment, I won't post it...

Code: Select all

date

# disable board
SERVERPATH/board_status.pl

# set path and db name variables
dbpath="MYSERVER/db_backups"
dbname="MYDBNAME"
output_file="$dbpath/$dbname/dump.sql"

# do the backup
/usr/local/mysql/bin/mysqldump -h localhost -u USERNAME --password=PASSWORD $dbname > $dbpath/$dbname/dump.sql 2>&1

# create new backup filename
filename="$dbpath/$dbname/database_`date +%Y-%m-%d`.sql.gz"

# zip, and copy the output
gzip $output_file
mv $output_file.gz $filename

chmod 644 $dbpath/$dbname/*

# enable board
SERVERPATH/board_status.pl

date
The purpose of the call to "date" at the beginning and the end is to track how long the process takes. The perl script board_status.pl takes the board offline during the backup, and returns it to online after the backup is done.

The reason for setting parameters for the dbname and path is that eventually I will use this script on several boards. So I want one backup script for all of them.

The most important step in any back up process is to TEST YOUR BACKUP!!! I used to work for a software company that sold a back up product, and you can't imagine how many times people assumed that everything was going well because the back up was running. But they never tried to restore.

So at your first (and on some repetitive basis) chance, test your back up. Make a new forum, and restore your data. Test it out. If it works, do it again about once a quarter, or after major mods, or upgrades, or whatever. But test. 8)

Dave
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image
Darth Wong
Registered User
Posts: 2398
Joined: Wed Jul 03, 2002 5:20 am
Location: Toronto, Canada
Contact:

Post by Darth Wong »

Add the -e and -quick parameters to your mysqldump command. They will make the restore much quicker (the -e parameter is especially crucial, and can make a HUGE difference in restore times).

My DB currently weighs in at more than 600MB, and I have transferred hosts many times. Without the -e parameter, it would take a REALLY long time to restore my DB (particularly the search_wordmatch table).

Most people don't use the -e parameter because their DB isn't big enough for it to make a difference, but on a big DB it really does vastly speed up that restore. I've backed up, moved, and restored my board at another server in 15 minutes, which is no mean feat for a board with a DB hundreds of MB in size.
Not a three-foot tall green gnome in real-life: My home page.
My wretched hive of scum and villainy: http://bbs.stardestroyer.net/
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by drathbun »

Darth Wong wrote: Add the -e and -quick parameters to your mysqldump command. They will make the restore much quicker (the -e parameter is especially crucial, and can make a HUGE difference in restore times).

Okay, I know, "RTFM" ;-) but what does -e do?

Dave
User avatar
Black Fluffy Lion
Former Team Member
Posts: 6057
Joined: Sat Dec 15, 2001 11:37 am

Post by Black Fluffy Lion »

-e makes mySQL backup the data into INSERTs which contain multiple inserts in one go (e.g. INSERT INTO table (col1, col2) VALUES ('blah', 2), ('stuff', 3), ('more blah', 4)) instead of using multiple inserts to do it, resulting in a shorter backup file.

Instead of using -e and --quick, I would recommend using --opt, which combines a number of parameters, including -e and -q, to give a very quick backup
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by drathbun »

How does it affect the restore? My backup runs in less than 45 seconds now, but the restore takes forever. Will these options help that as well?

Dave
User avatar
A_Jelly_Doughnut
Former Team Member
Posts: 34457
Joined: Sat Jan 18, 2003 1:26 am
Location: Where the Rivers Run
Contact:

Post by A_Jelly_Doughnut »

According to Darth Wong and BFL, yes.

It also makes a difference on small ( <1 MB ) backups.
A Donut's Blog
"Bach's Prelude (Cello Suite No. 1) is driving Indiana country roads in Autumn" - Ann Kish
User avatar
Black Fluffy Lion
Former Team Member
Posts: 6057
Joined: Sat Dec 15, 2001 11:37 am

Post by Black Fluffy Lion »

--opt should speed up the restore as well. According to the mySQL manual, --opt combines the options --quick --add-drop-table --add-locks --extended-insert --lock-tables. Some of these, such as --quick, are designed to just speed up the backup; others, such as --extended-insert, are designed to cut down on the query and thus file size, which speeds up both backup and restore; and some, such as --add-locks, are designed to make restoring the data quicker. So, yes, --opt should speed up both backups and restores
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by drathbun »

In my preliminary test, using --opt reduced the time to make the backup by 25%, and reduced the size of the backup file to about 1/2 of a percent 8O of the original.

Without the --opt, my backup output file is 220M. With the --opt it is 1M. I am going to have to review this further and make sure it's actually working, but it looks really promising.

I can see one important difference... they disable the keys during the inserts, which makes the insert process substantially faster. Then the keys are rebuilt after the inserts are done. Very nice stuff, thanks for the tip. 8)

Dave
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image
User avatar
Black Fluffy Lion
Former Team Member
Posts: 6057
Joined: Sat Dec 15, 2001 11:37 am

Post by Black Fluffy Lion »

You're welcome. I just hope the change from 220 to 1MB won't mean the backup is corrupt or something, it is quite a large shrinkage ;)
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by drathbun »

Black Fluffy Lion wrote: You're welcome. I just hope the change from 220 to 1MB won't mean the backup is corrupt or something, it is quite a large shrinkage ;)

I just took a very fast look; I may have missed a digit. 8) I'm going to download the backup and restore on my server at home, which will be a nice test. If all the data comes through, and the restore runs faster than it did last time I tested, I'll be a very happy camper.

Dave
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by drathbun »

Okay, either there was something wrong with my eyes, or the backup had not completed when I made the prior post. Here are the results from my normal backup process last night.

I made the --opt change to my backup script, and when it ran the backup file decreased slightly. The backup files are gzipped, and it went from about 25MB to 23MB. However, when I unzipped the file, the "old" style backup was 214,913,636 bytes and the "new" style (with --opt) was only 99,592,425 bytes. So it went from 214MB to 99 MB, or about half.

And it took about the same amount of time as before.

I'll report later on the time it takes to restore. But given that there will be - at the very least - about half of the bytes to read / write, the restore should be substantially faster. 8)

Dave
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image
Locked

Return to “2.0.x Discussion”