sogo auth with mysql and mysql(view) against ispconfig database


fist step let mysql listen on:
therefore verify your /etc/mysql/my.cnf

cat /etc/mysql/my.cnf | grep bind
#bind-address		=

(should be done @ispconfig install)

create a mysql database and a user and grant all privileges to that user

GRANT ALL PRIVILEGES ON  `sogo` . * TO  'sogo'@';

use mysql database as root and create special view

use sogo;
CREATE VIEW sogo_auth_view AS
SELECT  email AS c_uid,
             email AS c_name,
             password AS c_password,
             name AS c_cn,
             email AS mail
FROM dbispconfig.mail_user
WHERE disableimap='n' AND postfix ='y';

in your sogo config set the following lines:


Have Fun!

simple sogo backup in perl with mysql


Need to export(backup) your sogo users contacts calendar etc?

Here some simple approach to get this done.

use warnings;
use DBI;

my $db ="databasename";
my $user = "username";
my $pass = "mypassword";
my $host = "localhost";
my $query = "SELECT mail FROM sogo_users";
my $bkppath = "/path/to/backup";
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);

my $dbh = DBI->connect("DBI:mysql:$db", "$user", "$pass")
	or die "Could not connect to database: " . DBI->errstr;

my $sth = $dbh->prepare("$query")
	or die "Couldn't prepare statement: " . $dbh->errstr;

	or die "Couldn't execute statement: " . $sth->errstr;

while ($data = $sth->fetchrow_array()) {
	my $mail = $data;
	my $bkp = `/usr/sbin/sogo-tool backup $bkppath $mail > /dev/null`;
	sleep 2;
	my $mv = `/bin/mv $bkppath/$mail $bkppath/$mail.$wday`;


Have fun!