backing up large boards

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.
Darth Wong
Registered User
Posts: 2398
Joined: Wed Jul 03, 2002 5:20 am
Location: Toronto, Canada
Contact:

Post by Darth Wong »

One note about --opt; it adds the --add-drop-table --add-locks --all -e -quick --lock-tables options to mysqldump, but the board might not respond well when the tables are locked. Then again, if your board doesn't get much traffic at that time of night (whenever you back up), it might not matter either way. It should only affect people who are trying to post while you're backing up.
Not a three-foot tall green gnome in real-life: My home page.
My wretched hive of scum and villainy: http://bbs.stardestroyer.net/
User avatar
Black Fluffy Lion
Former Team Member
Posts: 6057
Joined: Sat Dec 15, 2001 11:37 am

Post by Black Fluffy Lion »

Interesting, thanks for the statistics, drathbun. I look forward to hearing any differences in the time taken to restore...
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by drathbun »

Darth Wong wrote: It should only affect people who are trying to post while you're backing up.

Yes, I have already allowed for / planned around that. I have a perl program in my backup shell script that sets the board configuration to "offline" for the duration of the backup. Otherwise, you (potentially) don't have a consistent backup...

It looks like mysqldump goes table by table. If it backs up the posts table, then the posts_text table, and someone was in the middle of posting, it would be possible to get a post_text without a post record. Which would be a bad thing. 8) So I have the following perl script that changes the state of the board from online to offline, and then back again once the backup script is done.

Code: Select all

#!/usr/bin/perl

use DBI;
use strict;

# set up path options
my($phpBBroot) = '/home/server/www/forum_path';

# db access variables
my $dbhq;
my($dbms, $dbhost, $dbname, $dbuser, $dbpasswd) = ('','','','','');
my($inputline) = '';

# read config file
open (CONFIG, "<$phpBBroot/config.php");
while (<CONFIG>)
{
        $inputline = $_;
        eval($inputline) if (substr($inputline,0,3) eq '$db');

}
close (CONFIG);
# set up database interface handle
$dbhq = DBI->connect("dbi:$dbms:database=$dbname;host=$dbhost","$dbuser","$dbpasswd");

# cursor to get current status
my($c_get_status) = $dbhq->prepare('
        SELECT  config_value
        FROM    phpbb_config
        WHERE   config_name = "board_disable"
');

# cursor to update board config
my($c_update_status) = $dbhq->prepare('
        UPDATE  phpbb_config
        SET     config_value = ?
        WHERE   config_name = "board_disable"
');


$c_get_status->execute();
my($board_status) = $c_get_status->fetchrow();
$board_status = ($board_status == 1) ? 0 : 1;
$c_update_status->execute($board_status);

$c_get_status->finish();

$dbhq->disconnect;

print ("board status is now $board_status\n");
This perl script reads the config file for the username, password, and db info. It then connects and switches the board status. If it was online, it becomes offline... if off, it becomes on.

Before writing this script I wrote another to capture the number of people online at 10 minute intervals. I was looking for any 10 minute time period where there were no people on the board on a regular basis. However, that never happened. ;-) So I use the disable / enable instead.

Dave
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image
ChristianWeb
Registered User
Posts: 42
Joined: Wed Nov 13, 2002 6:24 pm
Contact:

Post by ChristianWeb »

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


what is SSH? :?
User avatar
A_Jelly_Doughnut
Former Team Member
Posts: 34457
Joined: Sat Jan 18, 2003 1:26 am
Location: Where the Rivers Run
Contact:

Post by A_Jelly_Doughnut »

Secure SHell :) It replaced Telnet to a large degree.
A Donut's Blog
"Bach's Prelude (Cello Suite No. 1) is driving Indiana country roads in Autumn" - Ann Kish
cstotes
Registered User
Posts: 145
Joined: Tue Oct 08, 2002 6:47 am

Post by cstotes »

I've used the mySQLFront gui. I backed up everything with phpMyAdmin and then restored it with mySQLFront. It was only a 6MB file and it took 1 hour to restore.
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by drathbun »

But SSH doesn't give you a backup program. It just gives you access to the command line. From there you would use something like mysqldump, which is what we were talking about earlier.

Same thing for telnet. The difference (I've been told) between ssh and telnet is that telnet sends your password as clear text to the server, while ssh does not.

Telnet is generally port 23, and ssh on port 22.

Dave
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image
Darth Wong
Registered User
Posts: 2398
Joined: Wed Jul 03, 2002 5:20 am
Location: Toronto, Canada
Contact:

Post by Darth Wong »

drathbun wrote: But SSH doesn't give you a backup program. It just gives you access to the command line. From there you would use something like mysqldump, which is what we were talking about earlier.

Same thing for telnet. The difference (I've been told) between ssh and telnet is that telnet sends your password as clear text to the server, while ssh does not.

Telnet is generally port 23, and ssh on port 22.

Dave

SSH has two other really cool features (actually more than that, but two that are relevant here):

1) scp. This allows encrypted file transfers between your machine and the remote machine, so your entire database and all of its tables can't be intercepted enroute (while this is not a huge possibility, it is a possibility nonetheless).

2) public/private keypair authentication. This means you can create a keypair, put a copy of your public key in the .ssh/authorized_keys file on the target machine, and then ssh in without having to type your password. This is really handy for automating remote backup tasks.

My home machine is set up to automatically log into the server every night via SSH, run a backup of the database and phpbb directory, and then use scp to copy both files from the server onto my local system.
Not a three-foot tall green gnome in real-life: My home page.
My wretched hive of scum and villainy: http://bbs.stardestroyer.net/
Locked

Return to “2.0.x Discussion”