Configure a redundant mariadb database with master-master-replication

3.6.1. Configure a redundant mariadb 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 and linotp-uwsgi service on both machines:

systemctl stop apache2.service linotp-uwsgi.service

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

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

The DB 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 respectively their includes 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 (linotpserver) we need to set up a replication user with password and access rights for the other site:

root@linotpserver1:~# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is...

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
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is...

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 = linotp
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = linotp
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 = linotp
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = linotp
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

mysql> use linotp;
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 |  linotp      |                  |
+------------------+----------+--------------+------------------+
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 -u root

mysql> use linotp;
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 | linotp       |                  |
+------------------+----------+--------------+------------------+
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 http POST request against the api endpoint /system/setConfig with the following data:

linotp.enableReplication=true

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

Tip

Detailed information about the handling calls against the administrative api endpoints can be found at Usage of the administrative API.

Tip

The comprehensive API documentation can be found here: https://www.linotp.org/doc/api/