980874 - [remo] Prod database dump

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Giorgos Logiotatidis [:giorgos]]

We need a prod database dump to run same data migration tests before pushing to production. Preferably today or on Monday we want to have this if possible.

Host: reps.mozilla.org

Because this contains some private emails can you please send it to my google drive?

Thanks!

Comment 1

[Brandon Burton [:solarce]]

Moving this to the database team as they have familiarity with the rules and procedures around providing a dump of production data with PII to developers for local development and testing

Comment 2

[Sheeri Cabral [:sheeri]]

Can we sanitize the emails at all? or do you need the exact data?

Comment 3

[Justin Crawford [:hoosteeno] [:jcrawford]]

:sheeri, sanitizing emails is great. Thanks for asking.

Comment 4

[Sheeri Cabral [:sheeri]]

I have sanitized the e-mails by doing a SHA1 hash of the e-mail address, and taking the first N letters of that as the username. N is the number of letters in the original email username.

e.g. [e-mail] becomes [e-mail]

Here's how I did it - I imported a backup to a new db (sanitized_reps_mozilla_org), and then:

use sanitized_reps_mozilla_org;

UPDATE profiles_userprofile 
SET private_email = CONCAT(LEFT(sha1(private_email) , LOCATE('@',private_email)-1), 
SUBSTRING(private_email,LOCATE('@',private_email)))
WHERE 1=1;

UPDATE auth_user
SET email = CONCAT(LEFT(sha1(email) , LOCATE('@',email)-1), 
SUBSTRING(email,LOCATE('@',email)))
WHERE 1=1;

I'll export this and get this to you post-haste.

Comment 5

[Sheeri Cabral [:sheeri]]

Put up in Google Drive...resolve if it looks good!

Comment 6

[Giorgos Logiotatidis [:giorgos]]

Thanks for the quick response Sheeri!