Mail Server - Adding domains and users to MySQL

Following from the previous article, we now have our firewall opening the correct ports.

Now we can start adding domains and users to the MySQL 'mail' database.



We're going to be using the MySQL command line to add the domains and users.

You can, of course, use PHPMyAdmin or other MySQL GUI if you prefer. I'll leave that to you.

However, if you are using a GUI to add the details, make sure that when you add a user's password, you enter it using the MySQL 'ENCRYPT' function.

Let's log into MySQL:

mysql -u root -p

You will be prompted to enter your MySQL root password.

Once logged in, ensure you are using the 'mail' database:

USE mail;

Note the semicolon (;) at the end of the command. This lets MySQL know the command is to be executed - nothing will happen if you leave it off.


To add a domain is very simple:

INSERT INTO `domains` (`domain`) VALUES ('');

Note the use of backticks (`) in the first two fields and the use of single quotes (') when entering that actual value.

To add another domain uses exactly the same procedure:

INSERT INTO `domains` (`domain`) VALUES ('');


Adding a user email and password takes the same format:

INSERT INTO `users` (`email`, `password`) VALUES ('', ENCRYPT('secretpassword'));

Three things here:

Firstly, similar to when adding the domain details, note the distinction between backticks (`) and single quotes (').

Secondly, you must use the MySQL 'ENCRYPT' function when adding the password.

Lastly, change the 'secretpassword' to a more secure one of your choosing!

To add another user for the same domain:

INSERT INTO `users` (`email`, `password`) VALUES ('', ENCRYPT('secretpassword'));

and to add a user for the '' domain:

INSERT INTO `users` (`email`, `password`) VALUES ('', ENCRYPT('secretpassword'));

Once done:


That will place you back at the command prompt.

Reload Postfix

As we have made changes to database, we will need to reload postfix:

sudo postfix reload


Have a look at where the mail will be physically located:

ls /home/vmail

You will see that the folder is empty.

The correct folders are only created on receipt of the first email.

So. let's do just that by sending an email from the command line:


Enter a subject and body, then enter a single period (.) to indicate the message is ended.

Now look at the /home/vmail folder:

ls /home/vmail

You will see a new folder has been created named '':


You will need sudo permissions to go any further and examine the contents of the folder.

However, for each user under a folder will be created. If we look back up to when I created my users, I had 'jeff' and 'onion'. I sent a new email to each user and now the folder structure looks like:


Sending an email to the second domain (in my case, will create a '' folder and so on.

Make sure you send an email to each account you create - this will ensure the file structure is created.


Adding domains and multiple users is very easy using the MySQL command prompt.

Once created, send an email to the new user and the relevant file structure will be automatically created.

Now the mail server should be ready for use.

Once you've tested the mail server setup and are sure everything is working as planned, you might consider adding a spam filter like SpamAssassin to your environment.

Previous Article

© 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