From 42b8f00ec4b8033a51f6b55b995793694bdc1fce Mon Sep 17 00:00:00 2001 From: Stefan Kangas Date: Tue, 8 Feb 2011 16:53:40 +0100 Subject: Improve replication configuration --- fripost-docs.org | 250 ++++++++++++++++++++++++++----------------------------- 1 file 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 + + GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'localhost' IDENTIFIED BY ''; + 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='', 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 with something better): - - create database mail; - - GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY ''; - 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 = -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 -- cgit v1.2.3