MySQL Master-Master Replication with Heartbeat Rumi, December 5, 2012December 5, 2012 Setting Up The Two Base Systems In this setup there will be one master server (which runs the ISPConfig control panel interface) and one slave server which mirrors the web (apache), email (postfix and dovecot) and database (MySQL) services of the master server. To install the clustered setup, we need two servers with a Debian 6.0 minimal install. Install only steps 1 – 8 of the perfect server tutorial and not the other steps as they differ for a clustered setup! In my example I use the following hostnames and IP addresses for the two servers: Master Server Hostname: server1.example.tld IP-address: 192.168.0.105 Slave server Hostname: server2.example.tld IP-address: 192.168.0.106 Whereever these hostnames or IP addresses occur in the next installation steps you will have to change them to match the IP's and hostnames of your servers. It is a good idea to synchronize the system clock with an NTP (network time protocol) server over the Internet. Simply run apt-get -y install ntp ntpdate and your system time will always be in sync. On server 1: Now we create a private/public key pair on server1.example.tld: ssh-keygen -t dsa root@server1:~# ssh-keygen -t dsa Generating public/private dsa key pair. Enter file in which to save the key (/root/.ssh/id_dsa): <– ENTER Created directory '/root/.ssh'. Enter passphrase (empty for no passphrase): <– ENTER Enter same passphrase again: <– ENTER Your identification has been saved in /root/.ssh/id_dsa. Your public key has been saved in /root/.ssh/id_dsa.pub. The key fingerprint is: 1b:95:bc:4a:f4:9f:d8:ea:24:31:0f:c9:72:d5:a7:80 root@server1.example.com The key's randomart image is: +–[ DSA 1024]—-+ | | | o o | | E * . . | | o = o o | | . S o . | | + O + . | | + + + | | o . | | .o | +—————–+ root@server1:~# It is important that you do not enter a passphrase otherwise the mirroring will not work without human interaction so simply hit ENTER! Next, we copy our public key to server2.example.tld: ssh-copy-id -i $HOME/.ssh/id_dsa.pub root@192.168.0.106 root@server1:~# ssh-copy-id -i $HOME/.ssh/id_dsa.pub root@192.168.0.101 The authenticity of host '192.168.0.101 (192.168.0.101)' can't be established. RSA key fingerprint is 25:d8:7a:ee:c2:4b:1d:92:a7:3d:16:26:95:56:62:4e. Are you sure you want to continue connecting (yes/no)? <– yes (you will see this only if this is the first time you connect to server2) Warning: Permanently added '192.168.0.101' (RSA) to the list of known hosts. root@192.168.0.101's password: <– server2 root password Now try logging into the machine, with "ssh 'root@192.168.0.101'", and check in: .ssh/authorized_keys to make sure we haven't added extra keys that you weren't expecting. Now check on server2 if server1's public key has correctly been transferred: server2: cat $HOME/.ssh/authorized_keys ssh-dss AAAAB3NzaC1kc3MAAACBAPhiAexgEBexnw0rFG8lXwAuIsca/V+lhmv5lhF3BqUfAbL7e2sWlQlGhxZ8I2UnzZK8Ypffq6Ks+lp46yOs7MMXLqb7JBP9gkgqxyEWqOoUSt5hTE9ghupcCvE7rRMhefY5shLUnRkVH6hnCWe6yXSnH+Z8lHbcfp864GHkLDK1AAAAFQDddQckbfRG4C6LOQXTzRBpIiXzoQAAAIEAleevPHwi+a3fTDM2+Vm6EVqR5DkSLwDM7KVVNtFSkAY4GVCfhLFREsfuMkcBD9Bv2DrKF2Ay3OOh39269Z1rgYVk+/MFC6sYgB6apirMlHj3l4RR1g09LaM1OpRz7pc/GqIGsDt74D1ES2j0zrq5kslnX8wEWSHapPR0tziin6UAAACBAJHxgr+GKxAdWpxV5MkF+FTaKcxA2tWHJegjGFrYGU8BpzZ4VDFMiObuzBjZ+LrUs57BiwTGB/MQl9FKQEyEV4J+AgZCBxvg6n57YlVn6OEA0ukeJa29aFOcc0inEFfNhw2jAXt5LRyvuHD/C2gG78lwb6CxV02Z3sbTBdc43J6y root@server1.example.tld Install postfix, dovecot and mysql with one single command: apt-get -y install postfix postfix-mysql postfix-doc mysql-client mysql-server openssl getmail4 rkhunter binutils dovecot-imapd dovecot-pop3d sudo Enter the new password for the MySQL root user when requested by the installer. You should choose the same password for both servers. Then answer the next questions as decsribed below: General type of configuration? <– Internet site Mail name? <– server1.mydomain.tld SSL certificate required <– Ok Use server1.example.tld on the first server and server2.example.tld on the second server. We want MySQL to listen on all interfaces, not just localhost, therefore we edit /etc/mysql/my.cnf and comment out the line bind-address = 127.0.0.1: vi /etc/mysql/my.cnf […] # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = 127.0.0.1 […] Then restart MySQL: /etc/init.d/mysql restart Now we prepare the MySQL servers for mysql master/master replication. On server 1: Log into MySQL on the shell with… mysql -u root -p … and enter the MySQL root passord that you had choosen during mysql install. Then execute this commnd on the MySQL shell: GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY 'slave_user_password'; FLUSH PRIVILEGES; quit; Replace 'slave_user_password' with a secure password that you want to use for the slave to connect to the master server. Replace this placeholder in the next steps with the password that you had choosen wherever the placeholder occurs. Now let's configure our 2 MySQL nodes: On server 1: vi /etc/mysql/my.cnf Search for the section that starts with [mysqld], and put the following options into it (commenting out all existing conflicting options): […] [mysqld] server-id = 1 replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 1 master-host = 192.168.0.106 master-user = slaveuser master-password = slave_user_password master-connect-retry = 60 expire_logs_days = 10 max_binlog_size = 500M log_bin = /var/log/mysql/mysql-bin.log […] Then stop MySQL: /etc/init.d/mysql stop Now do nearly the same on server2… On server 2: vi /etc/mysql/my.cnf Search for the section that starts with [mysqld], and put the following options into it (commenting out all existing conflicting options): […] [mysqld] server-id = 2 replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 2 master-host = 192.168.0.105 master-user = slaveuser master-password = slave_user_password master-connect-retry = 60 expire_logs_days = 10 max_binlog_size = 500M log_bin = /var/log/mysql/mysql-bin.log […] Then stop MySQL: /etc/init.d/mysql stop Now we have to sync the two mysql servers. We do this by copying over the mysql data directory from the master to the slave and also the debian configuration file that contains the debian-sys-maint user. This can be done as we stopped mysql before on both servers. On server 1: scp -pr /var/lib/mysql/* root@server2.example.tld:/var/lib/mysql/ scp -pr /etc/mysql/debian.cnf root@server2.example.tld:/etc/mysql/debian.cnf Now we start MySQL on the master server again: /etc/init.d/mysql start Log into the MySQL shell as root user… mysql -u root -p … and execute this command in the MySQL shell… SHOW MASTER STATUS; … to get the MySQL master status: mysql> SHOW MASTER STATUS; +——————+———-+————–+——————+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +——————+———-+————–+——————+ | mysql-bin.000002 | 106 | | | +——————+———-+————–+——————+ 1 row in set (0.00 sec) The information that we need for the next step is the binlog file mysql-bin.000002 and the binlog position 106. We need the same details for server2 later below. Now execute this command in the MySQL shell on the master to connect it to the slave: STOP SLAVE; CHANGE MASTER TO MASTER_HOST='192.168.0.106', MASTER_USER='slaveuser', MASTER_PASSWORD='slave_user_password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106; START SLAVE; Then check the slave status: SHOW SLAVE STATUS \G It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output. On server 2: Log into the MySQL shell as root user… mysql -u root -p … and execute this command in the MySQL shell: STOP SLAVE; CHANGE MASTER TO MASTER_HOST='192.168.0.105', MASTER_USER='slaveuser', MASTER_PASSWORD='slave_user_password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106; START SLAVE; Then check the slave status: SHOW SLAVE STATUS \G It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output The configuration of the mysql master/master replication is finished now and we proceed to install the other software packages. For Heartbeat follow this Blog Post- https://tweenpath.net/?p=1157 Src: http://www.howtoforge.com/installing-a-web-email-and-mysql-database-cluster-on-debian-6.0-with-ispconfig-3 Administrations Configurations (Linux) DebianHAHeartbeatMySQL