zoe.vc

A while ago I wrote an article about a script I created to backup databases. Since then I had to make little changes and decided to put it to Google code as Open-Source-Project and released the new version 0.1.1 with some minor changes.

If you're interested in the script, head over to its project page and check the appropriate pages like the Changelog and the Installation and Requirements page. Feel free to download and comment  it.

In my local development environment at home I use The Bug Genie 2 for bugtracking. The tool is quiet cool, although the german translation is totally broken (I fixed it and send it to them, let's see what happens). It comes with a module called "svn_integration" for integrating SVN into the tracker to automatically have updated issues when the SVN comments contain special keywords. That's quiet cool, too, but unfortunatly does not work for me.

I'm using Windows Vista Business x64 as os and for SVN the wonderful, free and easy VisualSVN Server. Next ugly thing on The Bug Genie is that there is no documentation for the modules. I found an entry in the forums how to use the integration in windows, but that did not work out of the box. But the code is already within the module, what is good.

So open modules/svn_integration/post_commit.php from your buggenie installation directory and adjust this line with your path to the bugtracker installation dir:

define('BUGS2_INCLUDE_PATH', 'D:\\xampp\\htdocs\\bugs\\');

Then create a batch file in the same directory called post-commit.bat. You can see the source below, copy it and adjust the following variables. I used the code from the forum post and adjusted it a little. Remember that urls with whitespaces do not work, create a symlink or rename your directories:

• Path to svnlook.exe (you see my VisualSVN Server resides in D:\VisualSVN_Server, the default location C:\Program Files (x86)\VisualSVN Server does not work because of the whitespaces) VisualSVN Server has all the svn tools in its bin directory.

SET SVNLOOK=D:\VisualSVN_Server\bin\svnlook.exe

• The path to your php executable. I used XAMPP, so it is in D:\xampp\php\php.exe if installed in root on drive D.

SET PHP=D:\xampp\php\php.exe

• The path to your SVN directory. This is where svn stores its data.

SET SVN_PATH=D:\svn\

• The post-commit hook we create later takes two arguments from VisualSVN Server, the path and the revision. Unfortunatly VisualSVN Server uses the url as path which collides with the svnlook command we use to determine if a bug was mentioned in a revision comment, as svnlook wants the real file path.
  So we have to adjust the url to a path. As there is no real substring functionality for windows batch (or I did not search long enough) we simply count the length of our SVN url. In my case VisualSVN Server listens at https://amanda:8443/svn/, so a repository would be at https://amanda:8443/svn/my_repos. The server root (https://amanda:8443/svn/) has a length length of 24. Change the 24 to the length of your url. The batch then combines the SVN_PATH and your repository path to the local path, D:\svn\myrepos in my case here.

SET REPOS=%REPOS:~24%

• Once again, set the path to your installation directory of the bugtracker (replace D:\xampp\htdocs\bugs with your path):

%PHP% -f "D:\xampp\htdocs\bugs\modules\svn_integration\post-commit.php" "%AUTHOR%" "%REV%" "%COMMIT_MSG%" "%CHANGED%"

After your saved the file, open VisualSVN Server and the properties of your repository. Select the the Hooks tab and doubleclick the Post-commit hook. Enter the path to the batch file with the 2 arguments path and revision, in my case I entered "D:\xampp\htdocs\bugs\modules\svn_integration\post-commit.bat %1 %2", where %1 is the repository path and %2 the revision number.

Finally you have to install either WebSVN (which I prefer) or ViewVC and specify the path to your repository (in my case it is http://localhost/websvn/listing.php?repname=my_repos&) in The Bug Genie module settings for svn_integration.

One final and important hint: your user in the bugtracker has to have the same username as in SVN. By default, the first user in the tracker has the username "Administrator". My SVN user does not have the name "Administrator", so I logged out of the tracker, changed the property "uname" of the MySQL table "bugs2_users" for the admin to my SVN username, logged in and then everything worked fine.

As always, feel free to add comments and spread the word

And here the post-commit.bat code:

@echo off
SET REV=%2
SET REPOS=%1

REM path to svnlook (remember that paths with whitespace do not work e.g. C:\Program Files --> use mklink to create a path without)
SET SVNLOOK=D:\VisualSVN_Server\bin\svnlook.exe

REM path to php executable
SET PHP=D:\xampp\php\php.exe

REM path to svn (not url!)
SET SVN_PATH=D:\svn\

REM maps the svn url to the svn path - straightforward but the easiest way
REM replace with the length of your svn url e.g. 24 for https://amanda:8443/svn/
SET REPOS=%REPOS:~24%

SET REPOS=%SVN_PATH%%REPOS:/=\%

%SVNLOOK% log -r %REV% "%REPOS%" > COMMIT_MSG
REM SET THE COMMIT_MSG FROM THE FILE. The file is expected to contain only one line with this value
FOR /F "delims=" %%A IN (COMMIT_MSG) DO SET COMMIT_MSG=%%A
echo COMMIT_MSG=%COMMIT_MSG%

%SVNLOOK% changed -r %REV% "%REPOS%" > CHANGED
REM SET THE CHANGED FROM THE FILE. The file is expected to contain only one line with this value
FOR /F %%A IN (CHANGED) DO SET CHANGED=%%A
echo COMMIT_MSG=%CHANGED%

%SVNLOOK% author -r %REV% "%REPOS%" > AUTHOR
REM SET THE AUTHOR FROM THE FILE. The file is expected to contain only one line with this value
FOR /F %%A IN (AUTHOR) DO SET AUTHOR=%%A
echo COMMIT_MSG=%AUTHOR%

%PHP% -r "echo urlencode($argv[1]);" "%COMMIT_MSG%" > URL_COMMIT_MSG
REM SET THE URL_COMMIT_MSG FROM THE FILE. The file is expected to contain only one line with this value
FOR /F %%A IN (URL_COMMIT_MSG) DO SET URL_COMMIT_MSG=%%A
echo COMMIT_MSG=%URL_COMMIT_MSG%

%PHP% -f "D:\xampp\htdocs\bugs\modules\svn_integration\post-commit.php" "%AUTHOR%" "%REV%" "%COMMIT_MSG%" "%CHANGED%"

Vor kurzem habe ich ein schönes Skript zum Sichern einer MySQL-Datenbank und anschließendem Versenden des Backups per Email hier gefunden.

Das Skript ist an sich ganz gut, aber mir hat es strukturell nicht gefallen, da man die Datenbankzugangsdaten inline eintragen muss und es ausserdem nur für eine Datenbank funktioniert. Deswegen habe ich das ganze ein wenig umgeschrieben und stelle das hier gerne zum Download bereit.

Was kann es?

• Backup mehrerer Datenbanken
• Versenden der Backups an mehrere Empfänger

Es wird dabei pro Datenbank eine Email versendet und auf dem Server bleiben keine Dateien liegen. Das ganze ist auch noch einfach zu konfigurieren.

Wie benutze ich das?

• Aktuelle Version (0.1) herunterladen
• Daten in backup.php anpassen (ist alles kommentiert)
• alles in ein Verzeichnis deiner Wahl hochladen
• ggf. einen cronjob anlegen, der das Skript periodisch ausführen

Hier die backup.php, welche die entsprechenden Klassen aufruft und das Backup anstößt (ist auch im Download mit enthalten):

ini_set('error_reporting', E_ALL);
// include the files
require_once 'MySQLConfig.php';
require_once 'MySQLBackup.php';
 
// add some databases to backup
// the domain will be appended to the email subject and is also included within the sql file for identification.
$cfgHost0 = new MySQLConfig('username0', 'password0', 'database_name0', 'domain0');
$cfgHost1 = new MySQLConfig('username1', 'password1', 'database_name1', 'domain1');
 
$backup = new MySQLBackup();
// the path to the directory where this script is resided
$backup->setExecutionPath('/srv/domain/backup/');
// add the database configs to backup
$backup->addDatabaseToBackup($cfgHost0);
$backup->addDatabaseToBackup($cfgHost1);
// the sender of the backup mail
$backup->setSender('admin@yourdomain.com');
// add some people to receive the backup
$backup->addRecipient('john@yourdomain.com');
$backup->addRecipient('frank@yourdomain.com');
// execute the whole thing
$backup->backup();

Falls es Probleme und/oder Anregungen gibt, bitte diesen Post kommentieren, danke!

PS: zum Anlegen eine Cronjobs per SSH auf deinem Server einloggen, dann "crontab -e" ausführen, dann z.B. "0 2 * * 0,3 wget http://yourdomain.com/backup/backup.php -nc -q -O /dev/null" für eine Ausführung Sonntags und Mittwochs um 2 Uhr nachts einfügen. Mit ":wq" speichern und die Datei schliessen, dann sollte es schon funktionieren

Manchmal ist es nötig, die Datenbank von Typo3 auf UTF-8 umzustellen, da man arabische Zeichen oder was auch immer speichern will, und wobei die Kollation der Datenbank latin1_swedish_ci, mit der sich Typo3 standardmäßig installiert, nicht taugt.

Dazu habe ich diesen nützlichen Blogeintrag von Markus Giesen gefunden: Typo3 mySQL-Datenbank auf UTF-8 umstellen bzw. konvertieren

Jedoch ist bei mir im Export nie die Kollation festgeschrieben, diesen Schritt konnte ich de facto überspringen. Auch hatte ich keinen phpMyAdmin, also mussten die Kommandos für die SQL-Konsole zum Anpassen der Kollation her:

Anzeigen der verfügbaren Kollationen auf dem Server:

SHOW COLLATION
SHOW COLLATION LIKE 'utf%'

Ändern der Kollation der aktuellen Datenbank:

ALTER DATABASE DEFAULT COLLATE utf8_general_ci

Und danach weiter nach Markus Giesens Anleitung verfahren und alles wird gut