Database backup

[Markro]

Hello,

I own a fairly large forum (sql dump = ~2 GB)

I'm looking for an effective method of storing the mysql dump on the server the site is hosted on, also a copy of it on another backup server.

I need for this to be a automatic process and occur every 24 hours.

Does anyone have any idea how to do this?

---

or

---

Setting up a mirrored database type situation, where when a thread is posted and stored in the main server database, it is also instantly stored in the backup server database.

Does anyone have any idea how to do this?

[MoT3rror]

<a href="https://vborg.vbsupport.ru/showthread.php?t=106935&highlight=backup" target="_blank">https://vborg.vbsupport.ru/showt...ghlight=backup</a>

[Eikinskjaldi]

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.


#!/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)

[Dismounted]

The only reliable method of backing up and restoring a database is with shell access via telnet or ssh. This is because backing up with a PHP script like that in the Admin CP or phpMyAdmin can result in PHP timeouts errors and an incomplete backup file. Please see the instructions here:

Backup:
http://www.vbulletin.com/docs/html/maintenance_ssh_backup

Restore:
http://www.vbulletin.com/docs/html/maintenance_ssh_restore

P.S. If you don't have shell access, some people have also reported success with these scripts:

MySQLDumper:
http://www.mysqldumper.de/en/index.php

MySQLHotxcopy:
http://www.vbulletin.com/forum/showthread.php?t=134821&highlight=mysqlhotcopy

Bigdump:
http://www.ozerov.de/bigdump.php