After mysql db repication I cannot login privacy idea web page

[Tevfik Ceydeliler]

Hi,

I try to replicate pi and my user database as masterand slave

After replication, I cannot login my master privacy idea server.I get this error:

Authentication failed. (OperationalError) (2003, "Can't connect to MySQL server on '127.0.0.1' (111)") None None

I dont know which configuration file shoud change to fix it.

Can you help me?
Regards..

[Cornelius Kölbel]

Hello Tevfik,

the configuration file, where the SQL connection is configure, is
pi.cfg.

Usually it is located at /etc/privacyidea/pi.cfg.

See the config file documentation:
http://privacyidea.readthedocs.org/en/latest/installation/system/inifile.html

If you are missing anything, you are welcome to add infos to the docs
here:
https://github.com/privacyidea/privacyidea/blob/master/doc/installation/system/inifile.rst


Kind regards
Cornelius

> --
> You received this message because you are subscribed to the Google
> Groups "privacyidea" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to privacyidea...@googlegroups.com.
> To post to this group, send email to priva...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/privacyidea/58807d92-42ed-431e-9b83-49a191057b57%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

--
Cornelius Kölbel
corneliu...@netknights.it
+49 151 2960 1417

NetKnights GmbH
http://www.netknights.it
Landgraf-Karl-Str. 19, 34131 Kassel, Germany
Tel: +49 561 3166797, Fax: +49 561 3166798

Amtsgericht Kassel, HRB 16405
Geschäftsführer: Cornelius Kölbel

[Tevfik Ceydeliler]

Hi again,

How to replicate if you have two PI server one of master and other is slave?

Specially tokens

Regards

14 Temmuz 2015 Salı 11:05:28 UTC+3 tarihinde Cornelius Kölbel yazdı:

[Cornelius Kölbel]

Hi,
you are running mysql?
Why not replicate master-master?

Kind regards
Cornelius

> https://groups.google.com/d/msgid/privacyidea/9617b95e-3362-4c20-8c94-58d758268678%40googlegroups.com.

[Tevfik Ceydeliler]

Hi,

Yes I use mysql.

I can use master- master replication.  But generally we use master-slave. Thats all.

Is there any procedure for replication?

14 Temmuz 2015 Salı 12:01:17 UTC+3 tarihinde Cornelius Kölbel yazdı:

[Cornelius Kölbel]

Hello Tevfik,

privacyIDEA does not keep any states (except OTP counter) and does not
replicate any data itself.

I assume you run a setup like this:

[privacyIDEA A] [privacyIDEA B]
| |
| |
| |
[ DB 1 ]---<replication>---[ DB 2 ]

So the database is totally transparent and you can do whatever you want
on the database level.
Well, if you are doing Master-Slave replication, you can not use the
Slave for active authentication, since during authentication the OTP
counter is increased in the database. And this information needs to be
replicated to the other server. Otherwise you can use the same OTP value
to authenticate on the other server.

Having said this, you can use any replication scenario on a database you
wish to. Master-Master, Multi-Master, Master-Slave. In case of Master
slave the slave would only be a standby.

Just check the web for your preferred replication setup like
https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication

Anyway, you can run several privacyIDEA instances with one virtual DBMS
like this, given that the DBMS provides the availability by other means:

[privacyIDEA A] [privacyIDEA B]
| |
| |
| |
[ DBMS with high availablity ]


On each privacyIDEA node you need to configure pi.cfg accordingly.

I hope this clarifies things.

Kind regards
Cornelius

> https://groups.google.com/d/msgid/privacyidea/739c203c-9935-4b7b-8227-080a68669488%40googlegroups.com.

[Tevfik Ceydeliler]

Hi,

Problem is;

We have System Center A and Disaster center B

Master PI  is located on Site A

Slave PI is located on Site B

So there is no 3rd location to  that stores shared db as u told. 

Tats why I can use Master-Slave but Slave standby.

How can suppose that PI keeps standby?

14 Temmuz 2015 Salı 14:07:00 UTC+3 tarihinde Cornelius Kölbel yazdı:

[Cornelius Kölbel]

Hi,

you can do it the same way.
Setup a PI node in A that refers to DB master in A.
Setup a PI node in B that refers to DB slave in B.
Both PIs should have same encryption keys! (/etc/privacyidea/enckey and
pi.cfg)

Now do you master-slave-repl on a DATABASE level!

If site A burns down, you can switch to using site B.

Regards
Cornelius

> https://groups.google.com/d/msgid/privacyidea/da4462fe-bfb8-4bb5-a041-db9044d956bc%40googlegroups.com.

[Tom Cole]

We are currently setup Master/Slave with the Slave in our DR data center.  I have had no issues, but I found the easiest way to get it working right was to upgrade to MySQL 5.6 as it uses GTID.  This way I didn't run into duplicate errors, etc. 

[Tevfik Ceydeliler]

Hi Tom,

Can you describe briefly how did you that?

For example, I replicate only pi and radius user databases.

and copy enckey and pi.cfg files to slave PI server

But cant achieve to replicate , I cant see enrolled tokens on slave 

Can you help me?

Regards...


14 Temmuz 2015 Salı 15:42:35 UTC+3 tarihinde Tom Cole yazdı:

[Cornelius Kölbel]

Hi Tevfik,

I am not sure if we are using the same words here.

You need to file-copy this stuff:

/etc/privacyidea/*
Adapt the pi.cfg to point to the DB slave, not the master.

Then copy the necessary parts of RADIUS - probably /etc/freeradius.

You will not need to change these files. Usually the contents of all
these file is fixed.

Then you will need to setup a _replicated_ database.
(I sent a link earlier)
Variable data is only stored in the database - not in files.

So when talking of HA or Replication there have to be taken into account
to things

1. Copy the necessary files
2. setup the replicated database (which is totally independent of
privacyIDEA)

And I have the impression, that you missed 2. ?

Kind regards
Cornelius

> https://groups.google.com/d/msgid/privacyidea/84a320d4-022e-4e5c-af64-f4392bf4a65d%40googlegroups.com.

[Tevfik Ceydeliler]

hi,

Well,

I understand. But. one more question.

My pi.cfg is like this:

import logging

# The realm, where users are allowed to login as administrators

SUPERUSER_REALM = ['super']

# Your database

#SQLALCHEMY_DATABASE_URI = 'sqlite:////etc/privacyidea/data.sqlite'

# This is used to encrypt the auth_token

#SECRET_KEY = 't0p s3cr3t'

# This is used to encrypt the admin passwords

#PI_PEPPER = "Never know..."

# This is used to encrypt the token data and token passwords

PI_ENCFILE = '/etc/privacyidea/enckey'

# This is used to sign the audit log

# This is the dummy base class

#PI_AUDIT_MODULE = 'privacyidea.lib.auditmodules.base'

# This is the default

#PI_AUDIT_MODULE = 'privacyidea.lib.auditmodules.sqlaudit'

# This is used to sign the audit log

PI_AUDIT_KEY_PRIVATE = '/etc/privacyidea/private.pem'

PI_AUDIT_KEY_PUBLIC = '/etc/privacyidea/public.pem'

PI_LOGFILE = '/var/log/privacyidea/privacyidea.log'

PI_LOGLEVEL = logging.INFO

PI_PEPPER = 'uAIujqnTFRbkQ00TZ_9Kj6gW'

SECRET_KEY = 's_LnCyQuADzUZVSEBxoJdNWo'

SQLALCHEMY_DATABASE_URI = 'mysql://pi:wZmTUD0G_F6d@localhost/pi'

As u see, there is paramater or configs point server IP or hostname.

So if I copy all files under /etc/privacyidea, pi.cfg shows exact configs. right?

14 Temmuz 2015 Salı 17:56:29 UTC+3 tarihinde Cornelius Kölbel yazdı:

[Cornelius Kölbel]

Hm,

you need to make sure, that SQLALCHEMY_DATABASE_URI works on master-PI
and slave-PI.

If on both systems, the database is on localhost and accessable with the
given username and password, you do not need to change pi.cfg and you
can have the same file on both systems.

Kind regards
Cornelius

> > > > 14 Temmuz 2015 Salı 12:01:17 UTC

> https://groups.google.com/d/msgid/privacyidea/66781460-9b4c-44d7-935e-b604d1812fc8%40googlegroups.com.

[Tevfik Ceydeliler]

Hi,

I try this,

But I get error during login in webui.

Authentication failed. (OperationalError) unable to open database file None None
then I copy SQLALCHEMY_DATABASE_URI  from old slave pi.cfg

but not works

SQLALCHEMY_DATABASE_URI = 'mysql://pi:wZmTUD0G_F6d@localhost/pi'

pi:wZmTUD0G_F6d is username and pasword i think

Dont know why didnt work.

14 Temmuz 2015 Salı 18:22:09 UTC+3 tarihinde Cornelius Kölbel yazdı:

[Cornelius Kölbel]

Hi Tevfik,

the information you provide do not give an clue.
Please read https://www.privacyidea.org/getting-help/.

I suppose this is no big issue, but without the necessary information, I
can not help you accordingly.

Kind regards
Cornelius

Am Dienstag, den 14.07.2015, 22:32 -0700 schrieb Tevfik Ceydeliler:
> Hi,

> > > > 14 Temmuz 2015 Salı 14:07:00 UTC

> https://groups.google.com/d/msgid/privacyidea/bf6543c8-eecb-43d4-8dec-f00536ce555e%40googlegroups.com.

[Tevfik Ceydeliler]

Hi,

finally I solved the problem.

Regards..

15 Temmuz 2015 Çarşamba 11:10:53 UTC+3 tarihinde Cornelinux K yazdı:

[Cornelius Kölbel]

Hi,

could it be interesting for other what the problem was?

kind regards

Cornelius

Cornelius Kölbel

Corneliu...@netknights.it

+49 151 2960 1417

NetKnights GmbH

http://netknights.it

Landgraf-Karl-Str. 19, 34131 Kassel, Germany

Tel: +49 561 3166797, Fax: +49 561 3166798

Amtsgericht Kassel, HRB 16405

Geschäftsführer: Cornelius Kölbel

-------- Ursprüngliche Nachricht --------
Von: Tevfik Ceydeliler <tevfik.c...@gmail.com>
Datum: 04.08.2015 16:58 (GMT+01:00)
An: privacyidea <priva...@googlegroups.com>
Betreff: Re: After mysql db repication I cannot login privacy idea web page

To view this discussion on the web visit https://groups.google.com/d/msgid/privacyidea/811df598-fd7a-4687-81a3-e5e995b9d944%40googlegroups.com.

[Tevfik Ceydeliler]

           Hi,
Problem was about password of  pi database user on MySQL.
After dump and import databases on Slave , I change password of pi database user as shown pi.cfg on Master. Before I didnt change this.
Now I apply only replication. That is no problem. I perefer master-slave architecture.
Regards

4 Ağustos 2015 Salı 17:58:55 UTC+3 tarihinde Tevfik Ceydeliler yazdı:

...

[Cornelius Kölbel]

HI Tevfik,

thanks for the feedback.

Kind regards
Cornelius

> https://groups.google.com/d/msgid/privacyidea/93657fcf-533a-48c1-8eb5-2b65e5d47c57%40googlegroups.com.

[Tevfik Ceydeliler]

I appreciated for your assistance. 

Where canI share my idea about PI?

Is there any wish list?

5 Ağustos 2015 Çarşamba 09:05:06 UTC+3 tarihinde Cornelinux K yazdı:

>           ...

[Cornelius Kölbel]

You can file an issue at github.

https://github.com/privacyidea/privacyidea

Kind regards
Cornelius

> > > > 14 Temmuz 2015 Salı 17:56:29 UTC

> https://groups.google.com/d/msgid/privacyidea/3fe88d56-fd03-48b2-8334-f434779397c2%40googlegroups.com.

> For more options, visit https://groups.google.com/d/optout.