die Seilerwerks

I maintain a drupal codebase that hosts multiple sites. I’ve been shamefully lax in getting regular backups of those files and databases, until today. Here is a pretty basic bash script that will create a bzip2 archive of the drupal codebase (including site-specific dirs), and then create mysqldump exports of each site’s database and gzip those files. This particular script requires that the drupal install directory be named “drupal”, but you can change this easily enough to suit your needs.

This does require a ~/.drupalsites file that contains the needed database login info (suggest chmod 600). Like I said, nothing too clever.

#!/bin/bash
DATESTAMP=`date +%Y%m%d`
SITESFILE=${HOME}/.drupalsites
# SITESFILE contents are in the following format:
# SITENAME:DBNAME:DBUSER:DBPASSWD
DRUPALDIR=/path/to/drupal
BACKUPDIR=${HOME}/backups/${DATESTAMP}

mkdir -p ${BACKUPDIR}

# Backup the drupal codebase
echo -n "Backing up ${DRUPALDIR} ... "
cd ${DRUPALDIR}/../
tar -cjpf ${BACKUPDIR}/drupal_${DATESTAMP}.tar.bz2 drupal
echo "Done."

cd ${BACKUPDIR}

# Backup MySQL Databases
cat $SITESFILE | while read line; do
        #echo $line
        line=(${line//:/ })
        echo -n "Backing up MySQL db ${line[1]} ... "
        DUMPFILE=${line[0]}_${DATESTAMP}.sql
        mysqldump -u ${line[2]} -p${line[3]} ${line[1]} > ${DUMPFILE}
        gzip ${DUMPFILE}
        echo "Done."
done

echo "Backup completed, all files are in ${BACKUPDIR}."

This script runs fine from cron, I have it scheduled for a weekly run. I then plan to rsync this to my home server for an offsite copy, even though the hosting service provides backups as well.

Reposted from The Pythian Group blog.

In the process of scripting a database migration, I was in need of something akin to the GNU basename utility that I know and love on Linux. basename is most famous for taking a full file path string and stripping away the leading path component, returning just the name of the file. This can be emulated in PL/SQL with calls to SUBSTR and INSTR, like this:

substr(dirname,instr(dirname,'/',-1)+1)

(Thanks to Ian Cary, who shared this logic on oracle-l)

As you can see, this simply finds the last occurence of /, which is our directory separator on *nix and Solaris operating systems. On Windows, it would be \. It then returns a substring beginning one character after that last separator until the end of the string. Voila, a basic basename routine!

Upon reading the basename man page again, I found that basename also takes an optional parameter, a suffix string. If this suffix string is provided, basename will also truncate that string from the end. For example:

$ basename /home/seiler/bookmarks.html
bookmarks.html
$ basename /home/seiler/bookmarks.html .html
bookmarks

I decided that this would be handy to have, and set out to create a compatible basename function in PL/SQL. Here is what I came up with:

CREATE OR REPLACE FUNCTION basename (v_full_path IN VARCHAR2,
                                        v_suffix IN VARCHAR2 DEFAULT NULL,
                                        v_separator IN CHAR DEFAULT '/')
        RETURN VARCHAR2
        IS
                v_basename VARCHAR2(256);
        BEGIN
                v_basename := SUBSTR(v_full_path, INSTR(v_full_path,v_separator,-1)+1);
                IF v_suffix IS NOT NULL THEN
                        v_basename := SUBSTR(v_basename, 1, INSTR(v_basename, v_suffix, -1)-1);
                END IF;

                RETURN v_basename;
        END;
/

I’ve also added an optional third parameter to specify a directory separator other than the default. It would probably be rarely useful, but not hard to remove if you don’t like it. As you can see, I’ve used similar SUBSTR/INSTR logic to identify the suffix index and prune it out.

Here it is in action:

SQL> COLUMN file_name FORMAT a45;
SQL> COLUMN basename FORMAT a15;
SQL> COLUMN no_suffix FORMAT a12;
SQL> SELECT file_name
  2          , basename(file_name) as basename
  3          , basename(file_name, '.dbf') as no_suffix
  4  FROM dba_data_files;

FILE_NAME                                     BASENAME        NO_SUFFIX
--------------------------------------------- --------------- ------------
/u01/app/oracle/oradata/orcl/users01.dbf      users01.dbf     users01
/u01/app/oracle/oradata/orcl/sysaux01.dbf     sysaux01.dbf    sysaux01
/u01/app/oracle/oradata/orcl/undotbs01.dbf    undotbs01.dbf   undotbs01
/u01/app/oracle/oradata/orcl/system01.dbf     system01.dbf    system01
/u01/app/oracle/oradata/orcl/example01.dbf    example01.dbf   example01

I hope this makes your work just a little bit easier, as it has mine.

While in Schaumburg, Illinois last week for the Oracle DBA Workshop II, I was taking some photos on my Motorola RAZR camera phone, in the hopes of posting them for my 5-year-old daughter to see. I needed to clear up some misconceptions, since she was under the impression that I “sleep at the school.” I soon learned, however, that transferring photos from the RAZR one-at-a-time over bluetooth to my Fedora 8 laptop became tedious, and waiting for a response from the GUI file browser was just frustrating. Enter: ObexFTP.

Thanks to a tip from a friend, I found ObexFTP and, in my quest to force myself to learn python, set about crafting a script to do the following:

• Transfer files based on a date (default to today).
• Transfer all files in the directory.

So I’m presenting to you my first stab at it. Some of the hard-codings depend on how the RAZR stores photos in the micro-SD card. If anyone wants to submit enhancements or critiques, I’m all ears. Right now it just works for what I needed it to do.

#!/usr/bin/env python

# Don Seiler, don@seiler.us 

import obexftp, ConfigParser, os
from xml.etree.ElementTree import XML
from optparse import OptionParser
from datetime import date

# This script is dependent on the Moto Razr convention of naming
# pictures in an MM-DD-YYYY_XXXX.jpg format

# Users need to create ~/.obexcopier.ini with these variables defined
# [ObexCopier]
# device = 1A:2B:3C:4D:5E:6F
# channel = 6
# source_dir = /MMC(Removable)/motorola/shared/picture
# dest_dir = /media/pictures

# Read config from ~/.obexcopier.ini
config = ConfigParser.ConfigParser()
config.read(os.path.expanduser('~/.obexcopier.ini'))

# Probably a waste of precious memory to store these again
device = config.get('ObexCopier','device')
channel = config.getint('ObexCopier','channel')
source_dir = config.get('ObexCopier','source_dir')
dest_dir = config.get('ObexCopier','dest_dir')

# Get today for default date
today = date.today().strftime("%m-%d-%y")

# Command-line handling to allow for date
parser = OptionParser()
parser.add_option("-d", "--date", dest="date", default=today, help="Grab pictures from this date, defaults to today [default: %default]",metavar="MM-DD-YY")
parser.add_option("-a", "--all", action="store_true", dest="all", default=False, help="Copy all files, regardless of date [default: %default]")
(options, args) = parser.parse_args()

# Connect to the client
print "Connecting to %s on channel %d" % (device, channel)
cli = obexftp.client(obexftp.BLUETOOTH)
cli.connect(device, channel)

# Get the list of files from the SD card picture dir
if options.all:
        print "Copying all files to disk"
else:
        print "Copying files from %s" % options.date

files_xml = cli.list(source_dir)
folder_listing = XML(files_xml)
files = folder_listing.findall('./file/')
for file in files:
        # Only handle pictures taken on the specified date
        if options.all or file.get('name').startswith(options.date):
                print "Copying %s" % file.get('name')
                data = cli.get(source_dir + '/' + file.get('name'))
                localfile = open(dest_dir + '/' + file.get('name'), 'wb')
                localfile.write(data)
                localfile.close()

# Disconnect and delete the client
cli.disconnect()
cli.delete

While trying to install pastebot on my CentOS5 sandbox for interal IRC usage, I found that I had to install a number of other perl modules upon which pastebot depended. This however became very frustrating as nearly all of them failed.

I ended up following the dependency chain to eventually find out that IO::Compress::Base was failing because the Scalar::Util module, installed stock by CentOS, lacked the “XS” extension. A Google search led me to this bug report confirming it. Fortunately the problem is easily solved by doing a “force install Scalar::Util” in cpan.

I assume that this problem also exists in RHEL5, and some LUGmates have reported similar breakage in Fedora.

Got my Fedora 8 live cd iso over the torrents yesterday. Just need to find a suitable host now …

UPDATE: Howard isn’t too thrilled.

Just thought I’d pass this tip along for anyone wanting to install the RPM2 perl module from CPAN. You’ll need these packages installed as well:

• rpm-devel
• elfutils-libelf-devel
• zlib-devel
• bzip2-devel
• libselinux-devel

Cheers. Thanks to Josh Bressers in #wilug for the help.

Edit: This was on RHEL4 for x86_64.

yum -y install libXp

Feeling in a rut lately, I decided to take the plunge and wiped WinXP of my laptop in favor of Fedora 7. Hopefully I can get World of Warcraft running under wine and things will be just peachy. I feel 300% better already being back on a linux desktop.

21:20:07 up 46 days, 21:24,  1 user,  load average: 153.21, 1019.09, 41.21

Yeah. 1019.

Seen on one of our ISP servers during a recent attempted brute-force password attack.

Jon Emmons over at Life After Coffee, a blog of linux tips and tricks, has posted some tips for the “find” command. Nothing new for myself, although his tip of redirecting STDERR to /dev/null to avoid having my terminal polluted with “Permission denied” errors is something that brightened my day, so I thought I’d share it with YOU. <3