hi, all
I am currently using mysql database master-slave synchronous replication to achieve privacyIDEA high availability, but after configuration is complete, the slave server in “Last_Errno: 1062” error, when I was in the slave server ". My CNF “within the configuration file to join the” slave - skip - errors = 1062 "is not an error, but it doesn’t make copies of slave server my PI database, also help please see is where to handle the problem? thanks!
I can’t help you with that error, but if it turns out that a Slave DB isn’t possible w/ PrivacyIDEA, check out the MySQL Galera Cluster. It’s Master-Master, and what I’m using for a HA setup. It works flawlessly.
hi ,bnort81
Thank you for your reply
I changed it to MySQL master-master copy and now both masters are reporting the same error “error 1062” saying that write_rows can’t be executed on the database table on pi.resolver with an error message, and then in the database fixing the error by executing the “set global sql_slave_skip_counter=10” command,However, the TOTP account created by one privaidea server does not work on the other privaidea server, so are some encrypted data tables on the two databases not replicable? thank
Did you copy the enckey, pi.cfg, public.prem and private.prem files from the server you first installed PrivacyIDEA on to the slave server? They need to go in the same directory, /etc/privacyidea. If you didn’t, then you are right, there is a decryption problem.
I am a newly installed two privacyIDEA servers, and the configuration replication mode is master-master replication, and enckey, pi.cfg, public.prem and private.prem all have the same installation path. In addition, I would like to change the configuration of “SECRET_KEY” in the pi.cfg file, and change the two servers into the same one, so that the encrypted data can be synchronized?
Is there a way to make both privacyideas highly available for authentication?
Yes, the pi.cfg files need to be identical, including the secret key. The only way I got it to work was to SFTP the enckey file, pi.cfg file and two .pem files from the first server I created to the second server. I initially tried generating those files from the same pi.cfg file, but it didn’t work. It wasn’t until I removed those 4 files from the second server and SFTP’d them from Server 1 to Server 2 that it worked.
Just for reference, my setup is two PI servers and a third server that just has a DB instance. The MySQL instances on those three servers are set up in a Galera cluster (I found that to be more reliable than a MySQL Master-Master setup).
It is no use to set the SECRET_KEY to the same in the pi.cfg file of two servers. The problem should be the error report. Error1062 and error1032 have two errors.
I don’t know if the errors you’re receiving are related to those files or not…but they do need to be the same. If they aren’t related, it still won’t work once those errors are resolved. Have you tried copying those four files from Server 1 to Server 2?
As I said, my setup is different, so I can’t help you with your specific error. I can just tell you that my setup, with a Galera MySQL Cluster and SFTPing those four files from Server 1 to Server 2 works, with changes replicated between the two PI servers regardless of which one the change was made on. In addition, users can authenticate with the same token to both servers.
Thank you very much for your answer.
According to your prompt, I copied the four files pi.cfg, enckey, private.pem, and public.pem on server1 to the second server. I have already implemented two server authentications. Errors such as “error1062” and “error1032” are reported. When I add “slave-skip-errors = 1062, 1032, 1054” to my.cnf configuration file, I can temporarily solve this problem, but I don’t know if I will report it in the future. Error, and I tested two servers. If the token code created on server1 is turned on, when both servers are open, he will only look for server1 instead of server2. I want to implement load balancing. . do you have any good advice?
First, I think you’re going to run into errors with a Master-Slave setup. If the primary goes down, the PI instance on the slave server won’t be able to write to the DB. At least, that’s my understanding of it, but I’m by no means a DBA. That’s why I went with the Galera setup, because it’s a full Multi-Sync, Master-Master setup. Any PI server can write to their local DB, and it gets replicated around the cluster.
Second, to answer your question, you could set up an Apache cluster, with one Virtual IP pointing to the two static IPs of the two servers.
In my case, we have a small number of users (~40), so I wasn’t concerned about load balancing, I wanted redundancy in case a server went down. If you are more concerned about redundancy than load balancing, then it depends on your authentication method.
In our case, we are using PAM on our servers to authenticate. So, I customized PAM so that it does the following:
PAM first passes credentials to Server 1
If it fails, a script is run to check to see if Server 1 is up. If yes, user is denied access. If no, it continues
PAM passes credentials to Server 2
If it fails, a script is run to check to see if either Server 1 or Server 2 is up. If either is, user is denied access. If not, it continues
PAM passes credentials to an LDAP server with no Two Factor.
At that point, credentials will fail because their LDAP password does not include the token they entered. However, PAM runs one final script to email the user and our SysAdmins to let them know the PI servers are down and LDAP only login has been enabled. The assumption is they will try again, but this time only enter their LDAP credentials. The PAM process will start over again, but the final PAM LDAP module will allow them in.
Thank you for your careful answer, I tested the next master-master copy or there will be problems, you said (mysql galera cluster) I have not done, can two pi servers do? Still have to have 3?
Two PI servers are fine, but you’ll need a third server for the DB portion. Galera uses a quorum voting system to determine which group of servers is “primary”. If you have only two servers, and one goes down, the server that is up doesn’t know if it’s primary or not. For all it knows, it was just a network drop between them. In order to prevent split brain, both DBs will shut down. So, with that third DB, if one drops, the other two know they have majority, and will continue to function.
That is my set up…two PI servers, each running their own DB. Then, I have a third server which only has MariaDB installed, and three servers act as one Galera Cluster.
hi,bnort81
I am using a PI system built on Ubuntu, and the system integrates the mysql database itself. It is not good to do HA. It is estimated that it is different from your environment. I built the PI system based on centos7 and can’t succeed. Go to “pi-manage createdb”. This step will be a problem. I have looked at the problem you mentioned on PI. I have encountered this problem. How did you build it based on centos7? You have better configuration documentation or Can the website link be sent to me?
My email: owen.zeng@outlook.com
With my ability, it can’t be configured according to the official link.
https://www.privacyidea.org/two-factor-authentication-with-otp-on-centos-7/
First, sorry for the late reply…for some reason, this thread was only set to notify me when I logged in that a reply had been posted, not email me. So, I just saw this.
To answer your question, the problem I posted was on RHEL 7. On CentOS 7, I had no problems. I still don’t know what the issue was, but I resolved it by using MariaDB Galera 5.5.6 instead of the version I used on CentOS, which was MariaDB 10.3.
I documented my steps as I built it out in test since I knew I’d have to replicate it in Prod eventually. I’m going over it now to make sure there is nothing specific to my network in it, and it’s not confusing. I wrote it only for myself to read, so I didn’t care about being clear on something if I knew I’d understand. I’ll go over it now, clarify anything I think might be vague, and send it to your email.
Thank you very much for your help to me. I have not been able to deal with errors such as “error1062, 1032, 1054” and so on. I want to build it according to your environment and see if it will report an error. Looking forward to your documentation.
I sent it right after I replied. If you did not receive it, please check your spam folder.
Ok, thank you 
