Improve replication configuration

1 files changed, 120 insertions, 130 deletions

diff --git a/fripost-docs.org b/fripost-docs.org
index 9bd947f..9004ff5 100644
--- a/fripost-docs.org
+++ b/fripost-docs.org
@@ -333,14 +333,102 @@ no-port-forwarding $THE_PUBLIC_KEY" | sudo -u $TUNNEL_USER tee $TUNNEL_HOME/.ssh
 
    telnet localhost $ORIGIN_PORT
 
-*** Basic configuration of MySQL
-**** Installing MySQL
+*** 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:
+*** Configuring the MySQL replication
+***** Overview
+     [[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.
+
+     - Set up the SSH tunnel.
+
+***** Configure the master
+
+     - Add this to my.cnf:
+:HIDDEN:
+server-id		= 1
+log_bin			= /var/log/mysql/mysql-bin.log
+expire_logs_days	= 10
+max_binlog_size         = 100M
+binlog_do_db		= mail
+:END:
+
+     - /etc/init.d/mysql restart
+
+     - Enter MySQL shell and create user with replication privileges:
+       mysql -u root -p
+
+       # use only ASCII for <password>
+
+       GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'localhost' IDENTIFIED BY '<password>';
+       FLUSH PRIVILEGES;
+
+       USE mail;
+       FLUSH TABLES WITH READ LOCK;
+
+       # Save the output of this command:
+       SHOW MASTER STATUS;
+
+       unlock tables;
+       quit;
+
+       # Copy this file to the slave:
+       mysqldump -u root -p --opt mail > mydump.sql
+       
+***** Configure the slave
+
+      - Enter the MySQL shell and create the database:
+
+        mysql -u root -p
+        CREATE DATABASE mail;
+        quit;
+  
+        mysql -u root -p --database=mail < mydump.sql
+  
+      - create a new temporary directory:
+        
+        sudo mkdir /var/lib/mysql/tmp
+        sudo chown mysql:mysql !$
+        sudo chmod 0750 !$
+
+      - /etc/mysql/my.cnf
+:HIDDEN:
+tmpdir		= /var/lib/mysql/tmp
+# Note that the server-id must be different on all hosts
+server-id		= 2
+:END:
+      - /etc/init.d/mysql restart
+
+      SLAVE STOP;
+      # [[http://dev.mysql.com/doc/refman/5.0/en/change-master-to.html][12.5.2.1. CHANGE MASTER TO Syntax]]
+      # NOTE: fill in these values using output from SHOW MASTER STATUS; above
+
+      CHANGE MASTER TO
+      MASTER_HOST='127.0.0.1',
+      MASTER_PORT=1949,
+      MASTER_USER='slave_user',
+      MASTER_PASSWORD='<password>', MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=98;
+
+      START SLAVE;
+      quit;
+
+***** Useful commands while debugging
+       start slave; stop slave;
+       show slave status\G
+
+*** Configuring the main IMAP server
+**** /etc/postfix/main.cf
 
 **** MySQL on the main IMAP server
 
@@ -361,8 +449,8 @@ mysql> describe log;
 mysql> describe mailbox;
      :END:
 
-      - sudo mysql -u root -p --database=mail
-        :HIDDEN:
+     - sudo mysql -u root -p --database=mail
+       :HIDDEN:
 DROP TABLE IF EXISTS `alias`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
@@ -418,101 +506,14 @@ CREATE TABLE `mailbox` (
 ) 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 USER 'mail'@'localhost' IDENTIFIED BY 'secret';
+       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
-     
-     [[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.  This process is described above.
-     The rest is fairly straight-forward (instructions below adapted from [[http://www.howtoforge.com/mysql_database_replication][here]]).
-
-     - Set up the SSH tunnel.
-
-***** Configure the master
-
-     - Add this to my.cnf:
-:HIDDEN:
-server-id		= 1
-log_bin			= /var/log/mysql/mysql-bin.log
-expire_logs_days	= 10
-max_binlog_size         = 100M
-binlog_do_db		= mail
-:END:
-
-     - /etc/init.d/mysql restart
-
-     - Enter MySQL shell and create user with replication privileges:
-       mysql -u root -p
-
-       When in shell, do the following (replace <password> with something better):
-
-       create database mail;
-
-       GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<password>';
-       FLUSH PRIVILEGES;
-
-       # Is this only needed when using "load data from master"?
-       grant reload, super, replication client on *.* to 'slave_user';
-
-       USE mail;
-       FLUSH TABLES WITH READ LOCK;
-       SHOW MASTER STATUS;
-
-       unlock tables;
-       quit;
-       
-***** Configure the slave
-
-      - Enter the MySQL shell and create the database:
+**** Test delivery
 
-      mysql -u root -p
-      Enter password:
-      CREATE DATABASE mail;
-      quit;
-
-      - /etc/mysql/my.cnf
-:HIDDEN:
-tmpdir		= /var/lib/mysql/tmp
-# Note that the server-id must be different on all hosts
-server-id		= 2
-master-host		= 127.0.0.1
-master-port		= 1949
-master-user		= slave_user
-master-password		= <password>
-master-connect-retry	= 60
-replicate-do-db		= mail
-:END:
-      - create the temporary directory:
-        
-        mkdir /var/lib/mysql/tmp
-        chown mysql:mysql !$
-        chmod 0750 !$
-
-      - /etc/init.d/mysql restart
-      - Enter the MySQL shell and make the replication:
-
-      mysql -u root -p
-      Enter password:
-      LOAD DATA FROM MASTER;
-      quit;
-
-      A strange bug bit me at this point, notes:
-
-      start slave; stop slave;
-      show slave status\G
-
-      [[http://dev.mysql.com/doc/refman/5.0/en/change-master-to.html][12.5.2.1. CHANGE MASTER TO Syntax]]
-
-      CHANGE MASTER TO MASTER_PORT=1949, MASTER_CONNECT_RETRY=60;
-
-*** Configuring the main IMAP server to receive e-mail to Maildir
-
-    First setup the tables like above.
-
-    - sudo apt-get install maildrop
     - /etc/postfix/main.cf
 :HIDDEN:
 # Not really needed until we switch to using Courier maildrop
@@ -521,7 +522,6 @@ maildrop_destination_recipient_limit = 1
 virtual_mailbox_base = /home/mail/virtual
 :END:
     - sudo mkdir -p /home/mail/virtual/fripost.org/example/
-    - sudo maildirmake /home/mail/virtual/fripost.org/example/Maildir
     - mysql -u root -p
       INSERT INTO mailbox (username,password,name,maildir,domain)
       VALUES ('exempel@fripost.org','test666','Exempelanvändare','fripost.org/exempel/Maildir/','fripost.org');
@@ -529,37 +529,13 @@ virtual_mailbox_base = /home/mail/virtual
 
     Now it should work to send an e-mail to exempel@fripost.org
 
+**** Setting up dovecot
 
-*** Configuring a new smarthost to relay e-mail to the main IMAP server
-
-    Definitions:
-    IMAP server = the main storage server
-    smarthost = the receiving server (configured as MX)
-
-    First setup an SSH tunnel between the hosts according to instructions given
-    above in this document.
-
-    Next, you 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
-
-
-*** Setting up dovecot
-
-    - sudo apt-get install dovecot
-
+    - sudo aptitude install dovecot-imapd
     - /etc/dovecot/dovecot.conf
 
-      Note: These settings are already in the file but commented out or set to
-      other values.
-
+Note: These settings are already in the file but commented out or set to other
+      values.
 :HIDDEN:
 protocols = imaps
 protocol imap {
@@ -592,9 +568,7 @@ userdb sql {
 # Do not needlessly run as root
 user = nobody
 :END:
-
     - /etc/dovecot/dovecot-sql.conf
-
 :HIDDEN:
 driver = mysql
 connect = host=127.0.0.1 port=3306 user=XXX password=XXX dbname=mail
@@ -607,10 +581,26 @@ password_query = SELECT username AS user, password FROM mailbox WHERE username =
 # replace XXX with relevant numbers for the system
 user_query = SELECT concat('/home/mail/virtual/',maildir) AS mail, XXX AS uid, XXX AS gid FROM mailbox WHERE username = '%u' AND domain = '%d'
 :END:
-
     - sudo /etc/init.d/dovecot restart
 
-      After adding a user, you should be able to login using any IMAP client.
+    Provided there is a user, you should now be able to login using any IMAP
+    client.
+
+*** Configuring a new smarthost to relay e-mail to the main IMAP server
+
+    First setup an SSH tunnel between the hosts according to instructions given
+    above in this document.
+
+    Next, you 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
 
 ** Configuring the webserver