How to configure dovecot to use mysql for authentication

In dovecot.conf you want to use sql as your userdb and passdb:

passdb sql {
    args = /etc/dovecot/dovecot-sql.conf
}

userdb sql {
    args = /etc/dovecot/dovecot-sql.conf
}

dovecot-sql.conf can be anything you want, but it should be chown'd by root and not be world readable as it will contain sensitive information (Dovecot will read this file as root so you don't have to worry about permissions issues).

Now in dovecot-sql.conf you want something like this:

driver = mysql
connect = host={HOST} dbname={DBNAME} user={USER} password={PASSWORD}
default_pass_scheme = {SCHEME}
user_query = {USER_QUERY}
password_query = {PASSWORD_QUERY}

Now for the various values:

{HOST}: this specifies how you want to connect to MySQL it can be either A) a path to a UNIX socket (/var/run/mysql/mysql.sock) B) IP address (127.0.0.1) or C) hostname (localhost).

{DBNAME}, {USER}, {PASSWORD}: straightforward, the name of the database you're querying, the user you're authenticating as, and their password.

{SCHEME}: How the user's password is stored in the database. It can be any of these. Most likely you're either storing it in cleartext or hashing it with MD5 or SHA1. Choose whatever is appropriate.

{USER_QUERY}: This the exact SQL query that dovecot will execute to fetch information on your users (disregarding the substitutions it will make). The form of this is heavily dependent on how your database is setup. But no matter what the query needs to return a single row with the following columns: home or mail, uid, gid. You will need to use AS clauses in your SQL statement if these columns are not named as such. Dovecot performs variable substitution on the SQL statement before executing it. You can use most the variables listed here. Here is my user query as an example:

SELECT home, 8 AS uid, 12 AS gid FROM user WHERE username = '%u'

Since I have just virtual users the uid and gid for all users are 8 and 12 (for the "mail" user and "mail" group on my system, notice the use of 'AS' to get the right column names). In my setup each user has a "mail home" that Dovecot can use to store per user information and the actual mailbox location is based off of that. In a simpler setup you probably want to point dovecot directly at your users' mailboxes in which case you need to return a mail field instead of a home field.

{PASSWORD_QUERY}: This is the statement that Dovecot uses to authenticate your user. You need to return a single row with a column named "password". This password should use the scheme you specified above. Mine is:

SELECT password FROM user WHERE username = '%u'

Note that you don't have to use MySQL for both your password query and your user query. In fact it would probably be easier to use a static userdb and then specify mail and home locations based on a template that performs variable substitution like in the SQL statements.

Also, Dovecot has a pretty good wiki full of information. A more in depth discussion of using SQL as an authentication database can be found here. There should also be heavily commented example configuration files in your distribution's dovecot configuration directory.