MySQL SSL Master-Master Replication on Ubuntu 18.04 with PrivacyIDEA 3.0.1

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. :scream:

PrivacyIDEA Servers
192.168.1.198 - MFA1
192.168.1.199 - MFA2

Perform On Both Servers

  1. Install the privacyidea-apache2 package.
  2. Remove pi database.
mysql -u root -p
drop database pi;
  1. Mirror pi.cfg config settings
    i. Copy secret_key and pi_pepper values from MFA1 to MFA2
    ii. Add the following to the end of the SQLALCHEMY_DATABASE_URI connection 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

  1. Run MySQL SSL script wizard
    mysql_ssl_rsa_setup –uid=mysql

  2. Edit global MySQL config file
    nano /etc/mysql/my.cnf

[mysqld]
require_secure_transport = ON
bind-address		= 0.0.0.0
  1. Configure replication in MySQL config file
    nano /etc/mysql/mysql.conf.d/mysqld.cnf
    i. In the Logging and Replication section, 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
  1. Restart MySQL
    systemctl restart mysql

  2. 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

  1. Run MySQL SSL script wizard
    mysql_ssl_rsa_setup –uid=mysql

  2. Edit global MySQL config file
    nano /etc/mysql/my.cnf

[mysqld]
require_secure_transport = ON
bind-address		= 0.0.0.0
  1. Configure replication in MySQL config file
    nano /etc/mysql/mysql.conf.d/mysqld.cnf
    i. In the Logging and Replication section, 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
  1. Restart MySQL
    systemctl restart mysql

  2. 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;
  1. 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;
  1. Get master info on MFA2
    show master status;

MFA1

  1. 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.

  1. Log into MySQL
    mysql -u root -p

  2. Create pi database
    create database pi;

  3. Create PI tables in pi database
    pi-manage createdb

  4. 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.

  1. mysql -u root -p
  2. show variables like '%ssl%';

If have_openssl and have_ssl do not say YES, check the following:

  1. Run ls -l /var/lib/mysql to verify ownership of the pem files.
  2. If the files are not owned by mysql, execute chown mysql:mysql /var/lib/mysql/*.pem
  3. Restart Mysql, systemctl restart mysql
  4. Verify SSL is now enabled in mysql
    a. mysql -u root -p
    b. show variables like '%ssl%';

Thanks for the detailed HowTo.
There is also an older HowTo for a MySQL Multi-Master-Setup here: Ubuntu 16.04 - privacyIDEA High Availability using MySQL Active/Active
You could avoid removing the database after the initial install on the second node by installing the privacyidea package which just contains the application. Of course the apache setup needs to be done ‘by-hand’ in this case.

1 Like

Ya, I skimmed through that guide. Other than it being an older version, with possibly different procedures than the latest LTS, it appeared to be a solution for creating groups or clusters. This requires an odd number of servers to maintain cluster quorum (unless you can fake it with a share witness on server 1 or 2). That’s great for organizations the need/want the ability to scale out like that. For smaller organizations though, I think this might be a better fit.

After some sleep I thought, it may be possible to take an existing single node, setup master-slave replication from existing node to new node, then once replication is complete, setup the other replication link from new node to existing node to complete the master-master replication. However, PrivacyIDEA is my first dive into MySQL, or any DB application really, so I could be completely wrong.

@plettich,
My guide assumed that one of the two servers would always be online, so if both should happen to go offline simultaneously you have to manually log in and “re-bootstrap” the cluster.
This gets around that by setting up two master/slave relationships rather than multi-master clustering like I did.
It also gets around having to remove the built-in MySQL and install one from the MySQL repository since the one distributed by Ubuntu doesn’t have multi-master enabled.
One potential improvement to this guide could be to backup the pi database rather than drop it, then import it again once both servers are online. That way you don’t have to use any of the PI utils to rebuild the database (and you preserve your data if you are already using privacyIdea). My instructions include a backup and restore as part of the process.

1 Like

It’s funny you mention the two master-slave connections. I went looking for guides all over the internet for setting up master-master replication and they all seemed to use this method, which I thought was really weird, like MySQL doesn’t support master-master natively, and this was a kind of hack to get it implemented in some form. Or maybe this is how’s it’s done and wizards/GUIs on other things I’ve worked on has just obscured this.

I agree, having a method to migrate from a single node production server to multi-node HA would be great. I may work on improving this guide to include this information.

When I did a similar search, I ended up finding this guide to general group replication https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04
However, as the guide says, this requires a version of MySQL downloaded from the MySQL repository rather than the standard Ubuntu version of MySQL. This may have been part of the reason for the lack of guides when you were doing your searching. If you have to actually uninstall and re-install a different version of MySQL that may take things a bit farther than a lot of the newer Linux admins are comfortable with… :slight_smile:

1 Like