Archive for the ‘Mysql’ category

Defragmentation of multiple databases in Mysql

May 13th, 2012

What does table fragmentation mean?
This means that there is free data taking up space within each of these tables. This can eventually lead to storage and performance issues.

As our database keep growing,  we recognized that the database was getting slower and slower, so we decided to defrag all the tables in all databases. It was the challenge to defrag 20+ databases with 400+ tables

We have achieved by creating screen on Mysql server (screen -RD defrag) and ran the below script and redirect the output of script to file so we can watch what exactly script is doing

for db in data1 data2 data3 data4 data5 data6 data7 data8 data9 data10 data11 data12 data13;do
{
for tc in `mysql -h localhost -e “use $db  ;show tables” | grep -vE ‘Tables*’`; do
{
echo “mysql -h localhost -e \”use $db  ;OPTIMIZE TABLE ${tc};\”";
mysql -h localhost -e “use $db  ;OPTIMIZE TABLE ${tc};”;
echo “”;
};done;

}; done >defrag_stats.txt

You can check the out put of script in defrag_stats.txt file

[root@manoj-db ~]# tail -f defrag_stats.txt
mysql -h localhost -e “use data1  ;OPTIMIZE TABLE test_table1;”
Table   Op      Msg_type        Msg_text
data1.test_table1        optimize        note    Table does not support optimize, doing recreate + analyze instead
data1.test_table1        optimize        status  OK

After doing fragmentation of all databases, we are able to clear lot’s of space and  see some difference in mysql performance as well.

Thanks
Manoj

How to scalable your application using nosql

May 10th, 2012

How to scalable your application

Most of the scalable application on web use MYSQL+memcached+varnish cache as the back end for their applications. Recently some of them shifted to NOSQL for one of the biggest reason ‘performance’. Certainly, NOSQL performs better than MYSQL for simple queries and primary key look-ups.

Most of the database queries of web applications are simple and related to primary key lookups. So, it seems to be healthy decision of migrating to NOSQL from MYSQL.

Yoshinori Matsunobu recently launched a MySQL plug-in ‘HandlerSocket’ which implements protocol for MySQL. The plug-in allows applications to communicate directly with MySQL storage engines, without the overhead associated with using SQL. Operations such as parsing and optimizing queries, as well as table handling operations (opening, locking, unlocking, closing) are also included in this plug-in.

As a result, HandlerSocket can provide much better performance for applications that using simple queries and primary key look-ups.

How it Works?

Whenever a query is fired on a table in MySQL, MySQL servers upper layer parses the query. Then it opens a table for which query is fired. After performing operations on data table is closed and results are returned, for every single query disk operation involved and MySQL Engine opens a new instance of table and closes it after performing operations.

Opening and closing of tables is very costly as it causes mutex contensions and hampers performance. The biggest difference here in Handlersocket is, it doesn’t open/close tables everytime. It reuses the open table connections.

Advantages

1. Supporting lots of query patterns
2. It can handle lots of concurrent connections
3. Extremely high performance
4. Smaller network packets
5. Running limited number of MySQL internal threads
6. Grouping client requests
7. It can reduce the number of fsync() calls and replication delay
8. No duplicate cache and data inconsistency
9. No need to modify/rebuild MySQL
10.Independent from storage engines

How to install HandlerSocket

1. Install latest version of percona Mysql
2. Enabling the Plugin, First, add the following lines to the [mysqld] section of your my.cnf file:

loose_handlersocket_port = 9998
# the port number to bind to for read requests
loose_handlersocket_port_wr = 9999
# the port number to bind to for write requests
loose_handlersocket_threads = 16
# the number of worker threads for read requests
loose_handlersocket_threads_wr = 1
# the number of worker threads for write requests
open_files_limit = 65535
# to allow handlersocket to accept many concurrent
# connections, make open_files_limit as large as
# possible.

3. Login to mysql as root, and execute the following query:
mysql> install plugin handlersocket soname ‘handlersocket.so’;

4. Testing the Plugin installation

If handlersocket.so was successfully installed, it will begin accepting connections on ports 9998 and 9999. Executing a SHOW PROCESSLIST command should show HandlerSocket worker threads:

mysql> show processlist;
+—-+————-+—————–+—————+———+——+——————————————-+——————+———–+—————+———–+
| Id | User        | Host            | db            | Command | Time | State                                     | Info             | Rows_sent | Rows_examined | Rows_read |
+—-+————-+—————–+—————+———+——+——————————————-+——————+———–+—————+———–+
|  1 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         1 |
|  2 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         1 |
|  3 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         1 |
|  4 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         1 |
|  5 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         1 |
|  6 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         1 |
|  7 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         1 |
|  8 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         1 |
|  9 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         1 |
| 10 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         1 |
| 11 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         1 |
| 12 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         1 |
| 13 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         1 |
| 14 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         1 |
| 15 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         1 |
| 16 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         1 |
| 17 | system user | connecting host | handlersocket | Connect | NULL | handlersocket: mode=wr, 0 conns, 0 active | NULL             |         0 |             0 |         1 |
| 19 | root        | localhost       | NULL          | Query   |    0 | NULL                                      | show processlist |         0 |             0 |         1 |
+—-+————-+—————–+—————+———+——+——————————————-+——————+———–+————-

for  more details http://www.percona.com/doc/percona-server/5.5/performance/handlersocket.html

Thanks
Manoj

Rotating mysql slow query log

May 2nd, 2012

The best practice is to rotate this file daily. In addition you should both analyze the log file producing a top 5 or top 10 list of slow SQL queries each day. You can rotate logs using below script.

#/bin/bash
SLOW_LOGS_PATH=/var/logs/logs/log-slow-queries.log
echo “Rotating slow logs, please wait….”
cp ${SLOW_LOGS_PATH} ${SLOW_LOGS_PATH}.`date +%d-%m-%Y-%H:%M:%S`; > ${SLOW_LOGS_PATH}

Thanks
Manoj

Slave: received end packet from server, apparent master shutdown

April 21st, 2012

I was seeing this message continuously in mysql logs. It is a master-master with one slave setup in which one node had inconsistent tables. I tried to patch this by running a dump on the known-good server and importing that to the broken server + starting slave thread.

Below was the result:

[root@db-server tmp]# tail -f /var/log/mysqld.log
120420 23:55:55 [Note] Slave: received end packet from server, apparent master shutdown:
120420 23:55:55 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log ‘mysql-bin.000026′ at postion 104323331
120420 23:55:55 [Note] Slave: received end packet from server, apparent master shutdown:
120420 23:55:55 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log ‘mysql-bin.000026′ at postion 104323331
120420 23:55:55 [Note] Slave: received end packet from server, apparent master shutdown:
120420 23:55:55 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log ‘mysql-bin.000026′ at postion 104323331
120420 23:55:55 [Note] Slave: received end packet from server, apparent master shutdown:
120420 23:55:55 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log ‘mysql-bin.000026′ at postion 104323331
120420 23:55:55 [Note] Slave: received end packet from server, apparent master shutdown:
120420 23:55:55 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log ‘mysql-bin.000026′ at postion 104323331

I’ve been searching for answers to this for a while and it seems like of cases are the result of a duplicate server_id  I have verified that both
servers are different in config and in memory and found on the slave i used same server id as on master2. I changed server ID on slave, issue has been fixed after restarting Mysql server.

Master2 Server:
mysql> show variables where variable_name =’server_id’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| server_id     | 2     |
+—————+——-+
1 row in set (0.00 sec)

Slave Server 3:
mysql> show variables where variable_name =’server_id’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| server_id     | 2     |
+—————+——-+
1 row in set (0.00 sec)

Thanks
Manoj

How to take backup of selective tables in Mysql?

April 14th, 2012

We can take the backup of selective tables using mysqldump command in mysql. It will help when we have big database like 400G and we want to do quick backup of some tables

for i in `mysql -h 192.168.1.1 -u manoj -pmanoj testdb -e ‘show tables’ `
do
echo $i; mysqldump -t -u manoj -pmanoj testdb ${i} –where=category_id=1 >> /mysql_backup/testdb_db_14042011.txt
done

Thanks
Manoj

How to change mysql default character set to UTF8 in my.cnf?

April 3rd, 2012

You can change the default character set to UTF8 in mysql 5.5 using below steps

1. Check current character set

mysql> show variables like “collation_database”;
+——————–+——————-+
| Variable_name      | Value             |
+——————–+——————-+
| collation_database | latin1_swedish_ci |
+——————–+——————-+
1 row in set (0.00 sec)

mysql> show variables like “%character%”;show variables like “%collation%”;

+————————–+—————————-+
| Variable_name            | Value                      |
+————————–+—————————-+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+————————–+—————————-+

2. added below variables in my.cnf

[mysqld]
init_connect=’SET collation_connection = utf8_unicode_ci’
init_connect=’SET NAMES utf8′
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

3. Restarted Mysql

4. Check the default character set using below commands

mysql> show variables like “%character%”;show variables like “%collation%”;

+————————–+—————————-+
| Variable_name            | Value                      |
+————————–+—————————-+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.00 sec)

+———————-+—————–+
| Variable_name        | Value           |
+———————-+—————–+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+———————-+—————–+
3 rows in set (0.00 sec)

5. Create database and check the default character set

mysql> SHOW CREATE DATABASE manoj;
+———-+—————————————————————————————-+
| Database | Create Database                                                                        |
+———-+—————————————————————————————-+
| manoj    | CREATE DATABASE `manoj` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ |
+———-+—————————————————————————————-+
1 row in set (0.00 sec)

Thanks
Manoj

How to insert data in MySQL Table

September 28th, 2011

Let’s start inserting data in the table created in our post, table name was employee, we also discussed how to check the table structure in one of our post

Step 1) Login to MySQL

we will be using user : demo and password as demouser

mysql -u demo -p demouser

Step 2) use database in which we have created table

mysql> use demo;

Step 3) check the structure of the database

mysql> desc employee;
+————–+————–+——+—–+———+——-+
| Field        | Type         | Null | Key | Default | Extra |
+————–+————–+——+—–+———+——-+
| id           | int(11)      | YES  |     | NULL    |       |
| employeename | varchar(252) | YES  |     | NULL    |       |
| address      | varchar(256) | YES  |     | NULL    |       |
+————–+————–+——+—–+———+——-+
3 rows in set (0.07 sec)

so this tells us that the table has 3 columns id , employeename  and address , lets start by writing the insert statement  syntax used will be

Insert into employee(id ,employeename , address) values ( 1, ‘DemoEmployee’, ‘US’) ;

mysql> Insert into employee(id ,employeename , address) values ( 1, ‘DemoEmployee’, ‘US’) ;

Query OK, 1 row affected (0.02 sec)

Lets check if the record is been inserted properly.

mysql> select * from employee;

+——+————–+———+

| id   | employeename | address |

+——+————–+———+

|    1 | DemoEmployee | US      |

+——+————–+———+

1 row in set (0.00 sec)

Excellent we have successfully inserted out first record.

How to get list of tables in a MySQL database

September 28th, 2011

lets discuss now  how we can check  how many tables exist in a MySQL database .

Step 1) Login to MySQL

we will be using user : demo and password as demouser

mysql -u demo -p demouser

Step 2) use database in which we need to check table , as in our previous post we created database demo we will be using same database

mysql> use demo;

Step 3) we will use command show tables ;

+—————-+
| Tables_in_demo |
+—————-+
| employee       |
+—————-+
1 row in set (0.00 sec)

this will list all the tables which are under demo database.

In our next post we will discuss how to insert data in a MySQL Table.

How to Check table structure in MySQL

September 28th, 2011

in our previous  post we discussed how to  add more columns in MySQL table , lets see how we can check table structure in MySQL.

Step 1) Login to MySQL

we will be using user : demo and password as demouser

mysql -u demo -p demouser

Step 2) use database in which we need to check table , as in our previous post we created database demo we will be using same database

mysql> use demo;

Step 3) we have 2 methods of checking the table structure.

a) Using Desc , we will now check the table structure of table employee

mysql> desc employee;
+————–+————–+——+—–+———+——-+
| Field        | Type         | Null | Key | Default | Extra |
+————–+————–+——+—–+———+——-+
| id           | int(11)      | YES  |     | NULL    |       |
| employeename | varchar(252) | YES  |     | NULL    |       |
| address      | varchar(256) | YES  |     | NULL    |       |
+————–+————–+——+—–+———+——-+
3 rows in set (0.07 sec)

This command lists all the columns and data type used for each column.

b) Using show create table command

syntax : show create table tablename

mysql> show create table employee;

+———-+———————————————————————————————————————————————————————————+

| Table    | Create Table                                                                                                                                                                    |

+———-+———————————————————————————————————————————————————————————+

| employee | CREATE TABLE `employee` (

`id` int(11) default NULL,

`employeename` varchar(252) default NULL,

`address` varchar(256) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

+———-+———————————————————————————————————————————————————————————+

1 row in set (0.00 sec)

This command gives us create table structure for table employee.

In next post we will discuss how to check how many tables exists in a database.

How to Create a Database in MySQL

September 27th, 2011

Once MySQL Server setup is done , lets start by creating a database , in our example we will see how to create a MySQL Database

Step 1) Login to MySQL

we will be using user : demo and password as demouser

mysql -u demo -p demouser

Step 2) Now we will be creating a database demo
mysql> create database demo;
Query OK, 1 row affected (0.00 sec)

Step 3) Checking if the database is created ;

mysql>show databases;

+——————–+
| Database           |
+——————–+
| information_schema |
| demo                                |
| test                                    |
+——————–+
3 rows in set (0.00 sec)

we can see that the database demo is there in the list . in next post we will see how to create tables in a MySQL.

How to create table in MySQL

September 27th, 2011

In Our previous post we discussed how to create a database in MySQL , lets see how to create tables in MySQL database.

Step 1) Login to MySQL

we will be using user : demo and password as demouser

mysql -u demo -p demouser

Step 2) use database in which we need to create table , as in our previous post we created database demo we will be using same database

mysql> use demo;

Step 3)  Create table  lets assume our table will be called employee and it will have 2 columns  id and employeename we are starting with very basic table structure and then will be adding more columns to it.

mysql> create table employee (  id int , employeename varchar(252) );
Query OK, 0 rows affected (0.01 sec)

step 4) Lets check that if table is created.

mysql> show tables;
+—————-+
| Tables_in_demo |
+—————-+
| employee       |
+—————-+
1 row in set (0.00 sec)

so gr8 we are able to create our first table. in our next post we will discuss how to check the table structure and alter the table structure.

How to add more columns in exsiting MySQL table

September 27th, 2011

in our previous  post we discussed how to create table in MySQL Database , lets check now how we can add more columns to existing table .

Step 1) Login to MySQL

we will be using user : demo and password as demouser

mysql -u demo -p demouser

Step 2) use database in which we need to create table , as in our previous post we created database demo we will be using same database

mysql> use demo;

Step 3)  we created table employee , now we will be looking how to add more columns in existing table .lets add one more column address having data type varchar and length as 256

mysql> alter table employee add column address varchar(256) ;

Query OK, 0 rows affected (0.07 sec)

Records: 0  Duplicates: 0  Warnings: 0

here we go we can see that the table is being altered . in our next post we will be discussing how to check the  existing table structure.

Creating a UTF-8 database

September 20th, 2011

1. You can create a UTF-8 database with binary UTF-8 collation.
CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_bin;

2. You will also need to set the Server Characterset to utf8. This can be done by adding the following in my.ini for Windows or my.cnf for other OS like linux. It has to be declared in the Server section, which is the section after [mysqld]:

[mysqld]
default-character-set=utf8

3. Select the database using below command
use database name

4. Use the status command to verify database character encoding information.

mysql> status;
————–
mysql  Ver 14.12 Distrib 5.0.85, for pc-linux-gnu (i686) using readline 5.1

Connection id:          2464150
Current database:       databasename
Current user:           manoj@192.168.200.83
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ”
Using delimiter:        ;
Server version:         5.5.8-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             192.168.2.85 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 15 days 1 hour 10 min 31 sec

Threads: 103  Questions: 539733512  Slow queries: 32331  Opens: 354  Flush tables: 1  Open tables: 237  Queries per second avg: 415.105

5. In some cases, the individual tables collation and character encoding may differ from the one that the database as a whole has been configured to use. Please use the command below to ensure all tables within your manoj database are correctly configured to use UTF-8 character encoding and binary UTF-8 collation:

use manoj;
show table status;

Check for the value listed under the Collation column, to ensure it has been set to utf8_bin (that is, case-sensitive) collation for all tables.
If not, then this can be changed by the following command, executed for each table in the manoj database:

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

Please substitute the <tablename> above, with each table within the manoj database.

6. How to create database for utf8_bin
CREATE DATABASE Manoj CHARACTER SET utf8 COLLATE utf8_bin;

Thanks
Manoj

Not able to start Mysql after enabling innodb

June 3rd, 2011

I upgraded Mysql 5.0 to Mysql 5.5.8 and start mysql. It was working fine but when i enable innodb and try to start Mysql i was not able to start it.

I checked the Mysql error logs, saw below errors

[root@manoj mysql]# tail /var/log/mysqld.log
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 1073741824 bytes!
110603  7:34:15 [ERROR] Plugin ‘InnoDB’ init function returned error.
110603  7:34:15 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
110603  7:34:15 [ERROR] Unknown/unsupported storage engine: InnoDB
110603  7:34:15 [ERROR] Aborting

10603  7:34:15 [Note] /usr/sbin/mysqld: Shutdown complete

I moved the ib_logfile0 and ib_logfile01 to bak and start Mysql again. Now this time, it is working fine

[root@Manoj mysql]# mv ib_logfile0 ib_logfile0-bak
[root@Manoj mysql]# mv ib_logfile1 ib_logfile1-bak

Thanks
Manoj

SMTP authentication using Mysql

March 10th, 2011

There are times when you need to have users authenticate their SMTP sessions. Perhaps you have roaming users and you don’t want to be an open relay, but you cannot predict where these users are. You need a way for them to say to your SMTP server “hey I belong here, let me send email”.

One way to do is is using SMTP Authentication. The user’s username and password are sent to the SMTP server. The server then checks the pair is correct and lets the user then send mail (or not if they are incorrect). SMTP Authentication is defined in RFC 2554.

Postfix has a method of authentication, but it is tied up with SASL. For file-based authentication you just create a special password database. However for other types you cannot simply make a LDAP or MySQL table and be done with it. You can either use SASL natively or do it the way I have implemented it here where Postfix uses SASL which uses PAM which uses MySQL; around-about way but it does work. There is some sporadic documentation about this around The Internet, but I wrote this up in the hope you find it useful and so I don’t have to remember it or relearn it all over again.

You might also be able to adapt this method to use other sorts of PAM authentication. For example I’m pretty sure this method with a little adaption would also work for LDAP authentication. Obviously you could use other databases other than MySQL, its just what I was using here.
Required Packages

The following Debian packages are required to get this all working. I’m using debian Sarge here but for the most part it should work for other versions and dists with some small changes. Some other packages will be needed, but will be pulled in as dependencies.

postfix-tls 2.1.5-9
The main postfix server with TLS and SASL support.
libsasl2-modules 2.1.19-1.5
Modules that provide the LOGIN,PLAIN, ANONYMOUS, OTP, CRAM-MD5, and DIGEST-MD5 (with DES support) authentication methods.
libpam-mysql 0.4.7-1
PAM module to query a MySQL database – only for MySQL authentication.
metamail
Useful for base64 encoding and decoding using mimencode.

You have to make sure that either one or both of the authentication modules packages are installed. If you don’t and you setup Postfix to use SASL (see below) then the stupid process will be throttled. For older distributions you may need the libsasl (no 2) packages.
Postfix and MySQL socket problem

Postfix runs the smtpd daemon in a chrooted environment, usually something like /var/spool/postfix. That means that as far as the smtpd process is concerned you have nothing above that point. MySQL has a socket sitting in another directory, something like /var/run/mysql/mysqld.sock. The problem is that the socket sits in an area that smtpd believes doesn’t exist and cannot get to anyway because of the chroot.

To get around this problem, you have 3 options: 1. Stop smtpd from running into a chroot. 2. Move the mysql socket into the chroot. 3. Don’t use the mysql socket, use a TCP socket instead.

The last two are reasonably simple, possibly the third is the best option (you can make mysqld listen only to the loopback interface). Look at the MySQL documentation about how to move sockets or make it listen on its TCP port.
Stopping smtpd from being in a chroot

This had me going for a long, long time. To change this, edit /etc/postfix/master.cf and change the following line:

smtp inet n – n – – smtpd

The second ‘n’ means it is not chrooted. There may be a way of running smtpd in a chroot with the SASL and MySQL authentication but I’m not sure how.
Postfix Changes

The following lines are added to /etc/postfix/main.cf

smtpd_sasl_auth_enable = yes
smtpd_sasl_local_domain = myserver
broken_sasl_auth_clients = yes
smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject

SASL Files Setup

So far the postfix server knows it has to use SASL if it gets an authentication request. The default way for SASL to work out if you are authenticated is for it to examine a Berkley DB file called /etc/sasldb2. You can add and change users using the saslpasswd2 program.

The problem here is if you run smtpd in a chroot environment then it will not find the sasldb file. If you try to authenticate postfix will give an error “warning: SASL authentication problem: unable to open Berkeley db /etc/sasldb2: No such file or directory”. The problem here is that you have a /etc/sasldb2 file, but postfix is looking for a /var/spool/postfix/etc/sasldb2 file.

The two solutions for this problem are to either not run postfix in a chroot environment (see a previous section on how to stop it) or get that sasldb2 file into the correct directory. You can put it right by copying it. You will also need to make sure the user that smtpd runs as can read the file.

Debian users can automatically get this file updated by editing /etc/init.d/postfix. Around line 43 there is a list of files that are copied from their real directories into the chroot. Change the line so it looks like:

FILES=”etc/localtime etc/services etc/resolv.conf etc/hosts \
etc/nsswitch.conf etc/sasldb2″

Now when postfix is restarted you have the new sasldb2 ready to go.

If you are doing file-based authentication then you are done, drop down to the Testing section.
MySQL SASL Setup

For MySQL authentication, the next step is to get SASL to ask PAM to authenticate the user. There’s some confusion because the location of this file has moved around. On my system with the versions of the packages given above, it is found at /etc/postfix/sasl/smtpd.conf but it also has been found in /usr/local/lib/sasl/smtpd.conf and /usr/lib/sasl/smtp.conf. The file is real simple one-liner:

pwcheck_method: pam

That’s it for SASL, it will then use standard PAM as we all know and love for authenticating.
PAM Setup

The PAM setup is pretty standard. All you need to know is the PAM service is called smtp, so you need to create a file /etc/pam.d/smtp. SASL only uses the authentication management group.

It might be useful to test how things are going so far. To do this, and only for testing, you can use the pam_permit module. This module permits anything you send, so its useful for testing or for some strange circumstances, but shouldn’t be used in a production environment. The file /etc/pam.d/smtp would then look like:

auth required pam_permit.so

If you are going to run it with MySQL, use a configuration similar to that shown below. The configuration is similar to a user doing the following:

server$ mysql -u postfix -psecret postfixdb
mysql> SELECT id FROM users WHERE id=’givenusername’ AND password=’givenpassword’;

auth required pam_mysql.so user=postfix passwd=secret db=postfixdb table=users usercolumn=id passwdcolumn=password crypt=0

The table users has two columns. The first is called id and has the username, the second is password it has the unencrypted password in it. A select is made checking both username and password. If there is a single row returned, authentication is successful.
Testing

I use the plain authentication method for testing. To do this you need to convert the username and password into a base64 encoded string. For example, if you have username user and password pass, you would type:

server$ printf ‘useruserpass’ | mimencode
dXNlcgB1c2VyAHBhc3M=

So the string is the username and password joined together with between them. The username is needed twice. To test it, telnet to the SMTP port of your server and type the auth commands.

server$ telnet mail.my.server 25
Trying 10.1.2.3
Connected to 10.1.2.3.
Escape character is ‘^]’.
220 mail.my.server ESMTP Postfix
EHLO blah
250-mail.my.server
250-PIPELINING
250-SIZE 10240000
250-VRFY
250-ETRN
250-AUTH LOGIN PLAIN CRAM-MD5 DIGEST-MD5
250-AUTH=LOGIN PLAIN CRAM-MD5 DIGEST-MD5
250-XVERP
250 8BITMIME
auth plain dXNlcgB1c2VyAHBhc3M=
235 Authentication successful

I’ve used a EHLO instead of the normal HELO as this is an extended hello, so the server gives you a list of things it can do. Notice that there are two AUTH lines, this is due to the broken_sasl_auth_clients line in /etc/postfix/main.cf.

You may have different authentication modules, it depends on what packages you have installed.

The important thing is the server’s response to your commands is 235 Authentication successful. This means that it recognizes the username and password. If it doesn’t, it returns a 535 Error: authentication failed. If you get a failed message, check the mail logs. The logs should tell you why the authentication failed.

Instead of using the plain authentication, you might want to use the LOGIN method. Once again mimencode is used to get the base64 encoding:

server$ printf ‘user’ | mimencode
dXNlcg==
server$ printf ‘pass’ | mimencode
cGFzcw==

You now have the two base64 encoded strings, to test this method is very similar to the PLAIN method.

server$ telnet 10.1.2.3 25
Trying 10.1.2.3…
Connected to 10.1.2.3.
Escape character is ‘^]’.
220 my.mail.server ESMTP Postfix
EHLO blah
250-my.mail.server
250-PIPELINING
250-SIZE 10240000
250-VRFY
250-ETRN
250-AUTH LOGIN PLAIN CRAM-MD5 DIGEST-MD5
250-AUTH=LOGIN PLAIN CRAM-MD5 DIGEST-MD5
250-XVERP
250 8BITMIME
auth login
334 VXNlcm5hbWU6
dXNlcg==
334 UGFzc3dvcmQ6
cGFzcw==
235 Authentication successful

You might wonder what that strange text is after the 334 numbers. Once again mimencode can help. It’s a base64 encoding of the response from the mail server.

server$ printf ‘VXNlcm5hbWU6′ | mimencode -u ; echo
Username:
server$ printf ‘UGFzc3dvcmQ6′ | mimencode -u ; echo
Password:

So the mail server is asking for a username and password, in base64. I don’t know why they bother to do this as it doesn’t make it that much more secure but at least you now know what it is.
Client Configuration

OK, so you have you server setup that can do authentication, but now you want your laptop that is running Postfix to relay all email through your server. This section describes the client setup.
Postfix Setup

Setting up Postfix is pretty simple. Tell Postfix to send all email to your mail server and enable SASL. The file /etc/postfix/main.cf requires the following lines:

relayhost = mail.example.net
smtp_sasl_auth_enable = yes
smtp_sasl_password_maps = hash:/etc/postfix/sasl/passwd
smtp_sasl_security_options =

The configuration is telling postfix to send all email to mail.example.net, use SASL authentication and that the passwords are found in a particular file. Remember for outgoing mail Postfix uses smtp while incoming uses smtpd. As the client sends email the configuration lines have the “d less” smtp_ keywords.
Client Password file

The format of the client password file is simple, especially if you have written hash tables for Postfix before. The key is the remote server and the value is the username and password to use for that server separated by a colon.

mail.example.net myuser:secpasswd