1274277 - Give MDN team members access to production data

Status: RESOLVED FIXED

(developer.mozilla.org Graveyard :: General, enhancement)

Description

[Kadir Topal [:atopal]]

As the MDN product owner, I'd like to query a copy of the MDN database with production data, so I can quickly and frequently analyze and summarize data to make decisions.

For SUMO we solved this by providing a db slave behind VPN that required special access, sheeri commented about that on IRC:

> we can actually do better, and make a cluster, so that if we need to upgrade something it won't disrupt people (unless they're actually logging in to query)

Comment 1

[Sheeri Cabral [:sheeri]]

OK, the cluster is up and running, and monitored. All that's left is putting in backups.

The hostname will be mdntools-rw-vip.db.scl3.mozilla.com and the db lives on port 3306 (usual mysql port).

By using the VIP, you will have connectivity even if we have to take a db host offline for maintenance.

(that means when you are connecting, please don't use localhost, because it will work most of the time, but not all of the time).

Currently all the users from production are inherited. If you want more users (e.g. personal ones), please let me know, and if they just need mysql or if you intend them to have a login/homedir on these machines.

So basically, we're all set, I just need a list of usernames (in standard mozilla first initial/last name format, or whatever their LDAP username is).

Comment 2

[John Whitlock [:jwhitlock]]

I am unable to connect. Here's what I tried.

When on the VPN, I can't ping mdntools-rw-vip.db.scl3.mozilla.com

If I ssh into developeradm.private.scl3.mozilla.com (jwhitlock), I can ping the host, but can't connect with the mysql command line. I tried this command line:

mysql -h mdntools-rw-vip.db.scl3.mozilla.com -v -p -u <prod user> <prod database name>

Any advice on connecting? If it needs to be added, my LDAP user is jwhitlock, same email as bugzilla.

Comment 3

[Kadir Topal [:atopal]]

Sheerie, thanks so much for this! Users who should have access with homedir:

atopal
jperrier
jpatonnier
all of them @mozill.com

salvador, I don't have access to bug 1276379, could you cc me please?

Comment 4

[Sheeri Cabral [:sheeri]]

John - when I try from developeradm, I get a timeout:

[[e-mail]3 ~]$ nc -vz mdntools-rw-vip.db.scl3.mozilla.com 3306
nc: connect to mdntools-rw-vip.db.scl3.mozilla.com port 3306 (tcp) failed: Connection timed out

I'll see what we have to do to get it so you (and others) can connect to the machine while on the VPN. Let me know if you also need access from developeradm, and we can open a netflow.

Comment 5

[John Whitlock [:jwhitlock]]

I don't need access from developeradm, unless I'm going to start making anonymized DBs myself. I just need access from somewhere - VPN, developeradm, whatever is easiest.

Comment 6

[Kadir Topal [:atopal]]

Same here, Sheeri. I can't even ping mdntools-rw-vip.db.scl3.mozilla.com from VPN.

Comment 7

[Sheeri Cabral [:sheeri]]

adding :jabba and :atoll - can you create a VPN LDAP group for mdntools-rw-vip.db.scl3.mozilla.com aka 10.22.70.32?  (and if you're not the right people to do that, please cc/needinfo whoever is?)

And then add atopal and jwhitlock to the group? Thanx!

Comment 8

[:Atoll]

(In reply to Sheeri Cabral [:sheeri] from comment #7)
> adding :jabba and :atoll - can you create a VPN LDAP group for
> mdntools-rw-vip.db.scl3.mozilla.com aka 10.22.70.32?  (and if you're not the
> right people to do that, please cc/needinfo whoever is?)
> 
> And then add atopal and jwhitlock to the group? Thanx!

We can help, but not as part of some other team's bug - you'll need to file a bug blocking this one in Mozilla VPN: ACL Requests to get that. (I would normally do so, but having you file it saves us a round of signoffs and such.)

Comment 9

[Kadir Topal [:atopal]]

Thanks :atoll, I filed bug 1278107

Comment 10

[Sheeri Cabral [:sheeri]]

:atopal confirmed access, and I sent a password to jwhitlock as well, who is on PTO. Calling this resolved; please reopen if there's an issue.

Comment 11

[Kadir Topal [:atopal]]

I do have access now. I also filed bug 1278991 for jperrier and jpatonnier.

Comment 12

[John Whitlock [:jwhitlock]]

I was able to access w/ password, thanks!

Comment 13

[Ryan Johnson (:rjohnson)]

Hi Sheeri,

Can you add me to the list of authorized users? My username is "rjohnson".

Thanks!

Comment 14

[Matt Pressman [:mpressman]]

rjohnson has been added

Comment 15

[Ryan Johnson (:rjohnson)]

Hi Matt,

Sorry, I couldn't get to this until today, but I can't even ping mdntools-rw-vip.db.scl3.mozilla.com while connected to the VPN.

Also I can't ssh (or even ping) into developeradm.private.scl3.mozilla.com via the VPN.

Am I missing from an additional VPN group?

Comment 16

[Justin Dow [:jabba]]

I've added the user to vpn_mdntools_rw_vip, but user was already a member of vpn_developer, which should grant 22 and 80 to deverloperadm. Reconnecting the vpn client is required to receive the new ACL.

Comment 17

[John Whitlock [:jwhitlock]]

I believe rjohnson now has database access. But since the bug is still open...

mpressman, jperrier is turning over maintenance of the MDN Community Dashboard to Janet Swisher ([e-mail]), so she needs an account on the read-only database as well.  I can open the ACL bug.

Comment 18

[Matt Pressman [:mpressman]]

Pythian, can you create an account for [e-mail]

Comment 19

[Pythian Team73]

User created in mdntools, based on similar user rjohnson.

I will see if jswisher is available in IRC to provide the password

Comment 20

[Pythian Team73]

jswisher, can you provide your IRC nick/username so I can provide you with the password for your db user.

Comment 21

[Janet Swisher]

IRC nick is jswisher

Comment 22

[Pythian Team73]

Discussed with jswisher in IRC. 
I have provided the password.

Comment 23

[Pythian Team73]

[pythian.beebe]
Password provided, closing/resolved.

Comment 24

[Janet Swisher]

I finally have a db environment on my system, but was not able to get access with the credentials I was provided. Need help to resolve. TIA

Comment 25

[John Whitlock [:jwhitlock]]

My read-only connection works, and I ran this query:

SELECT User FROM mysql.user;  

I did not see jswisher or any variants in the user list. I did see my own account, and other that I know have access.  I suspect that it was lost in the last 2 months.

Comment 26

[Matt Pressman [:mpressman]]

Looks like the user creation only made it to one of the two hosts in the cluster. I've added the grants for jswisher on the second host so now both have the grants.

Comment 27

[Ryan Watson [:w0ts0n]]

Hi Janet, 

Did you get the access you needed after Matt's adjustment?

Comment 28

[Janet Swisher]

Yes, I did. Thanks!

Comment 29

[Ryan Watson [:w0ts0n]]

Perfect, thanks for getting back to me.