backing up large boards

LucidParody · Post by **LucidParody** » Wed Jul 09, 2003 9:51 pm

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?

A_Jelly_Doughnut · Post by **A_Jelly_Doughnut** » Wed Jul 09, 2003 9:55 pm

SSH is the most efficient way to backup or restore a DB, but some hosts don't support it.

Wert · Post by **Wert** » Wed Jul 09, 2003 11:13 pm

If your host gives you telnet access, that is the way to go. Fast and efficient.

LucidParody · Post by **LucidParody** » Thu Jul 10, 2003 1:49 am

what are the commands to back up and restore via ssh?

drathbun · Post by **drathbun** » Thu Jul 10, 2003 3:52 am

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

Darth Wong · Post by **Darth Wong** » Fri Jul 11, 2003 5:54 am

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.

drathbun · Post by **drathbun** » Fri Jul 11, 2003 1:40 pm

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

Black Fluffy Lion · Post by **Black Fluffy Lion** » Fri Jul 11, 2003 2:17 pm

-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

drathbun · Post by **drathbun** » Fri Jul 11, 2003 4:33 pm

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

A_Jelly_Doughnut · Post by **A_Jelly_Doughnut** » Fri Jul 11, 2003 4:49 pm

According to Darth Wong and BFL, yes.

It also makes a difference on small ( <1 MB ) backups.

Black Fluffy Lion · Post by **Black Fluffy Lion** » Fri Jul 11, 2003 5:23 pm

--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

drathbun · Post by **drathbun** » Fri Jul 11, 2003 8:24 pm

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

Black Fluffy Lion · Post by **Black Fluffy Lion** » Fri Jul 11, 2003 8:50 pm

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

drathbun · Post by **drathbun** » Fri Jul 11, 2003 9:27 pm

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

drathbun · Post by **drathbun** » Sat Jul 12, 2003 8:20 pm

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

advertisements