After a day of trial and error at various points, I was able to setup a 2-node PrivacyIDEA cluster using the PrivacyIDEA-apach2 package. This enables HA and secure replication traffic between the nodes. I am, by no means, a DBA or Linux guru, so if some of my terminology is wrong, I apologize.
Imporant Note: This guide uses self-signed certs to establish SSL connections. Production environments should use certificates signed by a trusted CA.
2nd More Important-er Note: This guide assumes you are setting up a new environment. If you already have a production system up and running, you’ll need to deviate, otherwise you’ll end up deleting your PI database. 
PrivacyIDEA Servers
192.168.1.198 - MFA1
192.168.1.199 - MFA2
Perform On Both Servers
- Install the privacyidea-apache2 package.
- Remove pi database.
mysql -u root -p
drop database pi;
- Mirror pi.cfg config settings
i. Copysecret_keyandpi_peppervalues from MFA1 to MFA2
ii. Add the following to the end of theSQLALCHEMY_DATABASE_URIconnection string:
&ssl_key=/var/lib/mysql/client-key.pem&ssl_cert=/var/lib/mysql/client-cert.pem
The full string will look something like
SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://pi:xxx@localhost/pi?charset=utf8&ssl_key=/var/lib/mysql/client-key.pem&ssl_cert=/var/lib/mysql/client-cert.pem'
MFA1
-
Run MySQL SSL script wizard
mysql_ssl_rsa_setup –uid=mysql -
Edit global MySQL config file
nano /etc/mysql/my.cnf
[mysqld]
require_secure_transport = ON
bind-address = 0.0.0.0
- Configure replication in MySQL config file
nano /etc/mysql/mysql.conf.d/mysqld.cnf
i. In theLogging and Replicationsection, add the following:
server-id = 1
log-bin = /var/log/mysql/bin.log
relay-log = /var/log/mysql/relay.log
binlog-do-db = pi
replicate-do-db = pi
auto-increment-offset = 1
-
Restart MySQL
systemctl restart mysql -
Create MySQL user for replication and configure user rights
mysql -u root -p
CREATE USER 'replicator'@'%' IDENTIFIED BY 'USERPASSWORD' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' REQUIRE SSL;
SHOW MASTER STATUS;
- Make note of the filename and log position from the final command’s output.
MFA2
-
Run MySQL SSL script wizard
mysql_ssl_rsa_setup –uid=mysql -
Edit global MySQL config file
nano /etc/mysql/my.cnf
[mysqld]
require_secure_transport = ON
bind-address = 0.0.0.0
- Configure replication in MySQL config file
nano /etc/mysql/mysql.conf.d/mysqld.cnf
i. In theLogging and Replicationsection, add the following:
server-id = 2
log-bin = /var/log/mysql/bin.log
relay-log = /var/log/mysql/relay.log
binlog-do-db = pi
replicate-do-db = pi
auto-increment-offset = 2
-
Restart MySQL
systemctl restart mysql -
Create MySQL user for replication and configure user rights
mysql -u root -p
CREATE USER 'replicator'@'%' IDENTIFIED BY 'USERPASSWORD' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' REQUIRE SSL;
- Create slave connection from MFA2 to MFA1
STOP SLAVE;
change master to master_host='192.168.1.198', master_port=3306, master_user='replicator', master_password='REPLICATORPASSWORD', master_log_file='LOGFILEFROMMFA1', master_log_pos=LOGPOSITIONFROMMFA1, master_ssl=1;
START SLAVE;
- Get master info on MFA2
show master status;
MFA1
- Create slave connection from MFA1 to MFA2
mysql -u root -p
STOP SLAVE;
change master to master_host='192.168.1.199', master_port=3306, master_user='replicator', master_password='REPLICATORPASSWORD', master_log_file='LOGFILEFROMMFA2', master_log_pos=LOGPOSITIONFROMMFA2, master_ssl=1;
START SLAVE;
At this point, replication setup is complete. You can verify connectivity of each slave connection by logging into MySQL and running show slave status \G. If there are connection errors reported, the error log is located under /var/log/mysql/error.log.
Since we deleted the original PrivacyIDEA database, we need to recreate it and populate it with the PI tables. This is a good way to test replication. By creating the database on MFA1 and then running show databases; on MFA2, you can verify MFA1 to MFA2 replication is working. After that, you can run either the createdb command or create a new PI admin on MFA2 to verify MFA2 to MFA1 replication is up and running.
-
Log into MySQL
mysql -u root -p -
Create pi database
create database pi; -
Create PI tables in pi database
pi-manage createdb -
Create PI administrator
pi-manage admin add administrator
Update: I ran through this for another test environment and hit a wall where the slave connections would not work. Running show slave status\G in MySql showed error code 2026. After digging around for a solution, I discovered that the certificates generated by mysql_ssl_rsa_setup –uid=mysql must be owned by the user, mysql.
Check if SSL is enabled. This should happen after running the mysql_ssl_rsa_setup script.
mysql -u root -pshow variables like '%ssl%';
If have_openssl and have_ssl do not say YES, check the following:
- Run
ls -l /var/lib/mysqlto verify ownership of the pem files. - If the files are not owned by mysql, execute
chown mysql:mysql /var/lib/mysql/*.pem - Restart Mysql,
systemctl restart mysql - Verify SSL is now enabled in mysql
a.mysql -u root -p
b.show variables like '%ssl%';
