From da612bc0141faf001390be38a706878a1a3b84bc Mon Sep 17 00:00:00 2001 From: Stefan Kangas Date: Sun, 27 Feb 2011 01:04:47 +0100 Subject: Improve MySQL replication instructions. --- fripost-docs.org | 132 ++++++++++++++++++++++++++++++------------------------- 1 file changed, 72 insertions(+), 60 deletions(-) diff --git a/fripost-docs.org b/fripost-docs.org index 62a902d..91ca235 100644 --- a/fripost-docs.org +++ b/fripost-docs.org @@ -512,89 +512,101 @@ mysql -u root -p ***** 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. +These instructions are mainly adapted from the MySQL manual. ***** 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/mysql/my.cnf: + + server-id = 1 + log_bin = /var/log/mysql/mysql-bin.log + expire_logs_days = 10 + max_binlog_size = 100M + binlog_do_db = mail + + +/etc/init.d/mysql restart - - /etc/init.d/mysql restart +***** Configure the slave +****** Set up an SSH tunnel - - Enter MySQL shell and create user with replication privileges: - mysql -u root -p +We begin by setting up an SSH tunnel from the slave to the master, as described [[Configuring an SSH tunnel between two hosts][above]]. - # use only ASCII for +****** Preparing steps to take on master - GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'localhost' IDENTIFIED BY ''; - FLUSH PRIVILEGES; +# Enter MySQL shell and create a user with replication privileges. +# NB: Use only ASCII for the +mysql -u root -p - USE mail; - FLUSH TABLES WITH READ LOCK; + GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'localhost' IDENTIFIED BY ''; + FLUSH PRIVILEGES; + USE mail; + FLUSH TABLES WITH READ LOCK; + quit; + +# Make a database dump. - # Save the output of this command: - SHOW MASTER STATUS; +mysqldump -u root -p --opt mail > mydump.sql - unlock tables; - quit; +# Now, copy this file to the slave. - # Copy this file to the slave: - mysqldump -u root -p --opt mail > mydump.sql - -***** Configure the slave +# Save the output of the SHOW MASTER STATUS COMMAND. +mysql -u root -p + + SHOW MASTER STATUS; + unlock tables; + quit; - - Enter the MySQL shell and create the database: +****** Slave configuration - mysql -u root -p - CREATE DATABASE mail; - quit; +# Create a new temporary directory. +# NOTE: It has to be outside of /tmp so the replication is not screwed up on e.g. power outage. + +TMP_DIR=/var/lib/mysql/tmp +sudo mkdir $TMP_DIR +sudo chown mysql:mysql $TMP_DIR +sudo chmod 0750 $TMP_DIR + + :: /etc/mysql/my.cnf + + tmpdir = /var/lib/mysql/tmp + # Note that the server-id must be different on all hosts + server-id = 2 + +/etc/init.d/mysql restart + +# Enter the MySQL shell and create the database: + +mysql -u root -p + + CREATE DATABASE mail; + quit; - mysql -u root -p --database=mail < mydump.sql +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 !$ +# [[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 +# NOTE: filling this in my.cnf is deprecated - - /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 +mysql -u root -p + + SLAVE STOP; - 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; - 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; + show slave status\G - START SLAVE; - quit; +# If it seems OK, just: -***** Useful commands while debugging - start slave; stop slave; - show slave status\G + quit; *** Configuring the main IMAP server **** /etc/postfix/main.cf -- cgit v1.2.3