Backing up a MySQL server

Just wanted to paste a small script here, which dumps and gzips all databases hosted on a MySQL instance.
Make sure, that this script is not readable for everybody, as it contains credentials.

dumpdbs.sh



#!/bin/bash
#
# MySQL database dump
#
# - Takes MySQL Dumps of all available databases
# - Only keeps one backup in the dumpfolder
# -> uncomment the LOG variable and the pipes to the tee command for logging to a file
#
# 2011, Looke
#

# Setup
DBUSER=root
DBPASS=xxx
DBDUMPDIR=/dbdumps
DBDUMPDATE=$(date '+%d-%m-%Y')
# LOG=/var/log/dumpdbs

# Create/Empty DBDUMPDIR
if [ ! -d $DBDUMPDIR ]; then
	echo $(date +"%d/%m/%Y %T") INFO: $DBDUMPDIR not found, will create it... #| tee -a $LOG
	mkdir $DBDUMPDIR
else
	echo $(date +"%d/%m/%Y %T") INFO: Emptying $DBDUMPDIR... #| tee -a $LOG
	rm -f $DBDUMPDIR/*
fi

# Loop through all databases available and dump them (gzipped)
for DBNAME in $(echo "show databases;" | mysql --user=$DBUSER --password=$DBPASS -s)
do
	echo $(date +"%d/%m/%Y %T") INFO: Dumping $DBNAME as ${DBNAME}_${DBDUMPDATE}.sql.gz... # | tee -a $LOG
	mysqldump --user=$DBUSER --password=$DBPASS $DBNAME | gzip -c > $DBDUMPDIR/${DBNAME}_${DBDUMPDATE}.sql.gz
done

This script does a good job together with Bacula. Here the Job resource in Bacula


Job {
  Name = "Backup MySQL DBs"
  ...
  Client Run Before Job = "/opt/bacula/scripts/dumpdbs.sh"  
  ...
}

Logon scripts with KiXtart

To follow up one of my previous posts (Mapping of network drives via batchfile), here is how you also could solve this kind of task using KiXtart. KiXtart is a free-format scripting language which allows to automate extensive configuration tasks, for example, but not limited to windows logon events.

Installation of KiXtart is quite simple, just get the binary, place it somewhere useful (i.e. the NETLOGON share in Windows domain environments) and edit the user accounts to use it as logon script (or write a batch logon script which calls the Kix binary).

Below is how i solved the problem of having a large list of available shares automatically checked if the user has permission to use them and if so to connect them to his computer.

I presume, you have Windows Security Groups in place, which use the same name as the shared folders.



; =============
;
; Dynamic share mapping script
;
; Author: Looke, 2010
; Filename: kixtart.kix
;
; Outline:
; * Iterates through the ServerDrives array, determines wether the
;   user is in the appropriate security group or not and maps
;   the network drive to a driveletter specified in the DriveLetters
;   array.
;
; =============

; -------------
; Admin configurable
; -------------

; Array of available groupshares
$ServerDrives = "\\SRV01\Share1",
		"\\SRV02\Share1"

; Array of available drive letters
$DriveLetters = "V:", "W:", "X:", "Y:", "Z:"

; -------------
; Better leave untouched
; -------------

; Iterator for the DriveLetters array
$DriveLetterIndex = 0

; -------------
; Removing current mappings
; -------------

; Removing mapped groupshares
FOR EACH $DriveLetter in $DriveLetters
	USE $DriveLetter /DELETE
NEXT

; -------------
; Mapping of groupshares
; -------------

; Dynamic mapping of groupshares
FOR EACH $ServerDrive in $ServerDrives

	; Getting the name of the shared folder
	; (which also is the name of the Windows Security Group)
	$Group = SPLIT($ServerDrive, "\")

	IF INGROUP($Group[3])
		USE $DriveLetters[0+$DriveLetterIndex] $ServerDrive
		IF @ERROR
			? "Failed with errorcode " + @ERROR
			  + " while mapping "
			  + $ServerDrive + " to "
			  + $DriveLetters[0+$DriveLetterIndex]
		ELSE
			? "Successfully mapped "
			  + $ServerDrive + " to "
			  + $DriveLetters[0+$DriveLetterIndex]
			$DriveLetterIndex = $DriveLetterIndex+1
		ENDIF
	ENDIF
NEXT

In my script, I didn’t make the mappings persistent. So, if you intend to also have users with laptops and a after-login VPN solution to connect to your servers, you might need to add the /PERSISTENT switch to the USE command(s).

Something else, which might be useful, is logging of logon events, as fiddling around with the windows security logs can be a bit of a pain. Missing in the script above, but can easily be integrated:



; Path to the logon logfiles (make sure, users can write to this path)
$LogPath = "\\SRV01\LOG$"

; Filenames and Paths of logon logfiles
$LogFile = "$LogPath\@WKSTA.log"

; Content of logon logfile
$LogText = "Date: @MDAYNO.@MONTHNO.@YEAR, @TIME" + CHR(13) + CHR(10) + 
	   "User: @USERID" + CHR(13) + CHR(10) + 
	   "Workstation: @WKSTA" + CHR(13) + CHR(10) + 
	   "IPs: @IPADDRESS0, @IPADDRESS1" + CHR(13) + CHR(10) + 
	   "MAC address: @ADDRESS" + CHR(13) + CHR(10) +
	   "-----------------------------------" + CHR(13) + CHR(10)

; Open, write and close Logfile
$LogError = OPEN(5, $LogFile, 5)
IF $LogError = 0
	$RES = WRITELINE(5, $LogText)
	$RES = CLOSE(5)
ENDIF 

A manual to using KiX can be found here:
http://www.kixtart.org/manual/

Adding KML Tracklogs to a Google Map

I finally got my hands on a GPS Track logger device (Holux M-241, a very nice gadget by the way) and thought to myself, it would be nice, if I could take those KML track log files and just upload them somewhere to make them available to be viewed on a web page. This is what came out as a first lazy sample:

http://looke.ch/kmloverlay/
You can find the source here: http://looke.ch/kmloverlay/source.php

It basically looks for KML files in a folder, which you define and puts them in a select box. Here you can select the file you want to have drawn on the Map. Very basic functionality, but its a start at least. Probably I will continue to add new stuff to it (like a description of the track logs or comments etc.)

The main part of this sample is Googles GGeoXml class, which allows to pass a publicly available KML to the Google Maps API:


map = new GMap2(MapElement);
geoXml = new GGeoXml(KMLurl);
map.addOverlay(geoXml);

 

UPDATE:
In order to preserve the usability on mobile devices (e.g. Android phones), I added some JS to load a different style for devices with screens that are less than 320px wide:


if (screen.width <= 320) {
document.write('<style type="text/css">div#map{width: 300px; height: 310px;}</style>');
}
else {
document.write('<style type="text/css">div#map{width: 640px; height: 480px;}</style>');
}

Setting file permissions using XCACLS

If you work with large quantities of files and have probably come across a situation where you had to modify file permissions, you know that the Explorer GUI is not much of a help. To relieve yourself from clicking your fingers to death, you could use XCACLS, which allows you to script file permission settings. XCACLS is also capable of creating listings of applying permissions.

As a first example on how to use XCACLS, I show you how to get a listing of all permissions applying to the folder c:\temp and its subs.

Grab yourself a copy of the XCACLS package from the MS site and go to Start > Run > cmd and cd to the path where you put xcacls.vbs and run the command below:

cscript xcacls.vbs "c:\temp\*"

The output you get, will look similar to this:


Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.

Starting XCACLS.VBS (Version: 5.2) Script at x/x/2009 x:xx:xx PM

Startup directory:
"C:\"

Arguments Used:
	Filename = "c:\temp\*"
**************************************************************************
File: C:\temp\access.log

Permissions:
Type     Username                Permissions           Inheritance 

Allowed  BUILTIN\Administrators  Full Control          This Folder Only
Allowed  NT AUTHORITY\SYSTEM     Full Control          This Folder Only
Allowed  DOMAIN\user            Full Control          This Folder Only
Allowed  BUILTIN\Users           Read and Execute      This Folder Only      

No Auditing set

Owner: DOMAIN\user
**************************************************************************

Operation Complete
Elapsed Time: 0.1875 seconds.

Ending Script at x/x/2009 x:xx:xx PM

So far for the displaying of permissions.

As an example for turning on file permission inheritance in a directory tree, simply run:

cscript xcacls.vbs "c:\temp2\*" /I ENABLE /F /T /S

To conclude this post, this is how you specify the owner over a whole dir tree and all subcontents (please take care to invoke the /E parameter to tell XCACLS to only edit the ACL record, otherwise the ACL gets blanked out):

cscript xcacls.vbs "c:\temp2\*" /O username /F /T /S /E

How to use Xcacls.vbs to modify NTFS permissions
http://support.microsoft.com/?scid=kb%3Ben-us%3B825751&x=6&y=13

Recursively delete outdated files with VBScript

Recently, I came across the situation, where I had to delete outdated SQL backup files from our MS SQL servers Backup directory. To tidy up the backup folder, I wrote a small VB script which handles this for me:


' DeleteOutdated
'
' Parameters
' MaxFileAge: Maximum file age in days (modification date)
' Path: Folder which contains the files
'
' VBScript ref. http://msdn.microsoft.com/en-us/library/t0aew7h6(VS.85).aspx
' FSO ref. http://msdn.microsoft.com/en-us/library/z9ty6h50(VS.85).aspx

Dim objFso
Set objFso = CreateObject("Scripting.FileSystemObject")

Sub DeleteOutdated(Path, MaxFileAge)
	Set objDir = objFso.GetFolder(Path)
	For Each objFile in objDir.Files
		If objFile.DateLastModified < (Date() - MaxFileAge) Then
			AskDelete = MsgBox("Delete "&objFile.Path&"?",3,"Delete file")
			If AskDelete = 6 Then
				objFile.Delete
			End If
			If AskDelete = 2 Then
				WScript.Quit
			End If
		End If
	Next

	For Each objSubfolder in objDir.Subfolders
		Call DeleteOutdated(objSubfolder, MaxFileAge)
	Next
End Sub

Call DeleteOutdated("c:\outdatedstuff", 1)

Hint
In this state, the script asks to delete everytime, if it finds a file wich is older than specified. Of course, you might want to remove that in productive usage, when script execution is scheduled.

Extended
Here I attached a version, which writes a logfile and processes empty subfolders (updated on 26.08.2009):
deleteoutdated-wlog

Arduino – building open-source electronics prototypes

Arduino is an open-source microprocessor platform to build electronics prototypes. Not only the development tools are open-source, but also the hardware itself. You can download all the necessary plans for it and build your own one, if you want to.

The really cool thing about the Arduino is, that you can program it with the open-source programming language Wiring, which is very straightforward to use.

Of course, being a complete newbie in electronics engineering, I ordered my own Arduino board as a prebuilt base-board – the Arduino Duemilanove
…and finally, it arrived :)

As a first act of familiarization, I started with the very basic “push a button to power the LED” program


int ledPin = 13; // choose the pin for the LED
int inPin = 2;    // choose the input pin (for a pushbutton)
int val = 0;      // variable for reading the pin status

void setup() {
  pinMode(ledPin, OUTPUT);  // declare LED as output
  pinMode(inPin, INPUT);      // declare pushbutton as input
}

void loop(){
  val = digitalRead(inPin);   // read input value
  if (val == HIGH) {           // check if the input is HIGH (button released)
    digitalWrite(ledPin, LOW);  // turn LED OFF
  } else {
    digitalWrite(ledPin, HIGH);  // turn LED ON
  }
}

Mapping of network drives via batchfile

If you work in a ActiveDirectory based environment and all your clients are connected to the domain, you might not have to deal with manually map home drives and other resources.

However, if some of your clients are not connected to the domain, but need access to domain resources, you probably noticed that it can be pretty annoying having service calls because of forgotten network paths and locked accounts.

To encounter that problem, I wrote a quick and dirty script, which gives the user a basic dialogue driven netdrive-mapper, so your service desk gets rid of the most basic calls.


@ECHO OFF

ECHO Removing all available mappings...
PAUSE
NET USE * /D

:USER
ECHO Attempting to connect to SERVER and map your homedrive...
PAUSE
SET /P USER=Please enter your username (username@domain.local):
NET USE U: \\server.domain.local\%USER:~0,-13%$ /USER:%USER% /PERSISTENT:NO

ECHO Attempting to connect to SERVER and map your Groupdrives...
PAUSE

:GROUP1
SET /P GROUP1=Please enter your main Group:
NET USE V: \\server.domain.local\%GROUP1% /PERSISTENT:NO
SET /P CONTINUE=Add another Groupdrive? (y or n)

IF /I '%CONTINUE%'=='y' GOTO GROUP2
IF /I '%CONTINUE%'=='n' GOTO PUB
ECHO "%CONTINUE%" is not valid. Going to map public drives GOTO PUB

:GROUP2
SET /P GROUP2=Please enter your second Group:
NET USE W: \\server.domain.local\%GROUP2% /PERSISTENT:NO
GOTO PUB

:PUB
ECHO Attempting to connect to SERVER and map public drives...
NET USE p: \\server.domain.local\public /USER:%USER% /PERSISTENT:NO
GOTO PRINTERS

:PRINTERS
ECHO Attempting to connect to SERVER and map printers...
PAUSE
START \\server.domain.local\printer1
START \\server.domain.local\printer2

Recursively delete files by extension

Recently, I got the task to remove all MP3- files from some shared folders (SMB shares). To do so, I wrote a tiny batch- script wich processes the DIR command’s output and examines it to match the MP3 extension.

Usage:
The script processes all folders and subfolders in its working directory (e.g. if you run it in c:\scripts it processes all folders and subfolders from there on – c:\scripts\*)


@ECHO OFF
FOR /F "tokens=*" %%G IN ('DIR /B /S *.mp3') DO DEL /F "%%G"