vb.org Archive - [vbcron] Automatic database backup [v1.3]

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vBulletin 3.0 Full Releases (https://vborg.vbsupport.ru/forumdisplay.php?f=33)

- - [vbcron] Automatic database backup [v1.3] (https://vborg.vbsupport.ru/showthread.php?t=62149)

[vbcron] Automatic database backup [v1.3]

This script will allow you to automaticly back-up your vBulletin database (or any database you have access to).

Current Stable Version: 1.3.1

System Requirements-

vBulletin RC 2 or later
Unix/Linux server with MySQL Dump support
Ability to chmod folders to 777 (775 works on some servers)

[high]IMPORTANT NOTE TO ALL THAT USE THIS SCRIPT[/high]

This script invokes an outside process that runs in the backround on your server to backup and compress your database. It is important to remember that this is not an instant process, do not enter you ftp as soon as the script says it is done and try to download your backup. It will likely be incomplete and your server will probbly still be writting the file to disk.

I ussally give the process an hour or two before I mess with the file, larger databases will take more time of course.

Installing the script-

1. You will need to change the information here for it to work correctly on your server:

PHP Code:


		
			
// ######################## Begin edits ######################

define('DB_HOST', 'localhost'); // database host



define('DB_USER', 'db_user'); // database username



define('DB_PASSWORD', 'password'); // database password



define('DB_NAME', 'db_name'); // vbulletins database name



define('DUMP_PATH', '/path/to/dump/folder'); // path to where db backup will be stored



define('DUMP_MAKE_TAR', 1); // set this to 1 for the backup to be compressed in a .tar.gz file

// ####################### End Edits ##########################

2. CHMOD the folder you specified in DUMP_PATH to 777

3. Upload the vbcron_db_backup_131.php file to the /vbroot/includes/cron directory

4. Enter you admin control panel, under Scheduled Tasks click [high]Add New Scheduled Task[/high]

Enter the information has follows:

Title: Automatic Database Back-up

Day of the Week:

This is the day of the week the task should run on. Set this to * to have it not apply, or for it to run every day (if Day of the Month is set to * below). Otherwise select a specific day. Note that this will override the day of the month option, unless this is set to *.

Day of the Month:
Set this to the specific day of the month the task should run. This is overridden by the Day of the Week setting above. Set this to * to have it not apply.

Hour:
This is the hour of the day the task should run. Set it to * to have it run every hour.

Minute:
This is the minute of the hour the task should run. Set it to * to have it run every minute (not really recommended!).

Log Actions:
If this is set to Yes, every time the scheduled task runs, a log will be made indicating that the task was run, and in certain cases, additional information will be provided (e.g. for the Birthday Email task, the usernames who were sent birthday wishes will be noted).

Filename:
Enter the path to vbcron_db_backup_131.php, ./includes/cron/vbcron_db_backup_131.php should work in most cases

What was added in 1.3.1

- Fixed all bugs reported in 1.3

What was added in 1.3

- Added ability for script to leave logs

Improvments to come:

- Email alert when db is begin backed up
- Close board when back-up is in process
- 'Lock down' db durring backup so no one can access it
- Table, by table back-up
- Clear session table before backup
- Make script more windows friendly
- Delete last .sql (or .tar.gz) file when doing current back-up
- [high]Ability to use hashed file names for back-ups[/high]
- [high]Save back-up to forgin server[/high]

To come: version 2.0

2.0 will introduce most, if not all the fetures listed above.

Public beta testing will begin in the next few days, expect a stable version in at least 2 weeks time.

How do we find out if we have MySQLdump on our server?

Quote:

Originally Posted by Boofo

How do we find out if we have MySQLdump on our server?

You can connect via SSH/telnet and try a mysqldump command from the CLI ...

Brad: you have a couple of Parse errors ... missing ";"'s in the DEFINE area ...

Code:

define('DB_HOST', 'localhost')

define('DB_USER', 'user')

Both need those pesky ;'s

EDIT: //

Also on the last line it should look like this

Code:

exec('mysqldump --add-drop-table -h ' .DB_HOST. ' -u' .DB_USER. ' -p' .DB_PASSWORD. ' ' .DB_NAME. ' 2>' .DUMP_PATH. '/error.txt >' .DUMP_PATH. '/' .DUMP_DATE. '.sql');

as you have a couple of extra bits you don't need ...

worked fine after that :) :)

Great idea!

One suggestion as an improvement ...

Include the facility to make this script also tar.gz the resultant SQL dump file, for compactness ...

Quote:

Originally Posted by Natch

One suggestion as an improvement ...

Include the facility to make this script also tar.gz the resultant SQL dump file, for compactness ...

I think you should be able to do that with this..

PHP Code:


		
			
// now execute the backup

exec('mysqldump --add-drop-table -h' .DB_HOST. ' -u' .DB_USER. ' -p' .DB_PASSWORD. ' ' .DB_NAME. ' 2>' .DUMP_PATH. '/' .DUMP_DATE. '-error.txt >' .DUMP_PATH. '/' .DUMP_DATE. '.sql');



// now archive and compress the backup

exec('tar -cSz --remove-files -C' .DUMP_PATH. ' -f' .DUMP_PATH. '/' .DUMP_DATE. '.tar.gz ' .DUMP_DATE. '.sql ' .DUMP_DATE. '-error.txt');

Natch sorry about that, I coded this real late :), ill fix the zip.

Pikok, ill add that to the file to, and probbly some more compresstion methods over time :)

Looks good, thanks Brad!

er... sorry for double posting. Just wondering, what is the difference between --opt and --add-drop-table?

This is a great hack, I'll install this one.

Also, good suggestion, Natch. I was on my way to request something like this. A sql file takes up lots of space when the forum is large (we have around 80,000 now), so making it a tar.gz is making the file compact and downloadable. ;)

Yay! Best hack so far!

EDIT: A suggestion. What about the script being able to disable the boards and display a "We're doing database maintenance, come back in 10 minutes" type of message? Is this possible?

Also, I get a Fatal error: Call to undefined function: query() in /var/www/vb3/includes/functions.php on line 501 error message once the script is done. What does it mean?

This worked great.. a few questions though -- Will it overwrite the old dump each day?
Also -- it also wrote an XXXXXXXX-error.txt file along with the dump but it's empty.

Worked great on RC4. Also worked with the tar.gz

Thanks =D

Quote:

Originally Posted by mharmon

This worked great.. a few questions though -- Will it overwrite the old dump each day?
Also -- it also wrote an XXXXXXXX-error.txt file along with the dump but it's empty.

this file, and the sql file, is deleted once the tar.gz process is finished.

Exactly what I was looking for! Thank you!

Hey guys, im busy today (I just got done dumping a ton of trash with a tractor! LOL) but if I get time I may update this one today, if not tomorrow.

Planned improvements:

Zip file type support
Delete last backup when doing current one
Close boards when backing up
Clear session table before backup
Options to NOT include stuff like the postcache in the backup

Mmh, now this is helpfull, just question. Is it also possible to dumb the file on an other server. We don't really have to place to store backups on the webserver. So I collect them at my own server instead at the moment, would be a dream if all the handelings I need to do would be taken over by this script.

TPB:

I'll look into that for you, however the backup would then consume alot of bandwidth with the transfer to the new server :)

after uploading to the server, how does it run? How often will it backup? More info please, and thanks!

Quote:

Originally Posted by anabolicedge

after uploading to the server, how does it run? How often will it backup? More info please, and thanks!

You would need to set up a new "scheduled task" or cron job with the script as the action ... otherwise it's just a nice file that don't do much ...

can someone explain this please? Maybe a text file with some instructions for us that dont know how.

thanks

Why not just have the script get the database info from config.php?

Great hack! *clicks install*

I modified it a tad to keep a "yesterday" and a "today". I'm sure your new version will incorporate something like this, I just couldn't wait. ;)

PHP Code:


		
			
// move the current file so we can always keep 2 backups

print('mv ' .DUMP_PATH. '/' .DB_NAME. '-today.tar.gz ' .DUMP_PATH. '/' .DB_NAME. '-yesterday.tar.gz 2> /dev/null');



// now execute the backup

print('mysqldump --add-drop-table -h' .DB_HOST. ' -u' .DB_USER. ' -p' .DB_PASSWORD. ' ' .DB_NAME. ' 2>' .DUMP_PATH. '/' .DB_NAME. '-' .DUMP_DATE. '-error.txt >' .DUMP_PATH. '/' .DB_NAME. '-' .DUMP_DATE. '.sql');



if (DUMP_MAKE_TAR == '1')

{

    // now archive and compress the backup

    print('tar -cSz --remove-files -C' .DUMP_PATH. ' -f' .DUMP_PATH. '/' .DB_NAME. '-today.tar.gz ' .DB_NAME. '-' .DUMP_DATE. '.sql ' .DB_NAME. '-' .DUMP_DATE. '-error.txt'); 

}

Well - this is just not working!!! I have configured it with my own details and have put it in the scheduled tasks list but when I do 'run now' no backup is run!!!

Does my server need to have 'Safe Mode' turned off by any chance? :ermm:

Quote:

Originally Posted by Brad.loo

Wonderful!!!

Quote:

Originally Posted by Mr. HillBilly

Why not just have the script get the database info from config.php?

exactly :)

Quote:

Originally Posted by Brad.loo

TPB:

I'll look into that for you, however the backup would then consume alot of bandwidth with the transfer to the new server :)

Well, we don't really have a big database (about 8k posts), and have plenty of bandwidth already, so a few more wouldn't be a problem.

Sounds good. Where could I found more info about it?

Question, Once it gets does it deletes the .sql file and leaves the tar.gz right?

I ran this my self from adminCP and my browser timed-out waiting for it to finish :|

At my vB3 the Script won't will be work :(
I click on "Do it now" and my vB says "All done."
On my FTP Server there's nothing to see about a backup :(

Plz help.

installed it, great hack,,

makes life easier

is there a way to date the file as it does it,

i.e.: friday.sql... sat.sql.. sun.sql and so on

and overwrite the same file as it come to it i.e.: fri.sql will overwrite fri.sql

any thoughts on this

Quote:

Originally Posted by ceo_tfw

is there a way to date the file as it does it,

i.e.: friday.sql... sat.sql.. sun.sql and so on

and overwrite the same file as it come to it i.e.: fri.sql will overwrite fri.sql

any thoughts on this

You should be able to change this line:

PHP Code:


		
			
$cur_date = date('U');

to this:

PHP Code:


		
			
$cur_date = date('l');

I haven't tested it but it should do it.

that did not work, it came up with 1.tar.gz

Quote:

Originally Posted by ceo_tfw

that did not work, it came up with 1.tar.gz

hmm... did you change it to a lower case L? It looks like a 1, but it should be a lower case L. I will test this out on my forums shortly.

I will try l (lower case L)

it looks like the number one to me (1)

I tryed it and it worked great, it created Sunday.sql

thanks for the help

cheers for that

Quote:

Originally Posted by Mr. HillBilly

I ran this my self from adminCP and my browser timed-out waiting for it to finish :|

Hmm, that shouldn't matter. It should still run the mysql dump. It did at my forums...

Can't wait for the update Brad! :D

Quote:

Originally Posted by Mr. HillBilly

Question, Once it gets does it deletes the .sql file and leaves the tar.gz right?

I ran this my self from adminCP and my browser timed-out waiting for it to finish :|

You need to chmod the directory for the backup to 777. It timed out on me too when I had it chmoded it to 775. ;)

How about the day of the week and the date behind it? Like this:

Sunday-Mar_3_2004.tz?

EDIT: I figured it out. This is what I have now:

$cur_date = date('l-F_j_Y');

and the filename is:

Monday-March_8_2004.tar.gz

Works like a charm, Brad! Thank you, sir. ;)

Does my server need to run with 'Safe Mode' OFF for this to work?

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01521 seconds Memory Usage 1,855KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (2)bbcode_code_printable (5)bbcode_php_printable (11)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (2)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (40)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: