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:
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:
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.
But the problem is that it also saves its parameter in pi.tokeninfo table.
Thanks in Advance for sharing your experience with DB replication.