LvXWarji

1CREATE DATABASE IF NOT EXISTS mailserver
2  CHARACTER SET = 'utf8mb4'
3  COLLATE = 'utf8mb4';
4
5USE mailserver;
6CREATE TABLE IF NOT EXISTS `virtual_domains` (
7 `id` int(11) NOT NULL auto_increment,
8 `name` varchar(50) NOT NULL,
9 PRIMARY KEY (`id`)
10 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
11
12USE mailserver;
13CREATE TABLE IF NOT EXISTS `virtual_users` (
14 `id` int(11) NOT NULL auto_increment,
15 `domain_id` int(11) NOT NULL,
16 `email` varchar(100) NOT NULL,
17 `password` varchar(150) NOT NULL,
18 PRIMARY KEY (`id`),
19 UNIQUE KEY `email` (`email`),
20 FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
21 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
22
23USE mailserver;
24CREATE TABLE IF NOT EXISTS `virtual_aliases` (
25 `id` int(11) NOT NULL auto_increment,
26 `domain_id` int(11) NOT NULL,
27 `source` varchar(100) NOT NULL,
28 `destination` varchar(100) NOT NULL,
29 PRIMARY KEY (`id`),
30 FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
31 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
32
33CREATE USER 'mailuser'@'%' IDENTIFIED BY '$1$cYbwHkQ9$l1x2qsz1W5L0Dl9dJdSqx.';
34GRANT SELECt ON mailserver.* TO 'mailuser'@'%';