PrivacyIDEA MySQL Master <-> Slave replication

Howtos
1

Hi,
I’m trying to achieve MySQL Master ↔ Slave database replication. The topology will have one Master and a couple of slave nodes.
Currently, I have a 2-two PrivacyIDEA installation on different sites with different configurations such as LDAP, SMTP, Policies, and Events. All this information is stored in a local database and replication should be applied only for tokens that are assigned for users.

What I’ve done:

1. Set server ID on master DB:
SET GLOBAL server_id = 1;
2. Edit mysql configuration file: /etc/mysql/my.cnf on master DB:

[mysqld] 
server-id=1
bind-address = 0.0.0.0 
log-bin = /var/log/mysql/bin.log 
binlog-do-db = pi 
replicate-do-db = pi

3. Creating a User for Replication

CREATE USER 'repl1'@'%' IDENTIFIED BY '**Password**'; 
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

4. Create a db backup on master DB and restore on slave.

mysqldump -u root pi  > pi.sql
scp pi.sql admin@slave-node:/home/admin
mysql -u root < pi.sql

4. Set server ID on slave DB:
SET GLOBAL server_id = 2;
5. Edit mysql configuration file: /etc/mysql/my.cnf on slave DB:

[mysqld] 
server-id=2
replicate-do-db = pi
replicate-do-table=pi.token
replicate-do-table=pi.tokenowner
replicate-do-table=pi.tokenrealm

6. Clone PI_ENCFILE between 2-two PrivacyIDEA

The issues that I’ve got:
1. When I’m deleting a token on the Master node, the replication failed with an error:

image
image897×118 6.34 KB

I guess that it’s trying to delete some information from pi.tokeninfo table that doest exist on Slave DB.

From my point of view the pi.tokeninfo table should be unique on each node because it contained counts of successful logins on the current auth server.

In case if I also replicate pi.tokeninfo table this error doesn’t occur, but it causes another error - In case if pi.tokeninfo on slave DB - for example count_auth_success: is updated - Replication also failed with an error:

image
image893×102 4.88 KB

Which actually makes sense.

2. This’s an issue with email tokens, the user’s email address also in the Info section (pi.tokeninfo), with means that privacy idea can’t send confirmation code from PrivacyIDEA that using Slave DB, because it hasn’t the email address.
I can set an option Read email address dynamically from user source on each request.
image
But the problem is that it also saves its parameter in pi.tokeninfo table.

image
image807×85 1.74 KB

Thanks in Advance for sharing your experience with DB replication.

2

Thank you for putting a lot of effort in this long post.
You can not run master-slave replication and use the slave for authentication. This is not possible.
You have two possibilities:

A) Run master-master replication or multi-master-replication.

B) Run master-slave replication and do not use the slave. Use it as cold standby.

An authentication will also write to the table token. So you will screw up your database if you replicate the master to the slave. You will e.g. overwrite the token.count and token.failcount.

That is your point of view, but it is wrong. It must be equal on every node.
The idea is not to “share” otp-seeds between different installations and use this copy of a token independently on different “nodes”. But to have one central 2FA auth source. And for this you must sync everything.

But I think you are trying to achieve something different here and simply went in the wrong direction.

So what are you trying to do (without mentioning “databases”)?

3

Thanks for your reply.
I have three sites in a different location.
image
I would like to have one central 2FA auth source (Europe) where all token for all users will be managed by the administrator.

The user should be able to authenticate on any of these three locations by his single TOTP code or EMAIL code confirmation.
For that reason, I decided to go with Master <> Slave replication.

4

Hi @cornelinux,
Thanks for your explanation.
I decided to go with option A (Master ↔ Master) replication.

image
image1136×958 190 KB

SITE 1 /etc/mysql/my.cnf:

[mysqld]
max_allowed_packet=256M
#MYSQL “Got an error reading communication packets” - Server Fault

server-id=1
bind-address = 0.0.0.0
binlog-do-db = pi
replicate-do-db = pi
replicate-do-table=pi.token
replicate-do-table=pi.tokeninfo
replicate-do-table=pi.tokenowner
replicate-do-table=pi.tokenrealm
replicate-same-server-id=0
auto-increment-increment=2
auto-increment-offset=1
relay-log=/var/lib/mysql/serverA-relay-bin
relay-log-index=/var/lib/mysql/serverA-relay-bin.index
log-error=/var/log/mysql/mysql.err
master-info-file=/var/lib/mysql/mysql-master.info
relay-log-info-file=/var/lib/mysql/serverA-relay-log.info
log-bin=/var/lib/mysql/serverA-bin

SITE 2 /etc/mysql/my.cnf:

[mysqld]
max_allowed_packet=256M
#MYSQL “Got an error reading communication packets” - Server Fault

server-id=2
bind-address = 0.0.0.0
binlog-do-db = pi
replicate-do-db = pi
replicate-do-table=pi.token
replicate-do-table=pi.tokeninfo
replicate-do-table=pi.tokenowner
replicate-do-table=pi.tokenrealm
replicate-same-server-id=0
auto-increment-increment=2
auto-increment-offset=1
relay-log=/var/lib/mysql/serverB-relay-bin
relay-log-index=/var/lib/mysql/serverB-relay-bin.index
log-error=/var/log/mysql/mysql.err
master-info-file=/var/lib/mysql/mysql-master.info
relay-log-info-file=/var/lib/mysql/serverB-relay-log.info
log-bin=/var/lib/mysql/serverB-bin

Currently, I do not have any issues.
Thanks

1 Like