View source for Template:OXLoadBalancingClustering Database

== Overview ==

You can choose between Galera or two-sided Master/Slave ("Master/Master") replication.

== Galera database setup ==

OX only supports the "Percona XtraDB Cluster" flavor of the Galera database.

=== Installation ===

== Debian systems ==

The following has been tested with Squeeze, but the Wheezy procedure should not be different, besides the repo paths need adjustments.

To install the software, we first need to configure the repository and its build key, update our sources lists and install the packages:

 gpg --keyserver  hkp://keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
 gpg -a --export CD2EFD2A | apt-key add -
 
 cat >/etc/apt/sources.list.d/percona.list <<EOF
 deb http://repo.percona.com/apt squeeze main
 deb-src http://repo.percona.com/apt squeeze main
 EOF
 
 apt-get update
 apt-get install percona-xtradb-cluster-client-5.5 percona-xtradb-cluster-server-5.5 percona-xtrabackup

== RHEL 6 systems ==

Should also apply to CentOS 6.

Percona XtraDB Cluster relies on ''socat'' which is not shipped by RHEL. We need to install from a different source. It is convenient to use the rpmforge repo. (''epel'' works not since its socat expects readline libraries version 5, while RHEL 6 (at leat 6.4) provides version 6.)

The installation command itself needs to be a composite ''remove'', ''install'' command since yum is not clever enough to resolve the conflicts itself, so we need to tell it how.

 wget wget http://pkgs.repoforge.org/rpmforge-release/rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm
 yum localinstall rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm
 
 wget http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
 yum localinstall percona-release-0.0-1.x86_64.rpm
 
 yum shell
 remove mysql-libs
 install Percona-XtraDB-Cluster-server Percona-XtraDB-Cluster-client
 run
 quit

=== Configuration ===

==== ''my.cnf'' configuration file ====

Galera needs also a ''my.cnf'' configuration file. But the Percona packages don't ship any; on purpose: https://bugs.launchpad.net/percona-server/+bug/673844

Usually MySQL expects this file to be located at ''/etc/mysql/my.cnf''.

Open-Xchange cannot provide sample ''my.cnf'' files here. You need to obtain / install / create one on your own. Make sure it has no settings which are forbidden for Galera. This includes the ''query_cache'' (it must not be enabled with Galera) and probably other settings which would contradict the settings explained in the next section.

Make sure you apply standard tunings for your memory size, number of allowed connections, and stuff.

We assume in the following that the ''my.cnf'' file has a directive like ''!includedir /etc/mysql/conf.d'', such that you can put additional config files ending with ''.cnf'' there.

==== ''wsrep.cnf'' configuration file ====

The Galera configuration then happens in a section called "wsrep", "write set replication", which is the internal name for the replication mechanism Galera is based on. A sample ''/etc/mysql/wsrep.cnf'' file looks like:

 [mysqld]
 # the following lines are required for galera:
 binlog_format=ROW
 default-storage-engine=innodb
 innodb_autoinc_lock_mode=2
 innodb_locks_unsafe_for_binlog=1
 query_cache_size=0
 query_cache_type=0
 bind-address=0.0.0.0
 wsrep_provider=/usr/lib64/libgalera_smm.so
 # the following lines need to be adjusted to your environment
 wsrep_cluster_name="my_wsrep_cluster"
 wsrep_cluster_address="gcomm://<GALERA_NODE1_IP>,<GALERA_NODE2_IP>,<GALERA_NODE3_IP>"
 wsrep_sst_method=xtrabackup
 wsrep_sst_auth=<db_user>:<db_password>
 # It is recommended to run Galera in synchronous mode, which makes it possible
 # to disable the OX builtin database replication monitor.
 # Default is semi-synchronous mode. To enable synchronous mode, use
 wsrep_causal_reads=1

When you adjusted those files, make sure they are identical on all nodes.

The replication user will be created later when the DB is running on the first node.

=== Cluster startup ===

Whenever all nodes of a Galera cluster are not running (like before starting the cluster for the very first time), the first Galera node needs to get started with the ''wsrep_cluster_address'' parameter overridden to the value "gcomm://" in order to denote that the node shall not try to join an existing cluster (which would inevitably fail now, because no other cluster nodes are running yet), but to bootstrap the cluster instead. This override can most conveniently done on the command line, instead of editing to wsrep.cnf file to and fro.

So, for the first node, the startup command is

 mysqld_safe --wsrep_cluster_address=gcomm:// &

You should then verify the Galera module is loaded properly using

 mysql -e "show status like 'wsrep%';"

You should verify some settings like

 | wsrep_local_state_comment  | Synced                               |
 | wsrep_cluster_size         | 1                                    |
 | wsrep_cluster_status       | Primary                              |
 | wsrep_connected            | ON                                   |
 | wsrep_provider_name        | Galera                               |
 | wsrep_provider_vendor      | Codership Oy <info@codership.com>    |
 | wsrep_provider_version     | 2.8(r162)                            |
 | wsrep_ready                | ON                                   |

Now you need to create the database user for the replication on this first node:

 # create wsrep user: in mysql shell:
 CREATE USER 'wsrep'@'localhost' IDENTIFIED BY '5ojijmedUg8';
 GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'wsrep'@'localhost';
 FLUSH PRIVILEGES;

The Galera peers can then be started on the nodes 2 and 3 using

 mysqld_safe &

Since the standard service startup scripts cannot account for this special treatment, we recomment not to use them.

You can check the status of the Galera cluster using

 mysql -e "show status like 'wsrep%';"

The output is lengthy. The most relevant fields are given as follows:

 +----------------------------+----------------------------------------------------------------------+
 | Variable_name              | Value                                                                |
 +----------------------------+----------------------------------------------------------------------+
 | wsrep_local_state_comment  | Synced                                                               |
 | wsrep_incoming_addresses   | <GALERA_NODE1_IP>:3306,<GALERA_NODE2_IP>:3306,<GALERA_NODE3_IP>:3306 |
 | wsrep_cluster_size         | 3                                                                    |
 | wsrep_cluster_status       | Primary                                                              |
 | wsrep_connected            | ON                                                                   |
 | wsrep_ready                | ON                                                                   |
 +----------------------------+----------------------------------------------------------------------+

=== Notes about configuring OX for use with Galera ===

Open-Xchange supports Galera as database backend only in the configuration where all writes are directed to one Galera node. For availability, it makes sense to configure a floating IP as write IP for OX, which can be transferred from one Galera to another Galera node if needed. We recommend putting the control of such a floating IP under the control of some HA cluster software like pacemaker/corosync.

Read requests may be distributed aribtrarily between the Galera nodes. You require a load balancer for this. See next section.

The wsrep_causal_reads=1 option enables you to configure OX with its replication monitor disabled (com.openexchange.database.replicationMonitor=false in configdb.properties). This seems to be the most robust and performant configuration in our tests.

However, this is not the only valid configuration. You can use wsrep_causal_reads=0 if you enable the replication monitor, or if you ony read from the same one node which also gets the write requests. Such a configuration may make sense if you use Galera only for (off-site) replication, but not for read scale-out.

=== Loadbalancer options ===

While the JDBC driver has some round-robin load balancing capabilities built-in, we don't recommend it for production use since it lacks possibilities to check the Galera nodes health states.

For most productions setup customers use enterprise-grade loadbalancing appliances. Those should get configured to check node availability not only on the TCP level, but to query the Galera sync status periodically. For an example of such an health check, see the our documentation for setting up a software loadbalancer using keepalived (next paragraph).

For testing purposes it is possible to use Keepalived to create a software loadbalancer on a therefore dedicated linux node. Documentation is available [[Keepalived|here]].

== Master/Master database setup ==
This section describes the setup process "Master/Master replication" for new Open-Xchange  database cluster. During configuration and initialization, other database operations must be prohibited.

The Master/Master replication is a vice versa setup of Master/Slave configurations. This means each server is afterwards the slave of the other. 

Server IPs in the example are 1.1.1.1 and 9.9.9.9

Startup both database machines and install the mysql server packages
 $ apt-get install mysql-server

During the installation, a dialog will show up to set a password for the MySQL 'root' user.

Open the MySQL configuration file on both servers:
 $ vim /etc/mysql/my.cnf

Modify or enable the following configuration options in the mysqld-section, use 1 as ${unique Number} on the server 1.1.1.1 and 2 for 9.9.9.9: 
 bind-address            = 0.0.0.0
 server-id               = ${unique Number}
 log_bin                 = /var/log/mysql/mysql-bin.log
 binlog_format           = statement
 max_allowed_packet      = 16M

* ''bindaddress'' specifies the network address where MySQL is listening for network connections. Since the MySQL slave and both Open-Xchange Servers are dedicated machines it is required to have the master accessible through the network.
* ''server-id'' is just a unique number within a environment with multiple MySQL servers. It needs to be unique for each server in a replication cluster.
* ''log-bin'' enables the MySQL binary log which is required for Master/Master replication. In general every statement triggered at the database is stored there to get distributed through the database cluster.

To apply the configuration changes, restart the MySQL servers.
 $ /etc/init.d/mysql restart

Then login to MySQL with the credentials given at the MySQL installation process
 $ mysql -u root -p
 Enter password:

=== First Master configuration ===
Choose one server to start with as the first Master (here we use 1.1.1.1).

Create a MySQL user with rights "REPLICATION". This account is used by the MySQL slave to fetch database updates. In this example, the username is "replication":

  mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'9.9.9.9' IDENTIFIED BY 'secret';

Verify that the MySQL daemon writes a binary log and note the log Position and File name:
 mysql> SHOW MASTER STATUS;
 +------------------+----------+--------------+------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +------------------+----------+--------------+------------------+
 | mysql-bin.000001 |     1111 |              |                  |
 +------------------+----------+--------------+------------------+

=== First Slave configuration ===

On 9.9.9.9, set the MySQL system user as owner of the binary log that has just been copied to the slave.
 $ chown mysql:adm /var/log/mysql/*

Configure MySQL on 9.9.9.9 to use 1.1.1.1 as Master Server. (Use the actual log File name and Position which you just obtained with the command SHOW MASTER STATUS  on 1.1.1.1. as explained above.)
 mysql> CHANGE MASTER TO MASTER_HOST='1.1.1.1', MASTER_USER='replication', MASTER_PASSWORD='secret', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1111;

Start the MySQL slave replication
 mysql> START SLAVE;

And check the status
 mysql> SHOW SLAVE STATUS\G;

"Slave_IO_Running" and "Slave_SQL_Running" should be set to "yes". Furthermore "Read_Master_Log_Pos" should be counting and "Seconds_Behind_Master" should be approaching the 0 mark.

=== Second Master configuration ===

This means, the first Master/Slave Replication is working and the "reverse" replication needs to be prepared. Please now create the replication user on 9.9.9.9:

  mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'1.1.1.1' IDENTIFIED BY 'secret';

Verify that the MySQL daemon writes a binary log and remember the log Position:
 mysql> SHOW MASTER STATUS;
 +------------------+----------+--------------+------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +------------------+----------+--------------+------------------+
 | mysql-bin.000009 |      9999|              |                  |
 +------------------+----------+--------------+------------------+

=== Second Slave configuration ===

1.1.1.1 is now the slave in this context and 9.9.9.9 is the master. Log in to 1.1.1.1

Configure MySQL on 1.1.1.1 to use 9.9.9.9 as Master Server. Use the remembered log and file position from 1.1.1.1.
 mysql> CHANGE MASTER TO MASTER_HOST='9.9.9.9', MASTER_USER='replication', MASTER_PASSWORD='secret', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=9999;

start the MySQL slave replication
 mysql> START SLAVE;

and check the status
 mysql> SHOW SLAVE STATUS\G;

"Slave_IO_Running" and "Slave_SQL_Running" should be set to "yes". Furthermore  "Read_Master_Log_Pos" should be counting and "Seconds_Behind_Master" should be approaching the 0 mark.

Also check the syslog if the replication has been sucessfully started
 $ tail -fn20 /var/log/syslog
 Jul 26 19:03:45 dbslave mysqld[4718]: 090726 19:03:45 [Note] Slave I/O thread: connected to master 'replication@1.1.1.17:3306',  replication started in log 'mysql-bin.000001' at position 10000

=== Testing Master/Master ===

On 1.1.1.1, create a new database in MySQL:
 mysql> CREATE DATABASE foo;

Verify the database to als be available on 9.9.9.9 afterwards:
 mysql> SHOW DATABASES;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | foo                |
 | mysql              |
 +--------------------+

Delete the new database on 9.9.9.9:
 mysql> DROP DATABASE foo;

Check if the database has also been removed on 1.1.1.1
 mysql> SHOW DATABASES;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 +--------------------+

== Creating Open-Xchange user ==

Now setup access for the Open-Xchange Server database user 'openexchange' to configdb and the oxdb for both groupware server addresses. These databases do not exist yet, but will be created during the Open-Xchange Server installation.

Note: The IPs in this example belong to the two different Open-Xchange Servers, please adjust them accordingly.
 mysql> GRANT ALL PRIVILEGES ON *.* TO 'openexchange'@'10.20.30.213' IDENTIFIED BY 'secret';
 mysql> GRANT ALL PRIVILEGES ON *.* TO 'openexchange'@'10.20.30.215' IDENTIFIED BY 'secret';