Configure a redundant MySQL database with master-master-replication
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This installation example is based on two LinOTP-servers both installed on a debian
or ubuntu system with the LinOTP packages.
One server is called linotpserver1 and the other one linotpserver2.

Introduction
.............

When setting up the master master replication, you should stop the Apache web server on both machines::

   /etc/init.d/apache2 stop

The encryption key must be the same on both machines, so copy one to the other::

   root@linotpserver1:~# scp /etc/linotp2/encKey root@linotpserver2:/etc/linotp2/

The MySQL server needs to listen on the external IP and not only on localhost,
so you need to comment the bind-address in ``/etc/mysql/my.conf`` on both machines::

   #
   # 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

Restart the MySQL servers and check with ``netstat``::

   netstat -tulpen
   tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      103        4722        973/mysqld

Replication user
................

On both machines, we need to setup a replication user::

   root@linotpserver1:~# mysql -u root -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 38
   Server version: 5.1.41-3ubuntu12.9 (Ubuntu)

   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

   mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl2_user'@'linotpserver2' IDENTIFIED BY 'secretPW';
   Query OK, 0 rows affected (0.00 sec)

   mysql> FLUSH PRIVILEGES;
   Query OK, 0 rows affected (0.00 sec)

   mysql> quit;

Do the same on linotpserver2::

   root@linotpserver2:~# mysql -u root -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 40
   Server version: 5.1.41-3ubuntu12.9 (Ubuntu)

   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

   mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl1_user'@'linotpserver1' IDENTIFIED BY 'secretPW';
   Query OK, 0 rows affected (0.00 sec)

   mysql> FLUSH PRIVILEGES;
   Query OK, 0 rows affected (0.00 sec)

   mysql> quit;

Configure replication
......................

Now we need to configure the replication. For this we edit the file ``/etc/mysql/my.conf``
again and add the following values to the section ``[mysqld]``.

On linotpserver1::

   # replication
   server-id = 1
   replicate-same-server-id = 0
   auto-increment-increment = 2
   auto-increment-offset = 1
   master-host = linotpserver2
   master-user = repl1_user
   master-password = secretPW
   master-connect-retry = 60
   replicate-do-db = LinOTP2
   log-bin = /var/log/mysql/mysql-bin.log
   binlog-do-db = LinOTP2
   relay-log = /var/lib/mysql/slave-relay.log
   relay-log-index = /var/lib/mysql/slave-relay-log.index

On linotpserver2::

   # replication
   server-id = 2
   replicate-same-server-id = 0
   auto-increment-increment = 2
   auto-increment-offset = 2
   master-host = linotpserver1
   master-user = repl2_user
   master-password = secretPW
   master-connect-retry = 60
   replicate-do-db = LinOTP2
   log-bin = /var/log/mysql/mysql-bin.log
   binlog-do-db = LinOTP2
   relay-log = /var/lib/mysql/slave-relay.log
   relay-log-index = /var/lib/mysql/slave-relay-log.index

The ``auto increment increment`` is set to 2, as we have two servers. Each server starts its autoincrement with a
different offset.

If you have already a filled database, you should lock the old database and dump the old,
existing database and import it to the new, second database (e.g. copy the contents of the old
database to the new replication).

If this is a new installation, we can go on like this.

Setup slaves
............

On linotpserver1::

   root@linotpserver1:~# mysql -u root -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 48
   Server version: 5.1.41-3ubuntu12.9-log (Ubuntu)

   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

   mysql> use LinOTP2;
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A

   Database changed
   mysql> FLUSH TABLES WITH READ LOCK;
   Query OK, 0 rows affected (0.04 sec)

   mysql> SHOW MASTER STATUS;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000001 |      106 | LinOTP2      |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)

   mysql> UNLOCK TABLES;

.. note:: You need to remember the "File" and the "Position" for later use.

On linotpserver2::

   root@linotpserver2:~# mysql -p -u root
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 50
   Server version: 5.1.41-3ubuntu12.9-log (Ubuntu)

   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

   mysql> use LinOTP2;
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A

   Database changed
   mysql> FLUSH TABLES WITH READ LOCK;
   Query OK, 0 rows affected (0.00 sec)

   mysql> SHOW MASTER STATUS;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000001 |      106 | LinOTP2      |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)

   mysql> UNLOCK TABLES;
   Query OK, 0 rows affected (0.00 sec)

Also remember this "File" and "Position".

Synching the machines
.....................

Now we setup the slave on both machines and tell them, where their master is at the moment.

On linotpserver1 we do::

   mysql> stop slave;
   Query OK, 0 rows affected (0.00 sec)

   mysql> change master to master_host='linotpserver2', master_user='repl1_user', master_password='secretPW',
   master_log_file='mysql-bin.000001', master_log_pos=106;
   Query OK, 0 rows affected (0.22 sec)

   mysql> start slave;
   Query OK, 0 rows affected (0.00 sec)

The ``master_log_file`` and the ``master_log_pos`` are the values from linotpserver2!

On linotpserver2 we do::

   mysql> STOP SLAVE;
   mysql> change master to master_host='linotpserver1', master_user='repl2_user', master_password='secretPW'
   master_log_file='mysql-bin.000001', master_log_pos=106;
   mysql> START SLAVE;

The ``master_log_file`` and the ``master_log_pos`` are the values from linotpserver1!

You may check the status with::

   mysql> show slave status;

If an error is displayed check the ``/var/log/syslog`` and ``/var/log/mysql/error.log`` for more details.

Activate the Replication Mode in LinOTP
.......................................

After the setup the replication awareness of LinOTP should be activated. This
will improve the experience in /manage by deactivating some caching features.
If this is not configured changes on LinOTP server 1 will be shown
significantly delayed on LinOTP server 2.

The feature is activated via an API call. First of all log in to the /manage
interface in order to extract the session cookie. Then perform the following
call in your browser:

.. code::

 https://<LINOTP>/system/setConfig?linotp.enableReplication=true&session=<COOKIE>

This configuration will be replicated to the other LinOTP server automatically.

.. tip::
 Detailed information about the handling of the session cookie can be found at
  :ref:`system_controller`

.. tip::
 The comprehensive API documentation can be found here:

 https://www.linotp.org/doc/api/