diff options
| -rw-r--r-- | fripost-docs.org | 184 |
1 files changed, 164 insertions, 20 deletions
diff --git a/fripost-docs.org b/fripost-docs.org index bd47baa..296e942 100644 --- a/fripost-docs.org +++ b/fripost-docs.org @@ -354,22 +354,174 @@ no-port-forwarding $THE_PUBLIC_KEY" | sudo -u $TUNNEL_USER tee $TUNNEL_HOME/.ssh *** Installing MySQL - sudo apt-get install mysql-server - generate a long (25 characters) password for the mysql root user - - /etc/mysql/my.cnf -:HIDDEN: -skip-innodb -:END: + - /etc/mysql/my.cnf: skip-innodb +*** MySQL on the main IMAP server +**** Overview + +We will use four tables `alias', `domain', `log' and `mailbox'. + + +***** mysql> show tables; ++----------------+ +| Tables_in_mail | ++----------------+ +| alias | +| domain | +| log | +| mailbox | ++----------------+ +4 rows in set (0.00 sec) + +***** mysql> describe alias; ++-------------+--------------+------+-----+---------------------+-------+ +| Field | Type | Null | Key | Default | Extra | ++-------------+--------------+------+-----+---------------------+-------+ +| address | varchar(255) | NO | PRI | | | +| goto | text | NO | | NULL | | +| domain | varchar(255) | NO | | | | +| create_date | datetime | NO | | 0000-00-00 00:00:00 | | +| change_date | timestamp | NO | | CURRENT_TIMESTAMP | | +| active | tinyint(4) | NO | | 1 | | ++-------------+--------------+------+-----+---------------------+-------+ +6 rows in set (0.00 sec) + +***** mysql> describe domain; ++-------------+--------------+------+-----+---------------------+-------+ +| Field | Type | Null | Key | Default | Extra | ++-------------+--------------+------+-----+---------------------+-------+ +| domain | varchar(255) | NO | PRI | | | +| description | varchar(255) | NO | | | | +| create_date | datetime | NO | | 0000-00-00 00:00:00 | | +| change_date | timestamp | NO | | CURRENT_TIMESTAMP | | +| active | tinyint(4) | NO | | 1 | | ++-------------+--------------+------+-----+---------------------+-------+ +5 rows in set (0.00 sec) + +***** mysql> describe log; ++-------+-------------+------+-----+-------------------+----------------+ +| Field | Type | Null | Key | Default | Extra | ++-------+-------------+------+-----+-------------------+----------------+ +| id | int(11) | NO | PRI | NULL | auto_increment | +| user | varchar(20) | NO | | | | +| event | text | NO | | NULL | | +| date | timestamp | NO | | CURRENT_TIMESTAMP | | ++-------+-------------+------+-----+-------------------+----------------+ +4 rows in set (0.00 sec) + +***** mysql> describe mailbox; ++-------------+--------------+------+-----+---------------------+-------+ +| Field | Type | Null | Key | Default | Extra | ++-------------+--------------+------+-----+---------------------+-------+ +| username | varchar(255) | NO | PRI | | | +| password | varchar(255) | NO | | | | +| name | varchar(255) | NO | | | | +| maildir | varchar(255) | NO | | | | +| domain | varchar(255) | NO | | | | +| create_date | datetime | NO | | 0000-00-00 00:00:00 | | +| change_date | timestamp | NO | | CURRENT_TIMESTAMP | | +| active | tinyint(4) | NO | | 1 | | ++-------------+--------------+------+-----+---------------------+-------+ +8 rows in set (0.00 sec) + +**** Steps to produce it +mysql -u root -p + + create database mail; + +sudo mysql -u root -p --database=mail +FIXME: Not 100 % up to date + :HIDDEN: +DROP TABLE IF EXISTS `alias`; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +CREATE TABLE `alias` ( + `address` varchar(255) NOT NULL default '', + `goto` text NOT NULL, + `domain` varchar(255) NOT NULL default '', + `create_date` datetime NOT NULL default '0000-00-00 00:00:00', + `change_date` datetime NOT NULL default '0000-00-00 00:00:00', + `active` tinyint(4) NOT NULL default '1', + PRIMARY KEY (`address`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Virtual Aliases - mysql_virtual_\nalias_maps'; +SET character_set_client = @saved_cs_client; + +DROP TABLE IF EXISTS `domain`; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +CREATE TABLE `domain` ( + `domain` varchar(255) NOT NULL default '', + `description` varchar(255) NOT NULL default '', + `create_date` datetime NOT NULL default '0000-00-00 00:00:00', + `change_date` datetime NOT NULL default '0000-00-00 00:00:00', + `active` tinyint(4) NOT NULL default '1', + PRIMARY KEY (`domain`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Virtual Domains - mysql_virtual_\ndomains_maps'; +SET character_set_client = @saved_cs_client; + +DROP TABLE IF EXISTS `log`; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +CREATE TABLE `log` ( + `id` int(11) NOT NULL auto_increment, + `user` varchar(20) NOT NULL default '', + `event` text NOT NULL, + `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY (`id`) +) ENGINE=MyISAM AUTO_INCREMENT=106 DEFAULT CHARSET=utf8 COMMENT='log table'; +SET character_set_client = @saved_cs_client; + +DROP TABLE IF EXISTS `mailbox`; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +CREATE TABLE `mailbox` ( + `username` varchar(255) NOT NULL default '', + `password` varchar(255) NOT NULL default '', + `name` varchar(255) NOT NULL default '', + `maildir` varchar(255) NOT NULL default '', + `domain` varchar(255) NOT NULL default '', + `create_date` datetime NOT NULL default '0000-00-00 00:00:00', + `change_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + `active` tinyint(4) NOT NULL default '1', + PRIMARY KEY (`username`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Virtual Mailboxes - mysql_virtua\nl_mailbox_maps'; +SET character_set_client = @saved_cs_client; + :END: + +mysql -u root -p + +# Create triggers + + use mail; + + DELIMITER $$ + CREATE TRIGGER alias_set_created_on_insert before insert on alias + for each row begin set new.create_date = current_timestamp; end$$ + CREATE TRIGGER domain_set_created_on_insert before insert on domain + for each row begin set new.create_date = current_timestamp; end$$ + CREATE TRIGGER mailbox_set_created_on_insert before insert on mailbox + for each row begin set new.create_date = current_timestamp; end$$ + DELIMITER ; + +# Create mail user + + CREATE USER 'mail'@'localhost' IDENTIFIED BY 'mijhl9hniiMu5WxvvtdgsacxZ'; + GRANT SELECT ON mail.alias TO 'mail'@'localhost'; + GRANT SELECT ON mail.domain TO 'mail'@'localhost'; + GRANT SELECT ON mail.mailbox TO 'mail'@'localhost'; + *** Configuring the MySQL replication ***** Overview - [[http://dev.mysql.com/doc/refman/5.0/en/replication.html][MySQL 5.0 Reference Manual :: 16 Replication]] +[[http://dev.mysql.com/doc/refman/5.0/en/replication.html][MySQL 5.0 Reference Manual :: 16 Replication]] - We will use MySQL replication to keep the MySQL user data on the smarthosts - in sync with the data held on the main IMAP server. - We begin by setting up an SSH tunnel, as described above. The rest is - fairly straight-forward. Here are instructions adapted from the MySQL - manual. +We will use MySQL replication to keep the MySQL user data on the smarthosts +in sync with the data held on the main IMAP server. - - Set up the SSH tunnel. +We begin by setting up an SSH tunnel, as described above. The rest is +fairly straight-forward. Here are instructions adapted from the MySQL +manual. + +- Set up the SSH tunnel. ***** Configure the master @@ -618,15 +770,7 @@ First setup an SSH tunnel between the hosts according to instructions given above. Next, we need to configure postfix on the smarthost to relay emails through the -tunnel: - - One quick-and-dirty example to try it out is: - - /etc/postfix/main.cf - relay_domains = fripost.org - transport_maps = hash:/etc/postfix/transport - - /etc/postfix/transport - fripost.org smtp:localhost:1917 - - sudo postmap hash:/etc/postfix/transport +tunnel. ** Configuring the webserver |