backing up large boards

[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.

[Black Fluffy Lion]

Interesting, thanks for the statistics, drathbun. I look forward to hearing any differences in the time taken to restore...

[drathbun]

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

[ChristianWeb]

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

what is SSH?

[A_Jelly_Doughnut]

Secure SHell It replaced Telnet to a large degree.

[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.

[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

[Darth Wong]

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.