Configure a redundant MySQL database with master-master-replication

3.6.2. 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 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/