RC3 AWS/RDS ansible config

[rene@proversity,org]

Hi guys,

Trying to get RDS connection configured for RC3 ansible bootstrap. Any help would be fantastic. 

1. Have tried with recommended EDXAPP_MYSQL_ settings but get connection refused for migrate@<ip> user during platform provisioning. 

2. Found MySQL config vars for EDXAPP, XQUEUE, COURSE_DISCOVERY, CREDENTIALS, INSIGHTS and PROGRAMS. Do these need to be individually changed or is there some propagation/deprecation?

Many thanks,

R

[m...@edx.org]

Hi,

Responses are inline:

Max Rothman

DevOps Engineer, edX

On Tuesday, January 26, 2016 at 3:00:54 AM UTC-5, rene@proversity,org wrote:

Hi guys,

Trying to get RDS connection configured for RC3 ansible bootstrap. Any help would be fantastic. 

1. Have tried with recommended EDXAPP_MYSQL_ settings but get connection refused for migrate@<ip> user during platform provisioning. 

This sounds like a permissions issue. Have you verified that the usernames/passwords match ones in the database?

 

2. Found MySQL config vars for EDXAPP, XQUEUE, COURSE_DISCOVERY, CREDENTIALS, INSIGHTS and PROGRAMS. Do these need to be individually changed or is there some propagation/deprecation?

We use different RDS instances for each app. You could conceivably use the same host with one database per app, but you should at least make a different user per app to keep them isolated. To DRY things out, you could define an extra variable for the host and reference it in each place its needed.

Many thanks,

R

[rene@proversity,org]

Hi Max,

Appreciate for the response. I've gone a little further since I posted. Also I should probably have mentioned that I'm trying minimally invasive deployment (working with only @server-vars.yml to configure custom sandbox install). 

I found this (https://openedx.atlassian.net/wiki/display/OpenOPS/Creating+and+Updating+Databases+and+Database+Users?focusedCommentId=32309418#comment-32309418) doc, which shed some light. Seemed like the format had changed though, looking at create_db_and_users.yml comments. From there I found COMMON_MYSQL_MIGRATE_USER etc settings and things became clear. 

Turns out the fix was much easier than expected. The migrate user was being created locally through edxlocal but couldn't connect to the EDXAPP_MYSQL remote endpoint. Had to leave out most of the app specific vars and instead simply define the database_connection: &default_connection with the right login_ settings. Also defining EDXAPP_MYSQL ADMIN credentials but not sure that's necessary. After that, the sandbox ansible build handles everything internally. 

This is probably obvious stuff but writing it out in case anyone else comes up against a similar issue.

Regards,

R 

[Daniel McQuillen]

Hi Rene,

Are you setting up Fullstack on AWS and then configuring to use RDS? If so, are you taking each step manually or are you using CloudFormation template?

I am interested in setting up a very simple architecture, similar to "Very Small Deployments" from Feanil's Hosting Open EdX presentation, with Fullstack (including MongoDB) on one box and MySQL via RDS.  But I'm having a hard time finding examples to go off of. I see the CloudFormation template that CloudGenious created, but it's way more powerful than what I'm trying to create.

If you do make manual steps to configure for RDS, am I right in assuming that you

1) provision AWS instance 

2) ssh into instance

3) do a curl | bash for ansible-bootstrap.sh 

3) setup your server-vars.yml to access a pre-existing RDS instance

4) do a curl | bash for sandbox.sh?

Thanks for any thoughts.

Daniel

[rene@proversity,org]

Hi Daniel,

Went through similar difficulties, but have everything running now so it does work out. I'm not using CloudFormation, for the same reason you mention. Furthermore, the CF templates were removed from the configuration master repo, leading me to think its somewhat legacy. Unfortunately, there aren't really any examples online, at least not that work with Ubuntu 14.04/Dogwood RC3. 

I'm doing everything with custom bash scripts that orchestrate EC2 instances (including RDS), install ansible, set server-vars and run the playbooks (there is a lot of duplication in the available scripts). After that there is some manual work to get everything connected together (OAuth2 etc). You have the process correct there, but might have problems with the bash scripts. Sometimes you'll need to let the dependencies install and then run the playbooks as another process. It all worked best when i passed the whole server-vars.yml file as an -e arg to the sandbox playbook.    

The biggest issue I came across was getting the RDS database correctly configured to handle provisioning for all the services using MySQL. Turns out you need to run the create_db_and_users playbook, passing in a db-config.yml as described here and in the playbook comments. Just make sure you define the migrate user (COMMON_MYSQL_MIGRATE_USER etc), along with other relevant server-vars, correctly if you want to deploy with Ansible. 

Hope that helps a bit. 

R 

[Daniel McQuillen]

Thanks for the tips, Rene. So rather than use sandbox.sh you break up the commands (command line or ansible) and do them manually?

I'm surprised you mentioned OAuth2, but perhaps that for something specific you're doing with APIs...

Thanks much for the pointer on the create_db_and_users playbook.

- Daniel

[Daniel McQuillen]

And now I understand your point about OAuth2...didn't realize that Open EdX came with an API that used OAuth2. Just noticed this in the nginx config comments...

# The api is accessed using OAUTH2 which

  # uses the authorization header so we can't have

  # basic auth on it as well.

[rene@proversity,org]

Hi Daniel,

Glad I could help. API is super important but not very difficult to set up, just remember LMS is your endpoint. 

OAuth2 is also used to authorize mobile application logins. I've been having some issues with Insights on that aspect as it wants a specific return path. 

If you haven't already found it, the main.yml playbook in the edxapp role contains reference to most of the server vars available. 

I'm not running the install manually as such. I just wrote my own versions of the ansible and sandbox bash. The default scripts are completely decoupled so that you can install ansible anywhere you need to run playbooks. I just changed the way it runs to install ansible and run sandbox on a master box but remotely configure everything else. Once I'm confidant everything works I'll commit the scripts to the public repo, in the meantime I'm still bringing micro-services up.  

[Daniel McQuillen]

Wow, that would be great if you share your scripts in the public repo! Looking forward to that if you decide to do it.

I thought on Fullstack the API would only be used by external 3rd parties (if such a thing was required), but I guess what you're alluding to is that even on Fullstack, the different open edx services use the APIs to communicate. I'm not sure if you're suggesting that OAuth2 is involved in that, but perhaps it is.

[rene@proversity,org]

Hi Daniel, 

I'll definitely be making everything public at some stage. Honestly, I've only been working with the software for a little over a month, still learning the systems. Working on a video series about OpenEdX at the moment, will release all together. Hope it will help. Regarding the API(s), they are the default way microsystems communicate with each other and OAuth2 is now the standard for API authentication. Insights for instance uses OAuth to authorize logins via the LMS endpoint, in fact you can run your entire analytics pipeline from a standalone box (using the APIs to talk to LMS/Studio/etc), the mobile apps use endpoints for authentication, user info and course discovery etc etc. They are the standard for authentication and communication between the disparate applications. Forum (cs_comment_service), Notes, Credit, eCommerce, etc all have APIs. Clone out the /edx/configuration repo and search inside for _API, you'll see what I mean. 

R

[Daniel McQuillen]

Rene, curious if you are using the edx_service_rds role as part of your RDS setup. I just came across those roles now...looks like they would be used before the create_db_and_users plays, unless you set those things up manually in AWS beforehand...

 

[rene@proversity,org]

Hi Daniel, not using edx_service_rds but thank you for pointing that out, interesting for scalability. I bring up the RDS instance manually, so easy in AWS. Not much configuration needs to be done after that, just security really. I'm sure that its better to use the edx_vpc etc roles to create instances but not necessary for the environment I'm currently working on. R 

[Daniel McQuillen]

Curious if anybody else has a good approach to standardizing the setup of Fullstack + RDS.

I would have thought the simplest steps to be:

1) Build server and ssh in to install Ansible via ansible-bootstrap.sh

2) Setup an /edx/app/edx_ansible/server-vars.yml file with three properties to point to the RDS instance and including the admin credentials : EDXAPP_MYSQL_HOST, EDXAPP_MYSQL_USER_ADMIN, and EDXAPP_MYSQL_PASSWORD_ADMIN

3) Modify the sandbox.sh file to include server-vars.yml when running the edx_sandbox.yml play, as I've done here: https://github.com/danielmcquillen/configuration/blob/b05cce50dc1d7bab7e2f55a84b57298997503948/util/install/sandbox.sh#L58

4) Run that slightly customized version of sandbox

Unfortunately that doesn't work for me. Has anybody else tackled this setup?

[Daniel McQuillen]

Edit: modified sandbox.sh link -> https://github.com/danielmcquillen/configuration/blob/078ba1ffe1721bd4e153f4f9305957d4f39703d3/util/install/sandbox.sh#L51

[Daniel McQuillen]

Rene, I wonder how you got the ansible mysql_db command to connect to your RDS.

I can connect fine from my EC2 instance to my RDS using the mysql -h (host) -u (admin user) -p (password) command.

But even if I hardcode the db creation task in edxlocal with my server-vars.yml settings, like so:

- name: create databases
   mysql_db:
     db: "{{ item }}"
     state: present
     encoding: utf8
     login_host: EDXAPP_MYSQL_HOST
     login_password: EDXAPP_MYSQL_PASSWORD_ADMIN
     login_user: EDXAPP_MYSQL_USER_ADMIN
   when: item != None and item != ''
   with_items: "{{ edxlocal_databases }}"

I still get a connect error. Did you run into that issue?

Thanks,

Daniel

[rene@proversity,org]

Hi Daniel,

Ran into a lot of issues before I found a working process. The trick for me was to leave all EdX files alone. 

1. I have server-vars.yml and db-config.yml in secure storage along with my bootstrap scripts. 

2. In server-vars (along with other features, api settings etc) I have the following (this is from my local branch but only the values are different in release):

MYSQL_LOGIN_HOST: 'localhost'
MYSQL_LOGIN_USER: 'root'
MYSQL_LOGIN_PASS: 'password'
MYSQL_LOGIN_PORT: 3306

COMMON_MYSQL_MIGRATE_USER: 'migrate'
COMMON_MYSQL_MIGRATE_PASS: 'password'
COMMON_MYSQL_READ_ONLY_USER: 'readonly'
COMMON_MYSQL_READ_ONLY_PASS: 'password'
COMMON_MYSQL_ADMIN_USER: 'admin'
COMMON_MYSQL_ADMIN_PASS: 'password'

EDXAPP_MYSQL_DB_NAME: 'edxapp'
EDXAPP_MYSQL_HOST: {{ MYSQL_LOGIN_HOST }}
EDXAPP_MYSQL_PORT: '{{ MYSQL_LOGIN_PORT }}'
EDXAPP_MYSQL_USER: 'edxapp'
EDXAPP_MYSQL_USER_ADMIN: 'admin'
EDXAPP_MYSQL_PASSWORD: 'password'
EDXAPP_MYSQL_PASSWORD_READ_ONLY: 'readonly'
EDXAPP_MYSQL_PASSWORD_ADMIN: 'password'
EDXAPP_MYSQL_REPLICA_DB_NAME: '{{ EDXAPP_MYSQL_DB_NAME }}'
EDXAPP_MYSQL_REPLICA_USER: '{{ EDXAPP_MYSQL_USER }}'
EDXAPP_MYSQL_REPLICA_PASSWORD: '{{ EDXAPP_MYSQL_PASSWORD }}'
EDXAPP_MYSQL_REPLICA_HOST: '{{ EDXAPP_MYSQL_HOST }}'
EDXAPP_MYSQL_REPLICA_PORT: '{{ EDXAPP_MYSQL_PORT }}'

XQUEUE_MYSQL_DB_NAME: 'xqueue'
XQUEUE_MYSQL_USER: 'xqueue'
XQUEUE_MYSQL_PASSWORD: 'password'
XQUEUE_MYSQL_HOST: '{{ MYSQL_LOGIN_HOST }}'
XQUEUE_MYSQL_PORT: '{{ MYSQL_LOGIN_PORT }}'

INSIGHTS_DATABASE_NAME: 'dashboard'
INSIGHTS_DATABASES:
  default:
    ENGINE: 'django.db.backends.mysql'
    NAME: '{{ INSIGHTS_DATABASE_NAME }}'
    USER: 'dash'
    PASSWORD: 'password'
    HOST: '{{ MYSQL_LOGIN_HOST }}'
    PORT: '{{ MYSQL_LOGIN_PORT }}'

I'm also defining MYSQL config for (ANALYTICS_API_DATABASES, ANALYTICS_PIPELINE_OUTPUT_DATABASE, ANALYTICS_PIPELINE_INPUT_DATABASE, HIVE_METASTORE_DATABASE etc) because I use the same files to run my analytics stack on a different instance. The COMMON_MYSQL_MIGRATE_* settings are super important as they are used by most services to run django migrations. 

3. You will need to change the MYSQL_LOGIN_* vars to your master RDS login details, using public dns as HOST. 

4. In my db-config file I have the user and database definitions only:

---
  database_connection: &default_connection
    login_host: "localhost"
    login_user: "root"
    login_password: "password"


  databases:
    - name: "edxapp"
      state: "present"
      encoding: "utf8"
      <<: *default_connection
    - name: "xqueue"
      state: "present"
      encoding: "utf8"
      <<: *default_connection
    - name: "dashboard"
      state: "present"
      encoding: "utf8"
      <<: *default_connection


  database_users:
    - name: "migrate"
      state: "present"
      password: "password"
      host: "%"
      privileges:
        - 'edxapp.*:ALL'
        - 'dashboard.*:ALL'
        - 'xqueue.*:ALL'
        - 'analytics.*:ALL'
        - 'reports.*:ALL'
        - 'metastore.*:ALL'
      <<: *default_connection
    - name: "edxapp"
      state: "present"
      password: "password"
      host: "%"
      privileges:
        - 'edxapp.*:ALL'
      <<: *default_connection
    - name: "xqueue"
      state: "present"
      password: "password"
      host: "%"
      privileges:
        - 'xqueue.*:ALL'
      <<: *default_connection
    - name: "dash"
      state: "present"
      password: "password"
      host: "%"
      privileges:
        - "dashboard.*:ALL"
      <<: *default_connection
    - name: "readonly"
      state: "present"
      password: "password"
      host: "%"
      privileges:
        - '*.*:SELECT'
      <<: *default_connection
    - name: "admin"
      state: "present"
      password: "password"
      host: "%"
      privileges:
        - '*.*:CREATE USER'
      <<: *default_connection

You can see that I'm passing the default_connection into each database and database_user, this allows for the RDS connection to be made. The service specific credentials are used internally in the MySQL instance. 

5. You will need to add a user and database record into db-config for all users you define - be sure to get the privileges correct.

6. The edxlocal task handles some of the work but after sandbox install you will still need to run the create_db_and_users playbook.

sudo /edx/app/edx_ansible/venvs/edx_ansible/bin/ansible-playbook -i 'localhost,' /edx/app/edx_ansible/edx_ansible/playbooks/edx-east/create_db_and_users.yml -e @/path/to/db-config.yml

For me that was it. If you change any of the settings or add new dbs/users just rerun the playbook. Don't forget to run /edx/bin/update on those services afterwards though. Can't be completely sure I've covered every base but this setup seems to work quite well. Hope that helps.

Regards,

Rene

[Daniel McQuillen]

Rene,

Awesome stuff man. Thanks for sharing. I'm going to copy your approach tomorrow and see how far I get.

I was going about it this a different way: my thinking was that edx_sandobox should be able to run exactly as it does now, but just against a different MYSQL host (not localhost). So theoretically all I had to do was the following:

1) define an EDXAPP_MYSQL_HOST, EDXAPP_MYSQL_USER_ADMIN and EDXAPP_MYSQL_PASSWORD_ADMIN in server-vars.yml

2) update sandbox.sh to include server-vars.yml in the extra vars, which runs edx_sandbox.yml playbook.

The edx_sandbox.yml playbook has conditional logic that won't run the mysql role if EDXAPP_MYSQL_HOST is defined, which makes sense. The next step, the edxlocal role, should just do everything the same, except do it against the remote host I defined.

Unfortunately, it doesn't appear there's any existing wiring to automatically use these three variables in edxlocal.

So, I updated the edxlocal/tasks.yml file in my own config repo clone to include the following settings for every mysql_db and mysql_user task in edxlocal/tasks.yml...

    login_user: "{{ EDXAPP_MYSQL_USER_ADMIN }}"

    login_password: "{{ EDXAPP_MYSQL_PASSWORD_ADMIN }}"

    login_host: "{{ EDXAPP_MYSQL_HOST }}"

    login_port: '{{ EDXAPP_MYSQL_PORT }}' 

(I had to define the port too, even though it should default to 3308...for some reason this wouldn't work unless I explicitly defined it...that took me an unreasonable amount of time to figure out, as I trusted it would respect the documented default).

When I ran my own sandbox.sh file, it would go along nicely, running the edx_sandbox.yml, which would get to edxlocal and create all the users and dbs on my RDS instance...until the "setup the read-only db user"  where it failed with a denied access error, which is where I left things today. Why it would fail on that task when the db credentials are exactly the same is a mystery to me. I'll pick up tomorrow with the advantage of all the work you've posted.

- Daniel

[rene@proversity,org]

Hi Daniel,

No problem, let me know how it goes. I was trying to accomplish the same thing, but realized quickly the config is more granular than expected. Few things to keep in mind:

1. The EDXAPP_MYSQL_* settings are only for EDXAPP (that's LMS and CMS)., there are more common vars all over the place. 

2. The MySQL condition in edx_sandbox only prevents the mysql role from being installed for the master playbook (if EDXAPP_MYSQL_HOST ~= localhost). The role will still get installed by one of the other roles that has it as a dependency. 

3. You should also be putting a copy of server-vars.yml in /edx/app/edx_ansible/. The /edx/bin/update processes (and a few others) read that file in by default. 

4. The edxlocal role effectively does what the create_db_and_users playbook does, just in a more limited sense (should only be for local installs). 

5. MySQL default port is 3306 (not 3308 - in case it wasn't a typo =). 

I understand your frustration. I was lucky to figure the data stores out pretty quickly, but have been fighting the analytics pipeline for nearly a week. 

There is definitely a documentation gap between devstack and production. 

Rene

[Daniel McQuillen]

Hi Rene,

Yes, you're right on both accounts: I should have used more generic variables as you did, (not the EDXAPP_ prefix), and I meant to type 3306 for the MySQL port. My excuse is that it's already Friday already here in AU!

I'm doing another AWS build now as I write, following your steps. As you refine and share your approach, I hope it's picked up for the wiki or similar, as there may be a number of potential users who want the simplicity of Fullstack for their small target audience, but at the same time want to add in the benefits RDS service for persistence (and by extension, maybe something like MongoLab). 

A great weekend to you and the rest of the ops mailing list!

Daniel

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Daniel McQuillen

McQuillen Interactive Pty. Ltd.

Melboune, Australia

www.mcquilleninteractive.com

www.simplediagrams.com

--
You received this message because you are subscribed to a topic in the Google Groups "Open edX operations" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/openedx-ops/kwPCcdEj8W0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to openedx-ops...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/openedx-ops/9dd09988-0e43-490f-a893-c2c7c10d9e2c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[rene@proversity,org]

Hi Daniel,

Just a quick heads up. I was doing a clean install today with the updated configuration repo. Realized that even if you fully define everything sandbox wants to run the edxlocal role. Just remove the role from edx_sandbox,yml or add the same when EDXAPP_MYSQL_HOST conditional. Otherwise it tries and fails to create local databases. Everything seems to run fine after that. That goes for the analytics_single.yml playbook too, if you're running the analytics stack anywhere.

Thank you for the compliment. I'm super busy just getting everything working at the moment but will definitely publish some docs/videos when finished. For mongodb MongoLab and compose.io are both good solutions. Personally I would really like to have an AWS mongo cluster under my control but then multi-region support is complicated. Strange that EdX discuss minimum scalable architecture but only provide either single server or full cluster configurations. 

Have a good one,

Rene

[Daniel McQuillen]

Hi Rene,

I want to try to summarize the steps you mentioned in this thread to create a kind of minimal-but-robust setup (Fullstack + RDS). Perhaps this helps somebody following this thread at a later date. This might not be exhaustive, but the main steps are:

1. Setup AWS EC2 server, AWS RDS DB, relevant subnets/security groups/elastic IP.

2. On server, run ansible-bootstrap.sh script, per Fullstack instructions

3. Create an /edx/app/edx_ansible/server-vars.yml file per your example above

4. Create an /edx/app/edx_ansible/db-config.yml file per your example above  (but including analytics-specific info...)

6. Run the standard sandbox.sh script, per Fullstack instructions (but this won't pull in server-vars.yml, will it? You might have customized this script so that it includes server-vars.yml as an extra argument for that script's final command that starts ansible.)

7. Run the db-config.yml with command "sudo /edx/app/edx_ansible/venvs/edx_ansible/bin/ansible-playbook -i 'localhost,' /edx/app/edx_ansible/edx_ansible/playbooks/edx-east/create_db_and_users.yml -e @/path/to/db-config.yml"

At least, that's how I think the process runs now. I'm trying to test it out and see what steps I missed. Thanks again for sharing.

- Daniel

[rene@proversity,org]

Hi Daniel,

I'll publish full docs at some stage soon but this is a good place to keep the basics. You've got the process pretty much down, however I can offer a few pointers. 

1. I keep my server-vars, db-config, other config files and custom bash scripts in a private repo. Clone them in first and run everything off them.

2. I have customized both the ansible and sandbox bash scripts to use server-vars at the path of my conf repo.

3. I run create_db_and_users before running sandbox (otherwise provisioning fails quite often with mysql permissions issues).

4. There are some changes that need to be made to the /edx/configuration repo for it to work in Ubuntu 14.04 and to allow for only a single install run. I have sent my contributor agreement through, as soon as that is confirmed, I'll submit pull requests for those fixes, but right now you should probably run the install without custom settings then rerun the sandbox playbook passing in custom server-vars.

5. Security is super easy, just create an openedx group for the app server and an openedx-rds group for mysql. Then use the respective group id for source/destinations.

6. Remember to include hive metastore, reports and analytics db config if you want to run the analytics stack on the same datastore. 

Hope you're up and running. 

Rene

[Daniel McQuillen]

Hi Rene,

Ok great. Thanks for that feedback. I've updated my sandbox.sh to work more like ansible-bootstrap.sh in regards to the environmental variable for configuration repo location (CONFIGURATION_REPO) and to respect any existing value for EXTRA_VARS 

https://github.com/danielmcquillen/configuration/commit/234b32f658216718f4440952b357d1ed00c480a2.

With that it's easier to follow the process you outlined.

- Daniel

[Daniel McQuillen]

Rene,

BTW, you  -- or anybody else attempting a minimum production setup -- might find this page helpful for the Analytics part of that small setup.

https://openedx.atlassian.net/wiki/display/OpenOPS/edX+Analytics+Installation

- Daniel

[rene@proversity,org]

Hi Daniel,

Thank you. Have gone through that process without much success. Installing the stack is fine. Getting it to run successful tasks and populate the reports DB is another issue and even once that works I still get no data to display on the insights frontend. I've moved over to full EMR cluster configuration, but doing it manually as the /edx-analytics-configuration build is a bit iffy. Remember that the Analytics Stack is freestanding and still requires Ubuntu 12.04 Precise. If you haven't yet, join the analytics group. Lots of good advice in there. Good luck, hope you get it working! I'm still struggling.

Rene

[saadat...@gmail.com]

Thanks for all the guidance!

You may need to add -c local as below otherwise some people may get SSH Permission denied error

sudo /edx/app/edx_ansible/venvs/edx_ansible/bin/ansible-playbook -c local -i 'localhost,' /edx/app/edx_ansible/edx_ansible/playbooks/edx-east/create_db_and_users.yml -e @/path/to/db-config.yml