mysql – KutuKupret colorful website

Change Password Using courierpassd on dovecot mysql

When you have webmail separated from your pop3/imap4 server things can be complicated if you don’t have direct access to mysql server over the network. using poppassd will not solving the problem if you’re using mysql, pgsql or ldap backend for storing user information. because (AFAIK) it only can access/change user/password on passwd/shadow file.

using courierpassd allows us to access or modify pop3/imap4 user information store in mysql, pgsl or ldap backend. one caveat, courierpassd using courier-authlib API in order to be able accessing those database backend. we have to install or build from source. courierpassd use the same protocol as poppassd does.

i’m using centos and this is how i do it.

first using non priviledged user cccount, create rpm build environment.

$ mkdir -p ~/rpmbuild/{BUILD,RPMS,SOURCES,SPECS,SRPMS}
$ echo '%_topdir %(echo $HOME)/rpmbuild' >> ~/.rpmmacros

mkdir $HOME/rpm
mkdir $HOME/rpm/SOURCES
mkdir $HOME/rpm/SPECS
mkdir $HOME/rpm/BUILD
mkdir $HOME/rpm/SRPMS
mkdir $HOME/rpm/RPMS
mkdir $HOME/rpm/RPMS/i386

echo '%_topdir $HOME/rpm' >> $HOME/.rpmmacros

download courier-authlib and courierpassd source

wget http://prdownloads.sourceforge.net/courier/courier-authlib-0.60.2.tar.bz2
wget http://www.arda.homeunix.net/?ddownload=375 -O courierpassd.1.1.2.tar.gz

compile and install courier-authlib

$rpmbuild -ta courier-authlib-0.60.2.tar.bz2
$sudo rpm -ivh /home/youruser/rpm/RPMS/x86_64/courier-authlib-0.66.4-1.el6.x86_64.rpm
$sudo rpm -ivh /home/youruser/rpm/RPMS/x86_64/courier-authlib-devel-0.66.4-1.el6.x86_64.rpm
$sudo rpm -ivh /home/youruser/rpm/RPMS/x86_64/courier-authlib-mysql-0.66.4-1.el6.x86_64.rpm

extract courierpassd, compile and install

#tar xzf courierpassd.1.1.2.tar.gz
#cd courierpassd-1.1.2
#./configure
#make
#make install

you want be able to access this courierpassd from other server on the network. people using xinet.d usually.

#vi /etc/xinetd.d/courierpassd
service courierpassd
{
       disable          = no
       socket_type      = stream
       protocol         = tcp
       port             = 106
       wait             = no
       user             = root
       server           = /usr/sbin/courierpassd
       instances        = 10
       only_from        = 192.168.0.101 127.0.0.1
}

admin March 22, 2016courier, dovecot, mysqlNo Comments

memcache mysql php

Memcached And PHP, Caching Mysql Query Result

I’ve been messing around with memcached and php-pecl-memcache to cache sql query result. Many web sites & applications such as Facebook, LiveJournal, Flickr, Slashdot, WikiPedia/MediaWiki, SourceForge, Digg and Twitter use memcached to enhance their performance.

Memcached (Memory Cache Daemon) was developed by the team at LiveJournal to improve performance of their social blogging site by minimizing the impact of the bottleneck caused by reading data directly from the database. Memcached is a server that caches Name Value Pairs in memory. The “Name”, or key, is limited to 250 characters, and the “Value” is limited to 1MB in size. Values can consist of data, HTML Fragments, or binary objects; almost any type of data that can be serialized and fits in memcached can be stored.

here is simple example/demonstration how to cache regular sql query

First of all, we need memcached daemon run on system

$ ps ax | grep memcached
 8955 ?        Ssl    0:00 memcached -d -p 11211 -u memcached -m 256 -c 1024 -P /var/run/memcached/memcached.pid -l 127.0.0.1

Setup simple mysql database/tables as shown bellow:

mysql-shell> CREATE DATABASE memcache;

Copy/Paste this tables schema to your mysql shell/console

CREATE TABLE memc
(
 personID int NOT NULL AUTO_INCREMENT,
 PRIMARY KEY(personID),
 FirstName varchar(15),
 LastName varchar(15),
 Age int
);
hit enter/return key

Insert some data

mysql-shell> INSERT INTO memc (FirstName, LastName, Age) VALUES('Memory', 'Cache', '100');

admin May 4, 2011memcache, mysql, php7

fedora mysql pam

Postfix Smtp Auth using pam_mysql On Fedora 12

First of all, of course we need to compile postfix for supporting sasl.the easiest way is read tutorial how to compile postfix rpm source at Simon J Mudd’s website

Since i’m using mysql database for storing username/password, i’m gonna show you how to create smtp authentication/SASL.

Install pam_mysql:

# yum -y install pam_mysql

Edit /etc/pam.d/smtp file :

auth required pam_mysql.so user=postfix passwd=password host=localhost db=postfixdb table=mailbox usercolumn=username passwdcolumn=password crypt=1 md5=1 sqlLog=0
account sufficient pam_mysql.so user=postfix passwd=password host=localhost db=postfixdb table=mailbox usercolumn=username passwdcolumn=password crypt=1 md5=1 sqlLog=0

admin December 10, 2009fedora, mysql, pam, postfixNo Comments

mysql programing

How To Use LIKE In MYSQL Stored Procedure Select Statement?

I write a stored procedure as below but always result nothing/incorect. [text] DELIMITER // DROP PROCEDURE IF EXISTS get_domain // CREATE PROCEDURE get_domain (IN domin VARCHAR(64)) BEGIN SELECT * FROM domain_senders WHERE domain LIKE '%domin%'; END// DELIMITER ; [/text] The procedure itself is successfully created.After looking around with google i've…

admin December 9, 2009mysql, programing3

mysql tunnel vpn

Securing MySQL traffic with stunnel

To encrypt a connection between a mysql client and a mysql server, run two instances of stunnel, one on client site and other on MySQL remote site

Here’s steps how to do it

# wget http://www.stunnel.org/download/stunnel/src/stunnel-4.27.tar.gz
# rpmbuild -ta stunnel-4.27.tar.gz
# rpm -ivh /usr/src/redhat/RPMS/stunnel-4.27-1.i386.rpm

Create stunnel.pem cert on server site

# openssl genrsa -out privkey.pem 2048
# openssl req -new -x509 -key privkey.pem -out cacert.pem -days 1095
# cat privkey.pem cacert.pem >> /etc/stunnel/stunnel.pem

Set the proper permissions on the resulting private key:

# chmod 0400 /etc/stunnel/stunnel.pem

Set the proper ownership of the stunnel chroot dir

# chown nobody:nobody /var/run/stunnel

admin September 20, 2009mysql, tunnel, vpnNo Comments

centos mysql policyd

Postfix + Centos + Policyd V2 + MySQL

Prerequisites

Requirements for Policyd v2

MySQL
Net::Server >= 0.96
Net::CIDR
Config::IniFiles (Debian based: libconfig-inifiles-perl, RPM based: perl-Config-IniFiles)
Cache::FastMmap (Debian based: libcache-fastmmap-perl, RPM based: perl-Cache-FastMmap)
Mail::SPF (Required for SPF)

Requirements for webui

PHP v5+

Download policyd v2

# wget http://downloads.sourceforge.net/project/policyd/2.0.x%20%28Stable%29/v2.0.7/cluebringer-2.0.7.tar.bz2?use_mirror=biznetnetworks

Build rpm from source tarball

# rpmbuild -ta cluebringer-2.0.7.tar.bz2

admin September 13, 2009centos, mysql, policyd, postfix18

mysql password

i forgot mysql root password!

With lots of server to maintained, sometimes this occasionally happened. how to reset root password you forgotten root password?? seems like chicken egg problem to me :) Don't worry here's the solutions First of all you will need to ensure that your database is stopped: [bash] # service mysqld stop…

admin September 2, 2009mysql, passwordNo Comments