Mail Server - Creating the MySQL Database


Now that we have Postfix and MySQL installed we need to create the database to hold the domain and user details.

Contents

 

Overview

The process of creating the database can look complicated. However, take your time over what we are actually doing and you will see the concept is very simple.

Don't be put off by the mass of SQL we'll use - we are simply creating the database from the command line.

Let's get cracking!

Make the Database

Let's start by creating the mail database - called, rather originally, 'mail':

mysqladmin -u root -p create mail

You will be prompted for the MySQL root password (which was set during the MySQL install).

 

User creation

Now we need to log into MySQL to create the various tables:

mysql -u root -p

Again, you will need to enter your MySQL root password.

We need to create a user for the database, just as we would with any other database. In this case, we will have 'mailadmin' with a password of your choice:

CREATE USER 'mailadmin'@'localhost' IDENTIFIED BY 'newpassword';

Replace 'newpassword' with a password of your choosing.

Next we need to flush the privileges:

FLUSH PRIVILEGES;

As with any user, we need to set a permission level for the 'mailadmin' user.

In this case we want the user to be able to use the 'mail' database and, more specifically, we want them to be able to SELECT, INSERT, UPDATE, DELETE data as needed:

GRANT SELECT, INSERT, UPDATE, DELETE ON `mail` . * TO 'mailadmin'@'localhost';

Again, flush the privileges:

FLUSH PRIVILEGES;

Table Creation

Now we've created the MySQL user, we can move into using the actual 'mail' database and create the necessary tables:

USE mail;

The output will let you know the database has changed.

Here comes the part that can look complicated.

However, all we are doing is creating three tables to hold the domain, user and forward details ('forward' simply forwards mail from one user to another. For example, sales@example.com will be forwarded to admin@example.com).

Domains table

Still in MySQL enter these details:

CREATE TABLE domains (
domain varchar(50) NOT NULL,
PRIMARY KEY (domain)
)
TYPE=MyISAM;

NOTE:  Newer versions of MySQL will require that you replace "TYPE=MyISAM;" in this and the following commands with "ENGINE=MyISAM;".

Until you enter the semi-colon (;) at the end, the command won't be executed. As such, you can put the command over several lines so it is clear to read and understand.

Users table

As before, enter the details into MySQL:

CREATE TABLE users (
email varchar(80) NOT NULL,
password varchar(20) NOT NULL,
PRIMARY KEY (email)
)
TYPE=MyISAM;

Forward table

And finally:

CREATE TABLE forwards (
source varchar(80) NOT NULL,
destination TEXT NOT NULL,
PRIMARY KEY (source)
)
TYPE=MyISAM;

Now we can leave MySQL:

quit;

You will be placed back to the command prompt in your terminal.

Summary

What we have done is to create a database named 'mail'. We also created a user named 'mailadmin' that has certain privileges on the database (namely, they can manipulate the data as needed).

The three tables that were added to the 'mail' database are very simply and hold the domain details, user details and any forwarding details we need.

You may notice that at this stage the password field of the users table is in plain text. When we add the password to MySQL we will encrypt it.

Now we have the base MySQL database setup with relevant tables for our domains and users.

The next article will look at configuring Postfix to examine and use the MySQL database.

Previous Article
Next Article



Was this content helpful?




© 2014 Rackspace US, Inc.

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License


See license specifics and DISCLAIMER