Instructions built using information from How To Configure MySQL Group Replication on Ubuntu 16.04 | DigitalOcean
These instructions use Group Replication which is Active/Active (all servers accept writes and replicate changes to all other servers). There are other replication methods for MySQL that can be found by Googling.
Ubuntu runs a version of MySQL that has group replication removed by default. To fix this, install the MySQL package distributed through dev.mysql.com:
- Go to http://dev.mysql.com/downloads/repo/apt/ and click the download button. Either copy the file that is downloaded to the ubuntu server (using winscp) or copy the address, connect to the privacyIDEA server, and type wget URL (replacing URL with the location of the file, something like https://dev.mysql.com/get/mysql-apt-config_0.8.8-1_all.deb) and press enter to download the file directly to the server.
- Once it is downloaded, run sudo dpkg -i downloaded_file_name and press enter.
- Leave the defaults, select OK and press enter.
- Run sudo apt-get update to update the package information.
- Run sudo apt-get install mysql-server to install the updated mysql (you should see references to mysql-community server in the installation prompt.
While installing, select N to keep the old config file.
Build a second server with privacyIDEA installed. Install the updated MySQL from the above instructions.
On the primary server run the following steps to make a backup of the privacyIDEA server. MySQL on Ubuntu doesn’t setup the root password properly, so these steps cannot be taken before upgrading MySQL.
cd ~
mkdir mysqlbackup
cd mysqlbackup
sudo mysqldump -u root -p --set-gtid-purged=off pi > privacyideabackup.sql
On either server generate a unique identifier for the group by running the command uuidgen. It will produce something that looks like this 5c12534b-48f0-45e2-b86d-e0b22c3efbf4
Save the key it generates.
On each server, edit the MySQL config file - /etc/mysql/my.cnf. Move to the bottom of the file and paste the following block of text
[mysqld]# General replication settings
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
transaction_write_set_extraction = XXHASH64
loose-group_replication_bootstrap_group = OFF
loose-group_replication_start_on_boot = OFF
loose-group_replication_ssl_mode = REQUIRED
loose-group_replication_recovery_use_ssl = 1# Shared replication group configuration
loose-group_replication_group_name = “<REPNAME>”
# List MySQL IP addresses below
loose-group_replication_ip_whitelist = “<Server IPs>”
# List MySQL IP addresses with associated ports
loose-group_replication_group_seeds = “<Server IPs:Ports>”# Single or Multi-primary mode? Uncomment these two lines
# for multi-primary mode, where any host can accept writes
loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON# Host specific replication configuration
server_id = <ID>
bind-address = “<ADDRESS>”
report_host = “<ADDRESS>”
loose-group_replication_local_address = “<LOCALADDRESS>”
Replace the following values:
<Server IPs> - A comma-separated list of servers that will be members of this replication group. (For example: 10.5.2.3,10.5.2.4,10.5.2.5)
<Server IPs:Ports> - A comma-separated list of servers and the port that each will use for communication. (For example: 10.5.2.3:33061,10.5.2.4:33062,10.5.2.5:33061)
<ID> a unique ID for the server (can be 1,2,3,etc)
<ADDRESS> current server IP (in both places)
<LOCALADDRESS> the IP address of the current server followed by the port included with it in the <Server IPs:Ports> list above.
On each server save and close the file, then restart mysql and open the correct firewall ports:
sudo service mysql restart
sudo ufw allow 33061
sudo ufw allow 3306
On each server open an interactive MySQL session with the command sudo mysql -u root -p
At the MySQL prompt run the following commands (pick an intelligent password to replace the question mark, max length 32 characters):
SET SQL_LOG_BIN=0;
CREATE USER ‘repl’@‘%’ IDENTIFIED BY ‘?’ REQUIRE SSL;
GRANT REPLICATION SLAVE ON . TO ‘repl’@‘%’;
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER=‘repl’, MASTER_PASSWORD=‘?’ FOR CHANNEL ‘group_replication_recovery’;
INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;
At this point, all servers should be configured and ready to go. Bootstrap the cluster by running the following command at the mysql prompt on one of the servers. This set of commands tells the first server that it should not look for other servers, but should start the cluster and take control:
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
Once that command is finished, run this command on all other servers:
START GROUP_REPLICATION
To check the status of the cluster, run this query at a mysql prompt. All servers should say online. If any server has a status of recovering for more than a few seconds it is probably an indicator of a problem with that server.
SELECT * FROM performance_schema.replication_group_members;
On each server, edit /etc/privacyidea/pi.cfg and copy the PI_PEPPER and SECRET_KEY values from the first server to the second server. This will ensure that privacyIDEA can properly hash passwords on both servers.
On each server, before closing the pi.cfg file, change the SQLALCHEMY_DATABASE_URI value to add the unix socket to the end of the command. Do not modify the password for the PI user, because it is different between the different servers.
Before:
mysql://pi:FFPPPSSS1234@localhost/pi’
After:
mysql://pi:FFPPPSSS1234@localhost/pi?unix_socket=/var/run/mysqld/mysqld.sock’
On the primary server, copy /etc/privacyidea/enckey to the same location on each server. The following command will copy the file. It should be run for each server:
sudo scp /etc/privacyidea/enckey USERNAME@SERVERIP:
# Log into the other server.
sudo cp ~/enckey /etc/privacyidea
On the primary server restore the privacyIDEA backup. This is a multi-step process because the privacyIDEA database is missing the primary key on one table.
- Open the previously-made backup file privacyideabackup.sql (if you have been following these steps directly, it should be in the same folder you are currently in).
- Find the following block of text (probably around line 50 or so)
CREATE TABLE `alembic_version` (
`version_num` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
- Add a comma to the end of the
version_numline and add a new Primary Key line. (See below)
CREATE TABLE `alembic_version` (
`version_num` varchar(32) NOT NULL,
PRIMARY KEY(`version_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
- Save and close the file, then restore the backup by running mysql -u root -p pi < privacyideabackup.sql
As a final step, open /etc/mysql/my.cnf and change the line loose-group_replication_start_on_boot = OFF to ON. This setting will bring the MySQL server back online as a group member should the server be rebooted. Important: If the cluster is down, this setting will make MySQL take a VERY long time start. Turn this setting off when bootstrapping a cluster.
At this point, you should be able to log into either server with the same username and password and make changes, which will be immediately reflected on the other server. The process is complete!