Create a table for each of the domain names to be used for email addresses.

CREATE TABLE `mailserver`.`virtual_domains` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Create a table that will hold each of the users that will need mailboxes.

CREATE TABLE `mailserver`.`virtual_users` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`password` varchar(106) NOT NULL,
`email` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Create a table that will hold aliases (additional email addresses) for a particular user.

CREATE TABLE `mailserver`.`virtual_aliases` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`source` varchar(100) NOT NULL,
`destination` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert a new user into the database

First, we need to add our first domain name into the domains table

INSERT INTO mailserver . virtual_domains (name) VALUES ('rogerlovejoy.uk');

Second, we need to create the user. Replace mysupersecretpassword with your password.

INSERT INTO mailserver.virtual_users (`domain_id`, `password` , `email`)
VALUES ('1', ENCRYPT('mysupersecretpassword', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), '######@rogerlovejoy.uk');

Third, we can optionally specify an alias (secondary email address) for the user. INSERT INTO `mailserver`.`virtual_aliases`
(`domain_id`, `source`, `destination`)
VALUES
('1', 'firstname.lastname@mydomain.com', 'myuser@mydomain.com');

Type exit once you are done to leave the context of MariaDB.