Looking for Large database backup & Restore SCRIPTS

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.
pengrus
Registered User
Posts: 566
Joined: Mon Dec 02, 2002 6:13 am
Contact:

Looking for Large database backup & Restore SCRIPTS

Post by pengrus »

I have had difficult experience with Restoring Large phpBB database (25MB+), my problem is either time out, or exceeding the max upload size set by php ini.

I am looking for a free or commercial script specifically for backup and restore the Large mysql database that will avoid the above problem. I have read somewhere that there are some commercial version that will do a great job on this. I don't mind paying for it. I wonder if anyone can share with us if they know some of the scripts.

Please do not reply with pieces of code that will do the job. I would really like to use a robust and easy to use program, not through the shell access.

Thank you very much!
Darth Wong
Registered User
Posts: 2401
Joined: Wed Jul 03, 2002 5:20 am
Location: Toronto, Canada
Name: Michael Wong
Contact:

Post by Darth Wong »

I don't understand; how do you plan to use a shell script without going "through the shell access"?

I've got a script, but it's kind of impossible to use without shell access (the only way to get around that on an account with no SSH is to crontab it, since cpanel servers will let you set up a cronjob IIRC).
Freedom of speech is not absolute, nor was it ever meant to be. If it were, then fraud would not be illegal.
User avatar
DaxAra
Registered User
Posts: 6
Joined: Wed Sep 10, 2003 3:31 am
Location: The Netherlands
Contact:

Post by DaxAra »

but sorry, dont know a script that does the trik for u!
have u tried doing this using PHPmyAdmin? (dont know if there is an timeout difference)

you could also split the xxx.sql file (the output file).
then u could Re-Insert it into the database in (for example) 5 pecess (5x5mb files).
i'm willing to do this for you.
Darth Wong
Registered User
Posts: 2401
Joined: Wed Jul 03, 2002 5:20 am
Location: Toronto, Canada
Name: Michael Wong
Contact:

Post by Darth Wong »

I know you don't want to, but the best way to back up a large database properly is to use a shell script. I know you said you don't want a code fragment, but I'm going to provide one anyway :)

Code: Select all

#!/bin/sh
# Backs up phpBB database and directory to home directory with datestamp
# Note: disables board during backup

# Set variables
STAMP=`date +%Y%m%d`
TARGET="/home/data/backups"
PHPBB="/var/www/html"
PRE="phpbb_"
USER="username"
PASS="password"
DB="phpbb"
HOST="localhost"

# Disable board
echo "update ${PRE}config set config_value=1 where config_name='board_disable';" | mysql -h${HOST} -u${USER} -p${PASS} ${DB}

# Back up database
mysqldump -e -q -h${HOST} -u${USER} -p${PASS} ${DB} | gzip - > ${TARGET}/${STAMP}_backup.sql.gz

# Back up main directory
cd $PHPBB
tar -cf - . | gzip - > ${TARGET}/${STAMP}_backup.tar.gz

# Re-enable board
echo "update ${PRE}config set config_value=0 where config_name='board_disable';" | mysql -h${HOST} -u${USER} -p${PASS} ${DB}
I suppose if I were an actual programmer instead of a lazy hack, I could set it up to harvest the variables from config.php. Oh well.

EDIT: whoops, I posted the debug version of the script! I fixed it.
Last edited by Darth Wong on Thu Sep 11, 2003 2:31 am, edited 2 times in total.
Freedom of speech is not absolute, nor was it ever meant to be. If it were, then fraud would not be illegal.
OskarMaria
Registered User
Posts: 15
Joined: Mon Apr 08, 2002 9:08 am

Post by OskarMaria »

You can try mywitch (http://www.mywitch.de). This is a commercial programm, which creates a little php-programm. You can run this on your webserver for administration of your sql database.

It is possible to backup and to restore large databases.

OM
pengrus
Registered User
Posts: 566
Joined: Mon Dec 02, 2002 6:13 am
Contact:

Post by pengrus »

Thanks for everyone who has replied. Also thanks to Wong for providing the code. Is it missing the restore step?

I am mainly looking for script/application that is similar to mywitch as OskarMaria posted. Take a look at the screenshots here:

http://www.mywitch.de/html/index.php4?tabelle=713

Any other similar programs that I can look into?

Thank you!
hostingstuff
Registered User
Posts: 43
Joined: Thu Jul 31, 2003 8:51 pm
Location: Natl Champs OSU!
Contact:

Post by hostingstuff »

I do not know of a way to do it without shell access, sorry. My suggestion would be to break down the files and upload the smaller files.

I did not get a chance to look at the mywitch program in detail but it loks like it could be a possibility for you.
Darth Wong
Registered User
Posts: 2401
Joined: Wed Jul 03, 2002 5:20 am
Location: Toronto, Canada
Name: Michael Wong
Contact:

Post by Darth Wong »

pengrus wrote: Thanks for everyone who has replied. Also thanks to Wong for providing the code. Is it missing the restore step?

Yeah, it's an automated backup routine. Meant to be run from a cronjob. Restoration is not something you would normally do on an automated basis, since a restoration is typically done because of some changing circumstance (eg- switching webhosts), and you will usually have to make some manual changes. Not that it would be hard to write up a restore script, but there are usually quite a few operations that have to be done when you're restoring a whole board; it's not as simple as just restoring the database itself.
I am mainly looking for script/application that is similar to mywitch as OskarMaria posted. Take a look at the screenshots here:

http://www.mywitch.de/html/index.php4?tabelle=713

Any other similar programs that I can look into?

Thank you!

Those programs are neat, but you really need to synchronize your backup of the database and the phpBB directory or the restored version will have messed-up avatars. That's why a custom script works better.

Besides, I never really understood why someone would pay for something so easily done yourself, but I guess that's just me.
Freedom of speech is not absolute, nor was it ever meant to be. If it were, then fraud would not be illegal.
pengrus
Registered User
Posts: 566
Joined: Mon Dec 02, 2002 6:13 am
Contact:

Post by pengrus »

Wong,

Thanks for your reply... I have no problem with backup the database. But your script is a great tool. I am having problems with Restoring the Large database, and that's the script that I am looking for.

I have searched through the board, and I didn't find a perfect one.

Issues:

file limitation
time out

I would like to have easy to use script with a nice interface (at least providing a feedback on the progress, like processing, restored successfully, etc...).

Can you write a script for this?

Thank you very much!!!
Die_H2k
Registered User
Posts: 121
Joined: Sun Mar 17, 2002 6:25 pm
Contact:

Post by Die_H2k »

hey similiar problem ;)
still trying to restore a backup to a new site
something happen
my friend when backup from acp with gzip on and all (data and ...)
the file was 10 mb file (gzip) but when extracted its 112 mb
i'm shock ofcourse cos i see in the admin index its only 25 mb gzip on
and my friend say all the backup are 1xx mb (montlhy backup)
so anyway i try to backup using phpmyadmin and all tables and all data and ...(structure)
its only 10 mb gzip and extracted only 26 mb

so strange lol ;)
anyway cant try it myself cos i'm using dial up and its just to big to download ;)

anyway back to the problem ;)

there is a script showed by an user here he was posting the the script (php script) in his forums and i try to get a new version and there is a new version ;)
here is the link http://www.ozerov.de/bigdump.php
so its basicly will do the upload to the sql server time by time (see yourself the script) the problem its seem my friend backup are mess
cos its stopped on line 49xxx and cant continue neither way i cant edit the file cost its big ;) and cant tell my friend to fix the mess file and upload it again (cos he already upload third time (100 mb file))
and i try it on localhost strangely its doesnt work only works at session one (cannot continue to sessions two)

so maybe anyone can try it and give comment ;)
and anyway you must to edit the php file itself and fill it with your password and stuff

anyway can i do
mysql -u myuser -ppass xxpir-xx261 < /xratw4at.com/test.sql
using crontab ??
and what is the command for it (what should i fill for the time date and stuff) ??


sorry bad english ;)
Image
Die_H2k
Registered User
Posts: 121
Joined: Sun Mar 17, 2002 6:25 pm
Contact:

Post by Die_H2k »

hey have try the script
its work nicely..
just dont forget to del it after you run it
its seem its error cos my db backup from phpbb are corrupted (maybe because a mod)

restoring 25 mb backup in just couple minutes on dial up
ofcourse you need to upload the backup file first ;)

but not try the contrab. No time need time to rest
12 hours online lol
Image
Darth Wong
Registered User
Posts: 2401
Joined: Wed Jul 03, 2002 5:20 am
Location: Toronto, Canada
Name: Michael Wong
Contact:

Post by Darth Wong »

OK, here's a restore script I made a while ago. It asks a lot of questions so it's not the most convenient, but restoration is a more complex operation than backup, and should be handled with more care.

Code: Select all

#!/bin/sh
# Restores phpBB database and directory

echo "Before running this script, you must:"
echo "1) Make sure the MySQL database exists and is empty."
echo "2) Make sure the destination directory exists, is writable, and is empty."
echo -n "Do you wish to continue? (y or n) "
read CONFIRM

if [ $CONFIRM = "y" ]; then
        # get system variables
        echo ""
        echo -n "Please enter your table prefix (default \"phpbb_\") "
        read PRE
        if [ -n $PRE ]; then
                PRE="phpbb_";
        fi
        echo -n "Please enter your MySQL username: "
        read USER
        echo -n "Please enter your MySQL password: "
        read PASS
        echo -n "Please enter your MySQL database name: "
        read DB
        echo -n "Please enter your MySQL hostname (default \"localhost\"): "
        read HOST
        if [ -n $HOST ]; then
                HOST="localhost";
        fi;
else
        exit;
fi

# Get names of backup files and target directory
echo ""
echo -n "Please enter database backup filename: "
read DBSRC
if [ ! -e $DBSRC ]; then
   echo "That file does not exist. Look for a file with a .sql.gz extension."
   exit;
fi
echo -n "Please enter phpBB directory backup filename: "
read DDSRC
if [ ! -e $DDSRC ]; then
   echo "That file does not exist. Look for a file with a .tar.gz extension."
   exit;
fi
echo -n "Please enter the directory to which you plan to restore phpBB:"
read PHPBB
if [ ! -e $PHPBB ]; then
   echo "That directory does not exist. Please check the name or create the directory and try again."
   exit;
fi

# Restore main directory
echo "Restoring the phpBB program directory ..."
zcat ${DDSRC} | (cd ${PHPBB} ; tar xvfB -)
echo "The phpBB program directory has been restored. Please edit config.php as necessary."

# Restore database
echo "Restoring the database (this may take quite a while) ..."
zcat ${DBSRC} | mysql -h${HOST} -u${USER} -p${PASS} ${DB}
echo "Database restored."

# Fix file permissions
echo "Fixing file permissions ..."
cd ${PHPBB}
find * -type d -exec chmod 755 {} \;
find * -type f -exec chmod 644 {} \;
chmod -R 777 images/avatars cache

# Re-enable board
echo "update ${PRE}config set config_value=0 where config_name='board_disable';" | mysql -h${HOST} -u${USER} -p${PASS} ${DB}
echo "File permissions fixed and board enabled.";
Note that the script is a little "dumb", ie- it doesn't actually check error codes and exit status flags to see if everything actually went smoothly. It also assumes that your backup files are gziped, which is usually the case. But it does work if your parameters are all entered correctly, etc.
Freedom of speech is not absolute, nor was it ever meant to be. If it were, then fraud would not be illegal.
Darth Wong
Registered User
Posts: 2401
Joined: Wed Jul 03, 2002 5:20 am
Location: Toronto, Canada
Name: Michael Wong
Contact:

Post by Darth Wong »

Die_H2k wrote: anyway can i do
mysql -u myuser -ppass xxpir-xx261 < /xratw4at.com/test.sql
using crontab ??
and what is the command for it (what should i fill for the time date and stuff) ??

If your sql file is gzipped, do this:

Code: Select all

zcat test.sql.gz | mysql -hhost -uuser -ppass dbname
In a crontab, do this:

Code: Select all

0 6 * * * zcat test.sql.gz | mysql -hhost -uuser -ppass dbname
This would execute at 6am. Of course, you would want to delete the crontab entry after it runs, or it will do this every day.
Freedom of speech is not absolute, nor was it ever meant to be. If it were, then fraud would not be illegal.
pengrus
Registered User
Posts: 566
Joined: Mon Dec 02, 2002 6:13 am
Contact:

Post by pengrus »

Thanks, Die_H2k, for sharing that script! It looks like a very nicely written script. And glad to hear that it worked for you! Did it work? Really? 8O :lol: Just want to confirm.

I just want a script like this to do a dirty job and giving you some feedback during the process. Now it looks like this is it. Need more testing on it. Could someone else could test this out too?

Wong's script is simple to use, but I am a little concerned as it does not give you any error confirmation. Does your script get around the file size, time out and memory limitation issues?? Thanks a lot for sharing your script, Wong! You are soooo goooood!
Die_H2k wrote: hey have try the script
its work nicely..
just dont forget to del it after you run it
its seem its error cos my db backup from phpbb are corrupted (maybe because a mod)

restoring 25 mb backup in just couple minutes on dial up
ofcourse you need to upload the backup file first ;)

but not try the contrab. No time need time to rest
12 hours online lol
Darth Wong
Registered User
Posts: 2401
Joined: Wed Jul 03, 2002 5:20 am
Location: Toronto, Canada
Name: Michael Wong
Contact:

Post by Darth Wong »

pengrus wrote: Thanks, Die_H2k, for sharing that script! It looks like a very nicely written script. And glad to hear that it worked for you! Did it work? Really? 8O :lol: Just want to confirm.

It looks like it splits up SQL files into chunks. It should work in theory, although it's kind of a kludge by its nature.
Wong's script is simple to use, but I am a little concerned as it does not give you any error confirmation. Does your script get around the file size, time out and memory limitation issues??

The filesize, timeout, and memory limitation issues are a function of PHP's built-in restrictions, and my script avoids all of them by simply not using PHP. It directly calls the mysqldump executable. I run it via crontab every day on my own board, which has a database in excess of 800MB.

It's a more reliable way of doing backups, particularly since it can be scheduled and requires no human intervention at all (although you might want to delete or archive old backups because they'll build up in the directory over time).
Freedom of speech is not absolute, nor was it ever meant to be. If it were, then fraud would not be illegal.
Locked

Return to “2.0.x Discussion”