Installing A Web, Email & MySQL Database Cluster On Debian 6.0 With ISPConfig 3

Version 1.0
Author: Till Brehm <t [dot] brehm [at] ispconfig [dot] org>
Last edited 02/10/2012

This tutorial describes the installation of a clustered web, email, database and DNS server to be used for redundancy, high availability and load balancing on Debian 6 with the ISPConfig 3 control panel. MySQL Master/Master replication will be used to replicate the MySQL client databases between the servers and Unison will be used to Sync the /var/www (websites) and /var/vmail (email account data) folders.

 

1 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. The base setup is described in the following tutorial in the steps 1 - 8:

http://www.howtoforge.com/perfect-server-debian-squeeze-with-bind-and-dovecot-ispconfig-3

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.

 

2 Installing The Two Servers

The following steps have to be executed on the master and on the slave server. If a specific step is only for the master or slave, then I've added a note in the description in red.

vi /etc/hosts

127.0.0.1       localhost
192.168.0.105   server1.example.tld
192.168.0.106   server2.example.tld

# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
ff02::3 ip6-allhosts

Set the hostname of the server:

echo server1.example.tld > /etc/hostname
/etc/init.d/hostname.sh start

Use server1.example.tld on the first server and server2.example.tld on the second server.

Edit the sources.list file...

vi /etc/apt/sources.list

... and ensure that your /etc/apt/sources.list contains the squeeze-updates repository (this makes sure you always get the newest updates for the ClamAV virus scanner - this project publishes releases very often, and sometimes old versions stop working).

[...]
deb http://ftp.de.debian.org/debian/ squeeze-updates main
[...]

Run

apt-get update
apt-get upgrade

to install the latest updates (if there are any).

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.

Share this page:

35 Comment(s)

Add comment

Comments

From: at: 2012-02-19 10:57:56

I can't get the MySQL sync to work.
After scp command and MySQL config edit it fails start on server2. Is there something missing from this tutorial?

From: at: 2012-03-01 09:47:28

The tutorial is complete and works on the servers that I installed. If you need help with your setup, please make a post in the forum which includes the detailed error messages etc.

From: Anonymous at: 2012-11-09 09:56:39

I'm having big-time trouble with Slave_IO_Running: No Why is this happening? The Slave_SQL_Running is Yes.... Help cause I'm stuck

From: poisen at: 2012-04-12 19:09:30

...I have the same problem with the mysql sync, after stopping mysql service and editing /etc/mysql/my.cnf there is no way to restart MySQL again.

 Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!

From: HansB at: 2012-06-09 15:50:23

Works like a charm with older Mysql.

Trying to do it with Mysql5.5+ (from dotnet) and I get the same error as above.

The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these options in MySQL 5.5, the server aborts with an unknown variable error.

–master-host
–master-user
–master-password
–master-port

What way to work around?

From: at: 2012-10-18 05:16:27

I know this works cause I have followed it in the past but I now notice something that is incorrect. 

 Where you have the command: ssh-copy-id -i $HOME/.ssh/id_dsa.pub root@192.168.0.106

Right below that section in the next block is code you obviously copied out of your xterm window.

 All the IP's are different, not hugely so but for anyone walking through this the first time and clueless about what they are doing and what is really going on and just following verbatim.  Small problem I thought you might care to know about and possibly address. I had to verify if I was reading your How-To or notes of my own in one of my wiki's and I don't think it was there before but hard to say, worked for me each time none the less.

hths 

From: Anonymous at: 2012-11-20 22:19:54

On server 2:

Log into the MySQL shell as root user..

You should use : 

/etc/init.d/mysql start (again)

Then you have an error like that:

 

ERROR 29 (HY000): File '/var/lib/mysql/master.info' not found (Errcode: 13 or similar. You should use chmod 777 /var/lib/mysql/master.info .

Then the error dissapear. If you are more serious , you should give the correct priveligies to mysql.

 

From: at: 2013-03-20 23:00:05

Hi guys

im new to this , is there a setup like this that works with ubuntu 12.04 lts.

i will be thankfull for life.

 

From: Anon at: 2013-09-22 12:04:58

I had problems following this set of instructions organising the base setup and MYSQL config. As I see it it only sets up replication one way. 

 I followed the following instructions to setup master-master and then carried on : 

 http://www.howtoforge.com/mysql5_master_master_replication_debian_etch

From: at: 2012-06-06 19:29:50

  • Create a user calle unison on server2
  • Make a private key on server1 and add it to the authorized list for unison on server2

 

On server2:

  • Add unison to the sudoers with `unison ALL = NOPASSWD: /usr/bin/unison-wrap`
  • mv /usr/bin/unison /usr/bin/unison-wrap
  • vi /usr/bin/unison

#!/bin/bash
/usr/bin/sudo /usr/bin/unison-wrap $*

  • chmod +x /usr/bin/unison

What happens now is that the user unison has no rights really but can get full root rights BUT ONLY FOR unison. Ofcouse you could potentially use unison to inject the full system. But unison itself account has no password and the private key is on server1. So as long as that private key is not been compromised you should be golden. If some one want i can write a full tutorial on setting up unison like this.

From: ColdDoT at: 2012-06-05 19:29:24

Good info
Only thing i find insecure is that you use root accounts for sync...
perhaps make a different user for that?

From: Anonymous at: 2012-12-02 15:19:21

thank you for this tutorial, it would be very nice when it works but after 6 tries with fresh installation of slave i give it up,

i think it is not working when the master server still is running  (3.0.4.6)

the SHOW SLAVE STATUS \G gives error, which can be solved with

replicate-ignore-table=dbispconfig.sys_datalog
replicate-ignore-table=dbispconfig.monitor_data 

in /etc/mysql/my.cnf

but after all, i see no choice in the "server is mirror of" LIST  in ispconfig

the databases on the slaves are all without any tables

the mirroring of www and vmail with unison, dont work

i tried very hard and concentrated and 1:1 like in this tutorial but now i am frustrated and give up

From: Anonymous at: 2013-01-09 15:43:59

ISPConfig create a new system user for new client. The new user, is used to applied permission to access publish directory.

How I can sync the files (/etc/passwd, /etc/group and /etc/shadow)?

 Will I use rsync or unison to this...

From: Giulio PR at: 2014-06-14 01:46:49

 I think so.

Also as I'm mostly interested in apache2, what about /etc/apache2? What's the point in having the mysql synched if the slave server doesn't know anything about the newly created site?

From: admin at: 2014-06-14 19:32:26

Everything related to the Website, database, mailuser and other configuration is synced by ispconfig automatically. No Need to install any other Software then the sftware described in the guide to get a fully replicated cluster.

From: admin at: 2014-06-14 19:30:50

Thats all synced by ispconfig automatically.

From: at: 2014-02-11 22:52:34

Just a quick comment on using crontab to launch unison, we are using it to sync emails between servers and started with a large var/vmail folder (20GB). During the initial sync unison was launched numerous times as a cron job and I believe each instance tried to sync the whole var/vmail folder structure which caused everything to grind to a halt and was eventually fixed by a reboot.
 
Anyway to avoid multiple instances of unison running again in the future I simply wrote the following bash script to check if unison was running and if it was simply. I then called this script from crontab rather than unison directly. Hope it helps someone.
 
if [ "$(pidof unison)" ]
then
echo "Unison Already Running"
else
echo "Launching Unison"
/usr/bin/unison
echo "Unison Finished"
fi

From: Richard Ablewhite at: 2014-06-04 02:53:49

On page 3 the guide adds unison into cron to run every 5 minutes. 

This is potentially dangerous as if the task takes more than 5 mins to complete then multiple copies of unison run simultaneously and will consume large amounts of bandwidth and memory. This is particularly dangerous as unison will then run multiple instances on both cluster nodes, I've seen this bring down clusters.

A quick fix is to add the following to cron instead, ensuring unison does start if the process is already running:

*/5 * * * * pgrep unison || unison

From: Benjamin at: 2013-05-17 15:06:12

I don't understand the last step.

For which reason we would use it ?

The SCP command copy the config of master to slave, in order to slave server use the database of master... but what is the advantage, because databases of both server will be synchronized.

And when the master server will failed, its database will not be able to used by slave server... that's not a problem ? 

I have used the port 8080 for both servers, I hope I can use my servers without that..

 Thank you for response..

 Benjamin

From: at: 2013-08-11 13:31:17

in the file you can read: $conf['db_host'] = 'localhost'; so even if you copy the file, each ispconfig server will connect to it's own database. if you don't use the same database, you'll always have 2 different setup. at this point, I think the dbispconfig2 databases become useless and could be deleted

From: Anonymous at: 2012-02-18 19:03:48

Easy to understand written toutorial - but I miss some points you mentioned in the introduction:

 

This tutorial describes the installation of a clustered web, email, database and DNS server to be used for redundancyhigh availability and load balancing on Debian 6 with the ISPConfig 3...

 

What I miss are postfix (queue) redundancy, amavis (temp + quarantine)redundancy as well as handling logfiles (if complete logfiles to be archived for some month -> Vorratsdatenspeicherung). Just copying /vmail from server 1 to server 2 isn't redundancy, the loss of mails is possible.

How to switch between services (or servers) if one goes down or isn't reachable and how is load balancing solved?

just my 2 cents
Alois

From: nitesh at: 2014-08-28 14:03:08

Hello,

 I also read that tutorial and this is too good. But i just want to ask if my master server goes down then how can  i run services on server 2 so my webistes will not go down.

 Please update on this.

From: at: 2012-03-16 18:57:48

 Hello, What user and password i need to ISPConfig control panel login ?

From: at: 2012-03-27 06:06:43

The default ISPConfig login is:

Username: admin
Password: admin

From: at: 2012-04-12 08:06:47

How IspConfig 3 manage the load balancing apache clusters sessions?

From: qqkk at: 2012-04-29 15:15:17

Then open System > Server Config and enable the checkbox "Connect Linux userid to webid" on the "Web" tab:

this config for server1 or server2??

From: at: 2012-06-27 10:48:47

I've done on both servers and works correctly

From: Shelby99 at: 2012-05-11 15:49:13

Hi

I can't login on default admin admin account. Every time i try on both servers i get this error:

ERROR

Username or Password wrong.1

Password lost?
 

 Anybody know what to do?

From: Ariel at: 2012-10-18 17:25:01

Same incident here.

 I've used these queries to the DB:

 mysql> select username, passwort, md5('admin')  from sys_user\G

*************************** 1. row ***************************

    username: admin

    passwort: 21232f297a57a5a743894a0e4a801fc3

md5('admin'): 21232f297a57a5a743894a0e4a801fc3

1 row in set (0.00 sec)

So, the password for 'admin' user is 'admin' but the interface does not allow me to log on.

From: at: 2012-11-01 03:19:23

Get the password again:

Code:
grep -i db_password /usr/local/ispconfig/interface/lib/config.inc.php
Set the password:
Code:
mysql -u root -p
mysql> update mysql.user set password=PASSWORD("<PASSWORD>") where User='ispconfig';
mysql> flush privileges;
mysql> quit

From: Anonymous at: 2012-06-19 13:28:14

Hello! First of thank your very much for this nice tut. I also solved the problem regarding to the login problem for the ISPconfig web interface. 

Only one thing I left: The configuration at the end "Connect Linux userid to webid". Do I have to set this configuration for both servers?

 Thank you, mic.

From: berry at: 2013-02-17 19:55:11

hello, thank you very much unbelievable

From: at: 2013-04-11 10:03:58

Same question: How IspConfig 3 manage the load balancing and HA?

From: Djerk at: 2015-03-25 14:59:28

For those using unison between differing OS releases. You can update unison by adding a repository to the older OS to bring both versions in sync.

Now for two questions:

1) unison fails to sync two folder. Something about not having permission. I looked in the destination folder and there are a number of hidden unison files there that I can't remove even as root. Will have to keep digging to find an answer for that

2) I don't use * in the IP field (eith v4 or v6). is this a prerequisite for multiserver setups? Or can we somehow link the IPs from the various servers to specific accounts?

From: Djerk at: 2015-03-26 18:49:17

Unison sync sorted, turns out I needed to learn about the immutable attribute. Lesson learned...

One thing I've noticed is that website quota usage isn't displayed anymore. The page is just empty. Is this a known bug or due to the master being Ubuntu 10.04 LTS and the slave being 14.04 LTS?