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.
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).
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:
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:
Now we've created the MySQL user, we can move into using the actual 'mail' database and create the necessary tables:
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, email@example.com will be forwarded to firstname.lastname@example.org).
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.
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;
CREATE TABLE forwards ( source varchar(80) NOT NULL, destination TEXT NOT NULL, PRIMARY KEY (source) ) TYPE=MyISAM;
Now we can leave MySQL:
You will be placed back to the command prompt in your terminal.
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.
© 2015 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