vb.org Archive - Database backup

If you have root/ssh access then you are better off using mysqlhotcopy rather than mysqldump, it will generate db binaries that you can move around as desired, and is much faster. I recommend running it without indices, which can be rebuilt fairly quickly if needed.

For transfer to another server use rsync via ssh.

For automation use cron (the system one, not the vb one).

My backup script is written in python, here it is in its entirety. Note it maintains a 14 day rolling backup, and keeps symlinks to the most recent in the top level of the backup tree.

Code:

#!/usr/bin/python

import string, time

import commands





doy = time.gmtime()[7] % 15

dbpath1 = "/backup/DBBACKUP/"

dbpath2 = "/backup/DBBACKUP/d%s/" % doy

lfile = open('/var/log/backuplog', 'a')



dbs = ['vb','another_db','a_third_db']



for db in dbs:

        cmd ="/usr/bin/mysqlhotcopy -u root -p <mypassword> --allowold --noindices %s %s" % (db, dbpath2)

        tlink = '%s%s' % (dbpath1, db)

        slink = '%s%s' % (dbpath2, db)



        cmd2 = 'rm %s' % tlink

        cmd3 = 'ln -s %s %s' % (slink, tlink)





        lfile.write(time.ctime() + '\n')

        for x in (cmd, cmd2, cmd3):

                lfile.write(x + '\n')

                (s,o) = commands.getstatusoutput(x)

                lfile.write(o + '\n')

in /etc/cron.d I have a script that contains:
MAILTO=
SHELL=/bin/sh
5 3 * * * root /usr/local/backupscripts/dbbackup.py

For the remote transfer you need to set up an ssh key, instructions can be found here
http://pkeck.myweb.uga.edu/ssh/ Personally, I run the remote backup manually every other day, since I don't trust the integrity of my backup host (a shared server)

X vBulletin 3.8.12 by vBS Debug Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_code_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (4)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 bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages:

X vBulletin 3.8.12 by vBS Debug Information

Page Generation 0.00988 seconds
Memory Usage 1,722KB
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
(1)bbcode_code_printable
(1)footer
(1)gobutton
(1)header
(1)headinclude
(6)option
(1)post_thanks_navbar_search
(1)printthread
(4)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
bbcode_fetch_tags
bbcode_create
bbcode_parse_start
bbcode_parse_complete_precache
bbcode_parse_complete
printthread_post
printthread_complete

Messages:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.00988 seconds Memory Usage 1,722KB 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 (1)bbcode_code_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (4)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 bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: