816972 - Update support.allizom.org with production data on a regular basis

Status: RESOLVED FIXED

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

Description

[Kadir Topal [:atopal]]

We need our staging environment to reflect our production environment. With the latest changes the difference has become so big that we effectively can't compare them anymore. 

Please update the staging environment with production data on the 1st of every month.

Comment 1

[Sheeri Cabral [:sheeri]]

Well, I missed the window for the 1st of December. I can do this whenever.

Comment 2

[Kadir Topal [:atopal]]

Alright, Great!

Stephen, could you list here what kind of accounts you need on support.allizom.org to run tests? I assume we'd need to create those accounts on production so they can be copied over periodically.

Once we have that, you can go ahead Sheeri.

Comment 3

[Stephen Donner [:stephend] Not actively reading bugmail]

We need:

User account: [e-mail]
Admin account: [e-mail]

Comment 4

[Sheeri Cabral [:sheeri]]

Is there anything I need to do with that user/admin account? It looks like an app user, not a db user...

FWIW I'm in Mountain View this week (I know you're here for a SUMO workweek) so let's try to get together, and also it would be easier to do stage after hours since I won't have to stay up late.

Comment 5

[Stephen Donner [:stephend] Not actively reading bugmail]

(In reply to Sheeri Cabral [:sheeri] from comment #4)
> Is there anything I need to do with that user/admin account? It looks like
> an app user, not a db user...
> 
> FWIW I'm in Mountain View this week (I know you're here for a SUMO workweek)
> so let's try to get together, and also it would be easier to do stage after
> hours since I won't have to stay up late.

These are just SUMO user accounts that we'd like to persist (i.e. not have to re-register each time we wipe our staging/dev, etc.)

Comment 6

[Kadir Topal [:atopal]]

Stephen, can you just create those accounts on production? 

I can then assign whatever permissions you require. Ricky, is that okay with you?

I guess we could also run a script after updating the staging db to create those accounts, but I assume creating the accounts on production is less of a hassle.

Comment 7

[Ricky Rosario [:rrosario, :r1cky]]

(In reply to Kadir Topal [:atopal] from comment #6)
> I can then assign whatever permissions you require. Ricky, is that okay with
> you?

Yes. I think they just need the wiki document delete permission, IIRC.

Comment 8

[Ricky Rosario [:rrosario, :r1cky]]

(In reply to Ricky Rosario [:rrosario, :r1cky] from comment #7)
> Yes. I think they just need the wiki document delete permission, IIRC.

The "Admin" user that is.

Comment 9

[Sheeri Cabral [:sheeri]]

I'm importing yesterday's sqldump of support_mozilla_org production into staging's support_allizom_org.

If there are any problems, we have backups and can revert.

Comment 10

[Sheeri Cabral [:sheeri]]

[[e-mail]1 scabral]# time mysql support_allizom_org < support_mozilla_com.2012.12.17.sql
real    29m15.896s
user    2m24.655s
sys     0m6.541s



Can you let me know if it looks good? I haven't updated any ACLs or anything...

Comment 11

[Ricky Rosario [:rrosario, :r1cky]]

(In reply to Sheeri Cabral [:sheeri] from comment #10)
> Can you let me know if it looks good? I haven't updated any ACLs or
> anything...

A few more things that have to happen every time we update data from prod to stage:

1- Truncate tidings_watch table
2- Update the row in django_site s/support.mozilla.org/support.allizom.org/

There might be more, but that's all I can think of for now. I already did 2. If you can do 1 that would be awesome, otherwise we will end up spamming people from -stage.

Comment 12

[Ricky Rosario [:rrosario, :r1cky]]

This is what we do locally and should be doing here as well:

SET SESSION FOREIGN_KEY_CHECKS = 0;

TRUNCATE tidings_watchfilter;

TRUNCATE tidings_watch;

TRUNCATE django_session;

TRUNCATE messages_inboxmessage;

TRUNCATE messages_outboxmessage_to;

TRUNCATE messages_outboxmessage;

SET SESSION FOREIGN_KEY_CHECKS = 1;

UPDATE django_site SET
    domain = 'support.allizom.org',
    name = 'support.allizom.org';

-- We don't pull images from production, clearing out the fields
-- let's us see the default placeholders.
UPDATE topics_topic SET
        image = NULL;
UPDATE products_product SET
        image = NULL;

Comment 13

[Sheeri Cabral [:sheeri]]

Done! 


mysql> use support_allizom_org
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SET SESSION FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> TRUNCATE tidings_watchfilter;
Query OK, 0 rows affected (0.24 sec)

mysql> 
mysql> TRUNCATE tidings_watch;
Query OK, 0 rows affected (0.24 sec)

mysql> 
mysql> TRUNCATE django_session;
Query OK, 0 rows affected (0.68 sec)

mysql> 
mysql> TRUNCATE messages_inboxmessage;
Query OK, 0 rows affected (0.18 sec)

mysql> 
mysql> TRUNCATE messages_outboxmessage_to;
Query OK, 0 rows affected (0.12 sec)

mysql> 
mysql> TRUNCATE messages_outboxmessage;
Query OK, 0 rows affected (0.13 sec)

mysql> 
mysql> SET SESSION FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> UPDATE django_site SET
    ->     domain = 'support.allizom.org',
    ->     name = 'support.allizom.org';
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> 
mysql> -- We don't pull images from production, clearing out the fieldsmysql> -- let's us see the default placeholders.
mysql> UPDATE topics_topic SET
    ->         image = NULL;
Query OK, 33 rows affected (0.02 sec)
Rows matched: 33  Changed: 33  Warnings: 0

mysql> UPDATE products_product SET
    ->         image = NULL;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

Leaving this open for more addendums and for a finalizing of automating this (and verifying it was done! It only takes 30 minutes to import this data and do the operations I just did, so it could be done daily, although it does slow down replication a bit so we might have to put in an outage window.

Comment 14

[Sheeri Cabral [:sheeri]]

Note: the regular automated updates are not something I'm going to be able to get to until at least next week.

Comment 15

[Kadir Topal [:atopal]]

Sheeri, let's start with the automated updates on January 1st if possible, but no rush, February 1st would be just as good.

Comment 16

[Sheeri Cabral [:sheeri]]

I'll do what I can!

Comment 17

[Sheeri Cabral [:sheeri]]

Created copy_support_to_stage.sh on the backup server and ran it manually - to copy today's backup from the backup server to the stage host. It's all working, on a manual run. Have to put this into puppet when we have SVN back.

Comment 18

[Sheeri Cabral [:sheeri]]

(script is in /data/backups/bin)

Comment 19

[Sheeri Cabral [:sheeri]]

script has been puppetized, will check on it tomorrow that it ran successfully (for now I'm just testing it nightly.

Comment 20

[Sheeri Cabral [:sheeri]]

Well, it worked, sort of:

[[e-mail]1 ~]# cd /data/backup-drop/sumo/support_mozilla_com/
[[e-mail]1 support_mozilla_com]# ls -rlth
total 1.1G
-rwx------ 1 root root 1.1G Jan 14 11:02 support_mozilla_com.2013.01.14.sql.gz
-rw-r--r-- 1 root root  26M Jan 15 07:53 support_mozilla_com.2013.01.15.sql.gz
[[e-mail]1 support_mozilla_com]# 


I'll note that the backup timing is at the same time:
-rwx------ 1 root root 1.1G Jan 15 07:57 support_mozilla_com.2013.01.15.sql.gz

So I need to tweak the timing of the run. I will set it to run the night before.

Comment 21

[Sheeri Cabral [:sheeri]]

For some bizarre reason, dev1.db.phx1.mozilla.com no longer has a /data directory. I created this again:

/data/backup-drop/sumo/support_mozilla_com/

and the script is running fine manually. I'll have to let it run automatically though to make sure it's doing the right thing.

Comment 22

[Sheeri Cabral [:sheeri]]

OK, it's been running fine and it looks like we have daily backups about 1.2G since 2/8.

I wrote a script to decompress yesterday's backup, import it, and then run all the commands in comment 12.

I am running this manually now, and if it works I'll set it to run automatically on the 10thth, so that we can ensure it works from cron before closing this bug.

Comment 23

[Sheeri Cabral [:sheeri]]

It worked and I set a cron job to run on the 10th.

Comment 24

[Sheeri Cabral [:sheeri]]

The output was:

gzip: support_mozilla_com.2013.03.09.sql.gz: No such file or directory
/usr/local/bin/import-sumo.sh: line 10: support_mozilla_com.2013.03.09.sql: No such file or directory
gzip: support_mozilla_com.2013.03.09.sql: No such file or directory

Because I didn't put a full path to the file....I had:

GZFILE=support_mozilla_com.$DATE.sql.gz

/bin/gunzip $GZFILE

Whoops.

Put the full path in, set it to run at 6:30 am today via cron.

Comment 25

[Sheeri Cabral [:sheeri]]

Cron worked! 

I have put the cron to run on the 1st of the month. Resolving.